#####################################################################
Character Set 조회
#####################################################################
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ -------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET KO16KSC5601
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_NCHAR_CHARACTERSET KO16KSC5601
NLS_RDBMS_VERSION 8.1.6.3.0
#####################################################################
Control File 조회
#####################################################################
SQL> select name from v$controlfile;
NAME
-----------------------------------------------
/oracle/app/oracle/oradata/WWW/control01.ctl
/oracle/app/oracle/oradata/WWW/control02.ctl
/oracle/app/oracle/oradata/WWW/control03.ctl
-----------------------------------------------
/oracle/app/oracle/oradata/WWW/control01.ctl
/oracle/app/oracle/oradata/WWW/control02.ctl
/oracle/app/oracle/oradata/WWW/control03.ctl
#####################################################################
user별 object 조회
#####################################################################
SQL> select owner, object_type, count(*) from dba_objects
where object_type in ('TABLE', 'INDEX', 'VIEW', 'PROCEDURE', 'FUNCTION')
group by owner, object_type
order by 1, 2;
where object_type in ('TABLE', 'INDEX', 'VIEW', 'PROCEDURE', 'FUNCTION')
group by owner, object_type
order by 1, 2;
OWNER OBJECT_TYPE COUNT(*)
--------------------- ------------------ ----------
ADMIT INDEX 72
ADMIT TABLE 73
BIZMAX INDEX 2
BIZMAX TABLE 2
CDL INDEX 90
CDL TABLE 73
CDL VIEW 10
CTXSYS INDEX 38
CTXSYS PROCEDURE 1
CTXSYS TABLE 29
CTXSYS VIEW 35
EDUGRAD INDEX 7
EDUGRAD TABLE 7
FRIEND TABLE 1
IAS INDEX 9
IAS TABLE 9
IPSI INDEX 69
IPSI TABLE 56
KOREA INDEX 15
KOREA TABLE 55
KRBIZ INDEX 348
KRBIZ TABLE 229
KRBIZ VIEW 1
KUPR INDEX 7
KUPR TABLE 6
NOTICE INDEX 23
NOTICE TABLE 31
ORANGE FUNCTION 9
ORANGE INDEX 4
ORANGE TABLE 6
ORANGE VIEW 39
ORS FUNCTION 7
ORS INDEX 57
ORS PROCEDURE 2
ORS TABLE 49
ORS VIEW 1
OUTLN INDEX 3
OUTLN TABLE 2
PERSON INDEX 6
PERSON TABLE 6
RNDCARD TABLE 1
RNDCARD VIEW 1
SCOTT INDEX 2
SCOTT TABLE 4
SYS FUNCTION 23
SYS INDEX 218
SYS PROCEDURE 10
SYS TABLE 197
SYS VIEW 1420
SYSTEM INDEX 23
SYSTEM TABLE 23
SYSTEM VIEW 3
WIZARD INDEX 47
WIZARD TABLE 53
--------------------- ------------------ ----------
ADMIT INDEX 72
ADMIT TABLE 73
BIZMAX INDEX 2
BIZMAX TABLE 2
CDL INDEX 90
CDL TABLE 73
CDL VIEW 10
CTXSYS INDEX 38
CTXSYS PROCEDURE 1
CTXSYS TABLE 29
CTXSYS VIEW 35
EDUGRAD INDEX 7
EDUGRAD TABLE 7
FRIEND TABLE 1
IAS INDEX 9
IAS TABLE 9
IPSI INDEX 69
IPSI TABLE 56
KOREA INDEX 15
KOREA TABLE 55
KRBIZ INDEX 348
KRBIZ TABLE 229
KRBIZ VIEW 1
KUPR INDEX 7
KUPR TABLE 6
NOTICE INDEX 23
NOTICE TABLE 31
ORANGE FUNCTION 9
ORANGE INDEX 4
ORANGE TABLE 6
ORANGE VIEW 39
ORS FUNCTION 7
ORS INDEX 57
ORS PROCEDURE 2
ORS TABLE 49
ORS VIEW 1
OUTLN INDEX 3
OUTLN TABLE 2
PERSON INDEX 6
PERSON TABLE 6
RNDCARD TABLE 1
RNDCARD VIEW 1
SCOTT INDEX 2
SCOTT TABLE 4
SYS FUNCTION 23
SYS INDEX 218
SYS PROCEDURE 10
SYS TABLE 197
SYS VIEW 1420
SYSTEM INDEX 23
SYSTEM TABLE 23
SYSTEM VIEW 3
WIZARD INDEX 47
WIZARD TABLE 53
54 rows selected.
#####################################################################
Datafile Size 조회
#####################################################################
SQL> select sum(bytes)/1024/1024 from dba_data_files;
SUM(BYTES)/1024/1024
-----------------------
20146
-----------------------
20146
SQL> select sum(bytes)/1024/1024 from dba_segments;
SUM(BYTES)/1024/1024
-----------------------
6561.64453
-----------------------
6561.64453
#####################################################################
user별 tablespace 조회
#####################################################################
SQL> select username, default_tablespace, temporary_tablespace from dba_users
where username not in ('SYS', 'SYSTEM');
where username not in ('SYS', 'SYSTEM');
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
-----------------------------\- ------------------------------ ------------------------------
TRACESVR SYSTEM SYSTEM
OUTLN SYSTEM SYSTEM
ORS TS_ORS01 TS_ORSTEMP01
AURORA$ORB
$UNAUTHENTICATED SYSTEM SYSTEM
NOTICE TS_NOT01 TS_NOTTEMP01
FRIEND TS_FRI01 TS_FRITEMP01
ORANGE TOOLS TEMP
IPSI TS_EEX01 TS_EEXTEMP01
KOREA TS_FRI01 TS_FRITEMP01
RNDCARD TS_FRI01 TS_FRITEMP01
SMBSUPP TS_FRI01 TS_FRITEMP01
ADMIT TS_EEX02 TS_EEXTEMP02
KRBIZ TS_KRB01 TS_NOTTEMP01
CTXSYS TS_CTX01 TS_NOTTEMP01
JONES SYSTEM SYSTEM
BIZMAX TOOLS TEMP
DBSNMP SYSTEM SYSTEM
PERSON TS_PER01 TS_NOTTEMP01
SCOTT SYSTEM SYSTEM
ADAMS SYSTEM SYSTEM
CLARK SYSTEM SYSTEM
BLAKE SYSTEM SYSTEM
CDL TS_CDL01 TS_CDLTEMP01
EDUGRAD TS_EDU01 TS_EDUTEMP01
KUPR TS_KUPR01 TS_NOTTEMP01
WIZARD TS_WIZ01 TS_NOTTEMP01
IAS TS_IAS01 TS_NOTTEMP01
IACF TS_KRB01 TS_NOTTEMP01
-----------------------------\- ------------------------------ ------------------------------
TRACESVR SYSTEM SYSTEM
OUTLN SYSTEM SYSTEM
ORS TS_ORS01 TS_ORSTEMP01
AURORA$ORB
$UNAUTHENTICATED SYSTEM SYSTEM
NOTICE TS_NOT01 TS_NOTTEMP01
FRIEND TS_FRI01 TS_FRITEMP01
ORANGE TOOLS TEMP
IPSI TS_EEX01 TS_EEXTEMP01
KOREA TS_FRI01 TS_FRITEMP01
RNDCARD TS_FRI01 TS_FRITEMP01
SMBSUPP TS_FRI01 TS_FRITEMP01
ADMIT TS_EEX02 TS_EEXTEMP02
KRBIZ TS_KRB01 TS_NOTTEMP01
CTXSYS TS_CTX01 TS_NOTTEMP01
JONES SYSTEM SYSTEM
BIZMAX TOOLS TEMP
DBSNMP SYSTEM SYSTEM
PERSON TS_PER01 TS_NOTTEMP01
SCOTT SYSTEM SYSTEM
ADAMS SYSTEM SYSTEM
CLARK SYSTEM SYSTEM
BLAKE SYSTEM SYSTEM
CDL TS_CDL01 TS_CDLTEMP01
EDUGRAD TS_EDU01 TS_EDUTEMP01
KUPR TS_KUPR01 TS_NOTTEMP01
WIZARD TS_WIZ01 TS_NOTTEMP01
IAS TS_IAS01 TS_NOTTEMP01
IACF TS_KRB01 TS_NOTTEMP01
28 rows selected.
#####################################################################
user 및 user priviedge 조회, 생성
#####################################################################
select 'CREATE USER ' || username || ' IDENTIFIED BY ' || username ||
' DEFAULT TABLESPACE ' || default_tablespace ||
' TEMPORARY TABLESPACE ' || temporary_tablespace ||';'
from dba_users
where username not in ('SYS','SYSTEM');
' DEFAULT TABLESPACE ' || default_tablespace ||
' TEMPORARY TABLESPACE ' || temporary_tablespace ||';'
from dba_users
where username not in ('SYS','SYSTEM');
'CREATEUSER'||USERNAME||'IDENTIFIEDBY'||USERNAME||'DEFAULTTABLESPACE'||DEFAULT_TABLESPACE||
'TEMPORARYTABLESPACE'||TEMPORARY_TABLESPACE||';'
create user TRACESVR identified by TRACESVR default tablespace SYSTEM temporary tablespace SYSTEM;
create user OUTLN identified by OUTLN default tablespace SYSTEM temporary tablespace SYSTEM;
create user ORS identified by ORS default tablespace TS_ORS01 temporary tablespace TS_ORSTEMP01;
create user AURORA$ORB$UNAUTHENTICATED identified by AURORA$ORB$UNAUTHENTICATED default tablespace SYSTEM temporary tablespace SYSTEM;
create user NOTICE identified by NOTICE default tablespace TS_NOT01 temporary tablespace TS_NOTTEMP01;
create user FRIEND identified by FRIEND default tablespace TS_FRI01 temporary tablespace TS_FRITEMP01;
create user ORANGE identified by ORANGE default tablespace TOOLS temporary tablespace TEMP;
create user IPSI identified by IPSI default tablespace TS_EEX01 temporary tablespace TS_EEXTEMP01;
create user KOREA identified by KOREA default tablespace TS_FRI01 temporary tablespace TS_FRITEMP01;
create user RNDCARD identified by RNDCARD default tablespace TS_FRI01 temporary tablespace TS_FRITEMP01;
create user SMBSUPP identified by SMBSUPP default tablespace TS_FRI01 temporary tablespace TS_FRITEMP01;
create user ADMIT identified by ADMIT default tablespace TS_EEX02 temporary tablespace TS_EEXTEMP02;
create user KRBIZ identified by KRBIZ default tablespace TS_KRB01 temporary tablespace TS_NOTTEMP01;
create user CTXSYS identified by CTXSYS default tablespace TS_CTX01 temporary tablespace TS_NOTTEMP01;
create user JONES identified by JONES default tablespace SYSTEM temporary tablespace SYSTEM;
create user BIZMAX identified by BIZMAX default tablespace TOOLS temporary tablespace TEMP;
create user DBSNMP identified by DBSNMP default tablespace SYSTEM temporary tablespace SYSTEM;
create user PERSON identified by PERSON default tablespace TS_PER01 temporary tablespace TS_NOTTEMP01;
create user SCOTT identified by SCOTT default tablespace SYSTEM temporary tablespace SYSTEM;
create user ADAMS identified by ADAMS default tablespace SYSTEM temporary tablespace SYSTEM;
create user CLARK identified by CLARK default tablespace SYSTEM temporary tablespace SYSTEM;
create user BLAKE identified by BLAKE default tablespace SYSTEM temporary tablespace SYSTEM;
create user CDL identified by CDL default tablespace TS_CDL01 temporary tablespace TS_CDLTEMP01;
create user EDUGRAD identified by EDUGRAD default tablespace TS_EDU01 temporary tablespace TS_EDUTEMP01;
create user KUPR identified by KUPR default tablespace TS_KUPR01 temporary tablespace TS_NOTTEMP01;
create user WIZARD identified by WIZARD default tablespace TS_WIZ01 temporary tablespace TS_NOTTEMP01;
create user IAS identified by IAS default tablespace TS_IAS01 temporary tablespace TS_NOTTEMP01;
create user IACF identified by IACF default tablespace TS_KRB01 temporary tablespace TS_NOTTEMP01;
'TEMPORARYTABLESPACE'||TEMPORARY_TABLESPACE||';'
create user TRACESVR identified by TRACESVR default tablespace SYSTEM temporary tablespace SYSTEM;
create user OUTLN identified by OUTLN default tablespace SYSTEM temporary tablespace SYSTEM;
create user ORS identified by ORS default tablespace TS_ORS01 temporary tablespace TS_ORSTEMP01;
create user AURORA$ORB$UNAUTHENTICATED identified by AURORA$ORB$UNAUTHENTICATED default tablespace SYSTEM temporary tablespace SYSTEM;
create user NOTICE identified by NOTICE default tablespace TS_NOT01 temporary tablespace TS_NOTTEMP01;
create user FRIEND identified by FRIEND default tablespace TS_FRI01 temporary tablespace TS_FRITEMP01;
create user ORANGE identified by ORANGE default tablespace TOOLS temporary tablespace TEMP;
create user IPSI identified by IPSI default tablespace TS_EEX01 temporary tablespace TS_EEXTEMP01;
create user KOREA identified by KOREA default tablespace TS_FRI01 temporary tablespace TS_FRITEMP01;
create user RNDCARD identified by RNDCARD default tablespace TS_FRI01 temporary tablespace TS_FRITEMP01;
create user SMBSUPP identified by SMBSUPP default tablespace TS_FRI01 temporary tablespace TS_FRITEMP01;
create user ADMIT identified by ADMIT default tablespace TS_EEX02 temporary tablespace TS_EEXTEMP02;
create user KRBIZ identified by KRBIZ default tablespace TS_KRB01 temporary tablespace TS_NOTTEMP01;
create user CTXSYS identified by CTXSYS default tablespace TS_CTX01 temporary tablespace TS_NOTTEMP01;
create user JONES identified by JONES default tablespace SYSTEM temporary tablespace SYSTEM;
create user BIZMAX identified by BIZMAX default tablespace TOOLS temporary tablespace TEMP;
create user DBSNMP identified by DBSNMP default tablespace SYSTEM temporary tablespace SYSTEM;
create user PERSON identified by PERSON default tablespace TS_PER01 temporary tablespace TS_NOTTEMP01;
create user SCOTT identified by SCOTT default tablespace SYSTEM temporary tablespace SYSTEM;
create user ADAMS identified by ADAMS default tablespace SYSTEM temporary tablespace SYSTEM;
create user CLARK identified by CLARK default tablespace SYSTEM temporary tablespace SYSTEM;
create user BLAKE identified by BLAKE default tablespace SYSTEM temporary tablespace SYSTEM;
create user CDL identified by CDL default tablespace TS_CDL01 temporary tablespace TS_CDLTEMP01;
create user EDUGRAD identified by EDUGRAD default tablespace TS_EDU01 temporary tablespace TS_EDUTEMP01;
create user KUPR identified by KUPR default tablespace TS_KUPR01 temporary tablespace TS_NOTTEMP01;
create user WIZARD identified by WIZARD default tablespace TS_WIZ01 temporary tablespace TS_NOTTEMP01;
create user IAS identified by IAS default tablespace TS_IAS01 temporary tablespace TS_NOTTEMP01;
create user IACF identified by IACF default tablespace TS_KRB01 temporary tablespace TS_NOTTEMP01;
28 rows selected.
#####################################################################
Grant
#####################################################################
SQL> select ' grant connect, resource to ' || username ||';'
from dba_users
where username not in ('SYS', 'SYSTEM');
SQL> select ' grant connect, resource to ' || username ||';'
from dba_users
where username not in ('SYS', 'SYSTEM');
''GRANTCONNECT,RESOURCETO'||USERNAME||';'
-----------------------------------------------------------
grant connect, resource to TRACESVR;
grant connect, resource to OUTLN;
grant connect, resource to ORS;
grant connect, resource to AURORA$ORB$UNAUTHENTICATED;
grant connect, resource to NOTICE;
grant connect, resource to FRIEND;
grant connect, resource to ORANGE;
grant connect, resource to IPSI;
grant connect, resource to KOREA;
grant connect, resource to RNDCARD;
grant connect, resource to SMBSUPP;
grant connect, resource to ADMIT;
grant connect, resource to KRBIZ;
grant connect, resource to CTXSYS;
grant connect, resource to JONES;
grant connect, resource to BIZMAX;
grant connect, resource to DBSNMP;
grant connect, resource to PERSON;
grant connect, resource to SCOTT;
grant connect, resource to ADAMS;
grant connect, resource to CLARK;
grant connect, resource to BLAKE;
grant connect, resource to CDL;
grant connect, resource to EDUGRAD;
grant connect, resource to KUPR;
grant connect, resource to WIZARD;
grant connect, resource to IAS;
grant connect, resource to IACF;
28 rows selected.
#####################################################################
Tablespace Size 조회
#####################################################################
SQL> select tablespace_name, sum(bytes)/1024/1024 || 'M' as MBYTES
from dba_data_files
group by tablespace_name
order by 1;
from dba_data_files
group by tablespace_name
order by 1;
TABLESPACE_NAME MBYTES
--------------------- -----------------------------------------
INDX 100M
IX_CDL01 300M
IX_EDU01 100M
IX_EEX01 500M
IX_EEX02 100M
IX_FRI01 100M
IX_IAS01 50M
IX_KRB01 500M
IX_KUPR01 100M
IX_NOT01 100M
IX_ORS01 500M
IX_PER01 200M
IX_WIZ01 30M
RBS 2516M
SYSTEM 300M
TEMP 500M
TOOLS 50M
TS_CDL01 1000M
TS_CTX01 50M
TS_EDU01 200M
TS_EEX01 1500M
TS_EEX02 1500M
TS_EEXTEMP01 500M
TS_EEXTEMP02 500M
TS_FRI01 300M
TS_FRITEMP01 1000M
TS_IAS01 100M
TS_KRB01 3200M
TS_KUPR01 200M
TS_NOT01 300M
TS_NOTTEMP01 1500M
TS_ORS01 1000M
TS_ORSTEMP01 500M
TS_PER01 500M
TS_WIZ01 100M
USERS 150M
36 rows selected.
#####################################################################
datafile이 두개 이상인 Tablespace 조회
#####################################################################
SQL> select tablespace_name , count(*)
from dba_data_files
having count(*)>1
group by tablespace_name
order by 1;
from dba_data_files
having count(*)>1
group by tablespace_name
order by 1;
TABLESPACE_NAME COUNT(*)
--------------------- ----------
RBS 3
TS_KRB01 3
SQL> select tablespace_name, file_name, bytes/1024/1024 || 'M' as MBYTES
from dba_data_files
where tablespace_name in ('RBS')
order by 1;
from dba_data_files
where tablespace_name in ('RBS')
order by 1;
TABLESPACE_NAME FILE_NAME MBYTES
---------------- --------------------------------------- --------------RBS /oracle/app/oracle/oradata/WWW/rbs01.dbf 516M
RBS /WWW/Data/rbs2.dbf 1000M
RBS /WWW/Index/rbs3.dbf 1000M
---------------- --------------------------------------- --------------RBS /oracle/app/oracle/oradata/WWW/rbs01.dbf 516M
RBS /WWW/Data/rbs2.dbf 1000M
RBS /WWW/Index/rbs3.dbf 1000M
SQL> select tablespace_name, file_name, bytes/1024/1024 || 'M' as MBYTES
from dba_data_files
where tablespace_name in ('TS_KRB01')
order by 1;
TABLESPACE_NAME FILE_NAME MBYTES
---------------- ----------------------------------- -------------TS_KRB01 /WWW/Data/ts_krb01.dbf 1200M
TS_KRB01 /WWW/Data/ts_krb02.dbf 1500M
TS_KRB01 /WWW/Data/ts_krb03.dbf 500M
---------------- ----------------------------------- -------------TS_KRB01 /WWW/Data/ts_krb01.dbf 1200M
TS_KRB01 /WWW/Data/ts_krb02.dbf 1500M
TS_KRB01 /WWW/Data/ts_krb03.dbf 500M
#####################################################################
Create Tablespace SQL Query Statement 작성
#####################################################################
SQL> set lines 200
SQL> set pages 200
SQL> col file_name for a50
SQL> col tablespace_name for a20
SQL> col Mbytes for a20
SQL> spool create_tablespace.sql
SQL> set echo off;
SQL> set heading off;
SQL> select ' create tablespace ' || tablespace_name ||
' datafile ''' || file_name || ''' size ' || bytes/1024/1024 || 'M autoextend off; '
from dba_data_files
where tablespace_name not in ('SYSTEM', 'USER', 'RBS', 'TOOLS', 'TEMP')
and tablespace_name not like '%TMP%'
and tablespace_name not like '%TEMP%';
SQL> set pages 200
SQL> col file_name for a50
SQL> col tablespace_name for a20
SQL> col Mbytes for a20
SQL> spool create_tablespace.sql
SQL> set echo off;
SQL> set heading off;
SQL> select ' create tablespace ' || tablespace_name ||
' datafile ''' || file_name || ''' size ' || bytes/1024/1024 || 'M autoextend off; '
from dba_data_files
where tablespace_name not in ('SYSTEM', 'USER', 'RBS', 'TOOLS', 'TEMP')
and tablespace_name not like '%TMP%'
and tablespace_name not like '%TEMP%';
'CREATETABLESPACE'||TABLESPACE_NAME||'DATAFILE'''||FILE_NAME||'''SIZE'||BYTES/1024/1024|
|'MAUTOEXTENDOFF;'
------------------------------------------------------------------------------------------------------
create tablespace USERS datafile '/oracle/app/oracle/oradata/WWW/users01.dbf' size 150M autoextend off;
create tablespace INDX datafile '/oracle/app/oracle/oradata/WWW/indx01.dbf' size 100M autoextend off;
create tablespace TS_NOT01 datafile '/WWW/Data/ts_not02.dbf' size 300M autoextend off;
create tablespace IX_NOT01 datafile '/WWW/Index/ix_not01.dbf' size 100M autoextend off;
create tablespace TS_FRI01 datafile '/WWW/Data/ts_fri02.dbf' size 300M autoextend off;
create tablespace IX_FRI01 datafile '/WWW/Index/ix_fri01.dbf' size 100M autoextend off;
create tablespace TS_ORS01 datafile '/WWW/Data/ts_ors01.dbf' size 1000M autoextend off;
create tablespace IX_ORS01 datafile '/WWW/Index/ix_ors01.dbf' size 500M autoextend off;
create tablespace IX_EEX01 datafile '/WWW/Index/ix_eex01.dbf' size 500M autoextend off;
create tablespace TS_EEX01 datafile '/WWW/Data/ts_eex02.dbf' size 1500M autoextend off;
create tablespace TS_EEX02 datafile '/WWW/Data/ts_eex04.dbf' size 1500M autoextend off;
create tablespace IX_EEX02 datafile '/WWW/Index/ix_eex02.dbf' size 100M autoextend off;
create tablespace TS_KRB01 datafile '/WWW/Data/ts_krb01.dbf' size 1200M autoextend off;
create tablespace IX_KRB01 datafile '/WWW/Index/ix_krb01.dbf' size 500M autoextend off;
create tablespace TS_CTX01 datafile '/WWW/Data/ts_ctx01.dbf' size 50M autoextend off;
create tablespace TS_PER01 datafile '/WWW/Data/ts_per01.dbf' size 500M autoextend off;
create tablespace IX_PER01 datafile '/WWW/Index/ix_per01.dbf' size 200M autoextend off;
create tablespace TS_KRB01 datafile '/WWW/Data/ts_krb02.dbf' size 1500M autoextend off;
create tablespace TS_CDL01 datafile '/WWW/Data/ts_cdl01.dbf' size 1000M autoextend off;
create tablespace IX_CDL01 datafile '/WWW/Index/ix_cdl01.dbf' size 300M autoextend off;
create tablespace TS_EDU01 datafile '/WWW/Data/ts_edu01.dbf' size 200M autoextend off;
create tablespace IX_EDU01 datafile '/WWW/Index/ix_edu01.dbf' size 100M autoextend off;
create tablespace TS_KUPR01 datafile '/WWW/Data/ts_kupr01.dbf' size 200M autoextend off;
create tablespace IX_KUPR01 datafile '/WWW/Index/ix_kupr01.dbf' size 100M autoextend off;
create tablespace TS_WIZ01 datafile '/WWW/Data/ts_wiz01.dbf' size 100M autoextend off;
create tablespace IX_WIZ01 datafile '/WWW/Index/ix_wiz01.dbf' size 30M autoextend off;
create tablespace TS_IAS01 datafile '/WWW/Data/ts_ias01.dbf' size 100M autoextend off;
create tablespace IX_IAS01 datafile '/WWW/Index/ix_ias01.dbf' size 50M autoextend off;
create tablespace TS_KRB01 datafile '/WWW/Data/ts_krb03.dbf' size 500M autoextend off;
29 rows selected.
SQL> spool off;
#####################################################################
Create Temporary Tablespace SQL Query Statement 작성
#####################################################################
SQL> set pages 200
SQL> set lines 200
SQL> col file_name for a50
SQL> col tablespace_name for a20
SQL> col Mbytes for a20
SQL> spool create_temp_tablespace.sql
SQL> set echo off;
SQL> set heading off;
SQL> select ' create temporary tablespace ' || tablespace_name ||
' tempfile ''' || file_name || ''' size ' || bytes/1024/1024 || 'M autoextensible off; '
from dba_data_files
where tablespace_name like '%TMP%'
or tablespace_name like '%TEMP%';
SQL> set lines 200
SQL> col file_name for a50
SQL> col tablespace_name for a20
SQL> col Mbytes for a20
SQL> spool create_temp_tablespace.sql
SQL> set echo off;
SQL> set heading off;
SQL> select ' create temporary tablespace ' || tablespace_name ||
' tempfile ''' || file_name || ''' size ' || bytes/1024/1024 || 'M autoextensible off; '
from dba_data_files
where tablespace_name like '%TMP%'
or tablespace_name like '%TEMP%';
create temporary tablespace TEMP tempfile '/oracle/app/oracle/oradata/WWW/temp01.dbf' size 500M autoextensible off;
create temporary tablespace TS_NOTTEMP01 tempfile '/WWW/Data/ts_not01.dbf' size 1500M autoextensible off;
create temporary tablespace TS_FRITEMP01 tempfile '/WWW/Data/ts_fri01.dbf' size 1000M autoextensible off;
create temporary tablespace TS_ORSTEMP01 tempfile '/WWW/Data/ts_orstemp01.dbf' size 500M autoextensible off;
create temporary tablespace TS_EEXTEMP01 tempfile '/WWW/Data/ts_eex01.dbf' size 500M autoextensible off;
create temporary tablespace TS_EEXTEMP02 tempfile '/WWW/Data/ts_eex03.dbf' size 500M autoextensible off;
create temporary tablespace TS_NOTTEMP01 tempfile '/WWW/Data/ts_not01.dbf' size 1500M autoextensible off;
create temporary tablespace TS_FRITEMP01 tempfile '/WWW/Data/ts_fri01.dbf' size 1000M autoextensible off;
create temporary tablespace TS_ORSTEMP01 tempfile '/WWW/Data/ts_orstemp01.dbf' size 500M autoextensible off;
create temporary tablespace TS_EEXTEMP01 tempfile '/WWW/Data/ts_eex01.dbf' size 500M autoextensible off;
create temporary tablespace TS_EEXTEMP02 tempfile '/WWW/Data/ts_eex03.dbf' size 500M autoextensible off;
6 rows selected.
SQL> spool off;
#####################################################################
Import Script 작성
#####################################################################
SQL> set pages 200
SQL> set lines 200
SQL> col file_name for a50
SQL> col tablespace_name for a20
SQL> col Mbytes for a20
SQL> spool import_user.sh
SQL> set echo off;
SQL> set heading off;
SQL> select 'imp system/manager file= filesize=6000m rows=y fromuser=' || username ||
2 ' touser=' || username || ' log=' || username || '.log'
3 from dba_users
4 where username not in ('SYS', 'SYSTEM');
SQL> set lines 200
SQL> col file_name for a50
SQL> col tablespace_name for a20
SQL> col Mbytes for a20
SQL> spool import_user.sh
SQL> set echo off;
SQL> set heading off;
SQL> select 'imp system/manager file= filesize=6000m rows=y fromuser=' || username ||
2 ' touser=' || username || ' log=' || username || '.log'
3 from dba_users
4 where username not in ('SYS', 'SYSTEM');
imp system/manager file= filesize=6000m rows=y fromuser=TRACESVR touser=TRACESVR log=TRACESVR.log
imp system/manager file= filesize=6000m rows=y fromuser=OUTLN touser=OUTLN log=OUTLN.log
imp system/manager file= filesize=6000m rows=y fromuser=ORS touser=ORS log=ORS.log
imp system/manager file= filesize=6000m rows=y fromuser=AURORA$ORB$UNAUTHENTICATED touser=AURORA$ORB$UNAUTHENTICATED log=AURORA$ORB$UNAUTHENTICATED.log
imp system/manager file= filesize=6000m rows=y fromuser=NOTICE touser=NOTICE log=NOTICE.log
imp system/manager file= filesize=6000m rows=y fromuser=FRIEND touser=FRIEND log=FRIEND.log
imp system/manager file= filesize=6000m rows=y fromuser=ORANGE touser=ORANGE log=ORANGE.log
imp system/manager file= filesize=6000m rows=y fromuser=IPSI touser=IPSI log=IPSI.log
imp system/manager file= filesize=6000m rows=y fromuser=KOREA touser=KOREA log=KOREA.log
imp system/manager file= filesize=6000m rows=y fromuser=RNDCARD touser=RNDCARD log=RNDCARD.log
imp system/manager file= filesize=6000m rows=y fromuser=SMBSUPP touser=SMBSUPP log=SMBSUPP.log
imp system/manager file= filesize=6000m rows=y fromuser=ADMIT touser=ADMIT log=ADMIT.log
imp system/manager file= filesize=6000m rows=y fromuser=KRBIZ touser=KRBIZ log=KRBIZ.log
imp system/manager file= filesize=6000m rows=y fromuser=CTXSYS touser=CTXSYS log=CTXSYS.log
imp system/manager file= filesize=6000m rows=y fromuser=JONES touser=JONES log=JONES.log
imp system/manager file= filesize=6000m rows=y fromuser=BIZMAX touser=BIZMAX log=BIZMAX.log
imp system/manager file= filesize=6000m rows=y fromuser=DBSNMP touser=DBSNMP log=DBSNMP.log
imp system/manager file= filesize=6000m rows=y fromuser=PERSON touser=PERSON log=PERSON.log
imp system/manager file= filesize=6000m rows=y fromuser=SCOTT touser=SCOTT log=SCOTT.log
imp system/manager file= filesize=6000m rows=y fromuser=ADAMS touser=ADAMS log=ADAMS.log
imp system/manager file= filesize=6000m rows=y fromuser=CLARK touser=CLARK log=CLARK.log
imp system/manager file= filesize=6000m rows=y fromuser=BLAKE touser=BLAKE log=BLAKE.log
imp system/manager file= filesize=6000m rows=y fromuser=CDL touser=CDL log=CDL.log
imp system/manager file= filesize=6000m rows=y fromuser=EDUGRAD touser=EDUGRAD log=EDUGRAD.log
imp system/manager file= filesize=6000m rows=y fromuser=KUPR touser=KUPR log=KUPR.log
imp system/manager file= filesize=6000m rows=y fromuser=WIZARD touser=WIZARD log=WIZARD.log
imp system/manager file= filesize=6000m rows=y fromuser=IAS touser=IAS log=IAS.log
imp system/manager file= filesize=6000m rows=y fromuser=IACF touser=IACF log=IACF.log
imp system/manager file= filesize=6000m rows=y fromuser=OUTLN touser=OUTLN log=OUTLN.log
imp system/manager file= filesize=6000m rows=y fromuser=ORS touser=ORS log=ORS.log
imp system/manager file= filesize=6000m rows=y fromuser=AURORA$ORB$UNAUTHENTICATED touser=AURORA$ORB$UNAUTHENTICATED log=AURORA$ORB$UNAUTHENTICATED.log
imp system/manager file= filesize=6000m rows=y fromuser=NOTICE touser=NOTICE log=NOTICE.log
imp system/manager file= filesize=6000m rows=y fromuser=FRIEND touser=FRIEND log=FRIEND.log
imp system/manager file= filesize=6000m rows=y fromuser=ORANGE touser=ORANGE log=ORANGE.log
imp system/manager file= filesize=6000m rows=y fromuser=IPSI touser=IPSI log=IPSI.log
imp system/manager file= filesize=6000m rows=y fromuser=KOREA touser=KOREA log=KOREA.log
imp system/manager file= filesize=6000m rows=y fromuser=RNDCARD touser=RNDCARD log=RNDCARD.log
imp system/manager file= filesize=6000m rows=y fromuser=SMBSUPP touser=SMBSUPP log=SMBSUPP.log
imp system/manager file= filesize=6000m rows=y fromuser=ADMIT touser=ADMIT log=ADMIT.log
imp system/manager file= filesize=6000m rows=y fromuser=KRBIZ touser=KRBIZ log=KRBIZ.log
imp system/manager file= filesize=6000m rows=y fromuser=CTXSYS touser=CTXSYS log=CTXSYS.log
imp system/manager file= filesize=6000m rows=y fromuser=JONES touser=JONES log=JONES.log
imp system/manager file= filesize=6000m rows=y fromuser=BIZMAX touser=BIZMAX log=BIZMAX.log
imp system/manager file= filesize=6000m rows=y fromuser=DBSNMP touser=DBSNMP log=DBSNMP.log
imp system/manager file= filesize=6000m rows=y fromuser=PERSON touser=PERSON log=PERSON.log
imp system/manager file= filesize=6000m rows=y fromuser=SCOTT touser=SCOTT log=SCOTT.log
imp system/manager file= filesize=6000m rows=y fromuser=ADAMS touser=ADAMS log=ADAMS.log
imp system/manager file= filesize=6000m rows=y fromuser=CLARK touser=CLARK log=CLARK.log
imp system/manager file= filesize=6000m rows=y fromuser=BLAKE touser=BLAKE log=BLAKE.log
imp system/manager file= filesize=6000m rows=y fromuser=CDL touser=CDL log=CDL.log
imp system/manager file= filesize=6000m rows=y fromuser=EDUGRAD touser=EDUGRAD log=EDUGRAD.log
imp system/manager file= filesize=6000m rows=y fromuser=KUPR touser=KUPR log=KUPR.log
imp system/manager file= filesize=6000m rows=y fromuser=WIZARD touser=WIZARD log=WIZARD.log
imp system/manager file= filesize=6000m rows=y fromuser=IAS touser=IAS log=IAS.log
imp system/manager file= filesize=6000m rows=y fromuser=IACF touser=IACF log=IACF.log
28 rows selected.
SQL> spool off;
#####################################################################
user별 object 조회
#####################################################################
SQL>col object_name format a25;
SQL>SELECT object_name, object_type
FROM user_objects
WHERE object_type = 'INDEX';
OBJECT_NAME OBJECT_TYPE
------------------------- ------------------
BIANRY_PK INDEX
BINARY_FK INDEX
BOARD_CTNT_LST_FK1 INDEX
BOARD_CTNT_LST_FK2 INDEX
BOARD_CTNT_LST_PK INDEX
BOARD_FK1 INDEX
BOARD_PK INDEX
COMMUNITY_TYPE_IDX INDEX
COMMUNITY_TYPE_PK INDEX
COMM_CTNT_LST_FK1 INDEX
COMM_CTNT_LST_FK2 INDEX
...
27개의 행이 선택되었습니다.
SQL>col object_name format a25;
SQL>SELECT object_name, object_type
FROM user_objects
WHERE object_type = 'INDEX';
OBJECT_NAME OBJECT_TYPE
------------------------- ------------------
BIANRY_PK INDEX
BINARY_FK INDEX
BOARD_CTNT_LST_FK1 INDEX
BOARD_CTNT_LST_FK2 INDEX
BOARD_CTNT_LST_PK INDEX
BOARD_FK1 INDEX
BOARD_PK INDEX
COMMUNITY_TYPE_IDX INDEX
COMMUNITY_TYPE_PK INDEX
COMM_CTNT_LST_FK1 INDEX
COMM_CTNT_LST_FK2 INDEX
...
27개의 행이 선택되었습니다.
참고>
object_type 조회
SQL>SELECT object_type
FROM user_objects
GROUP BY object_type;
OBJECT_TYPE
------------------
INDEX
SEQUENCE
TABLE
object_type 조회
SQL>SELECT object_type
FROM user_objects
GROUP BY object_type;
OBJECT_TYPE
------------------
INDEX
SEQUENCE
TABLE
'IT > Oracle' 카테고리의 다른 글
| ORA-15077 Could Not Locate ASM Instance Serving A Required Diskgroup (0) | 2008.11.26 |
|---|---|
| 오라클 재설치 (0) | 2008.11.26 |
| Cold backup (0) | 2008.09.29 |
| 컨트롤 파일의 재생성(DB명 변경) (0) | 2008.09.17 |
| Oracle admin tip (0) | 2008.07.25 |