#####################################################################
### 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
#####################################################################
### 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;
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
54 rows selected.
#####################################################################
### Datafile Size 조회
#####################################################################
SQL> select sum(bytes)/1024/1024 from dba_data_files;
SUM(BYTES)/1024/1024
--------------------
20146
SQL> select sum(bytes)/1024/1024 from dba_segments;
SUM(BYTES)/1024/1024
--------------------
6561.64453
#####################################################################
### user별 tablespace 조회
#####################################################################
SQL> select username, default_tablespace, temporary_tablespace from dba_users
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
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');
'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;
28 rows selected.
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;
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.
#####################################################################
### Temporary Tablespace의 Tempfile 조회
#####################################################################
#####################################################################
### datafile이 두개 이상인 Tablespace 조회
#####################################################################
SQL> select tablespace_name , count(*)
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;
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
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
#####################################################################
### 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%';
'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%';
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;
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');
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
28 rows selected.
SQL> spool off;
#####################################################################
### user별 object 조회
#####################################################################
IMP-00017: following statement failed with ORACLE error 2264:
"ALTER TABLE "VRLOGO" ADD CONSTRAINT "SYS_C002772" PRIMARY KEY ("SEQ_NO") U"
"SING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 102400 NEXT 5"
"3248 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST "
"GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_KRB01" ENABLE "
IMP-00003: ORACLE error 2264 encountered
ORA-02264: name already used by an existing constraint
. . importing table "VRMEMBER" 0 rows imported
. . importing table "VRUSED_SERVICE" 8 rows imported
About to enable constraints...
IMP-00017: following statement failed with ORACLE error 2430:
"ALTER TABLE "VRLOGO" ENABLE CONSTRAINT "SYS_C002772""
Import terminated successfully with warnings.
'ORACLE > Backup & Recovery' 카테고리의 다른 글
| INCREMENTAL, CUMULATIVE, COMPLETE EXPORT ＆ IMPORT (0) | 2008.02.12 |
|---|---|
| rman에서 3일치 놔두고 아카이브로그지우기 (0) | 2008.02.11 |
| Oracle 9i RMAN 명령어 (0) | 2008.01.28 |
| Configuration of Load Balancing and Transparent Application Failover (0) | 2007.12.11 |
| 오라클(oracle) 백업(exp), 복구(imp) 하기 (0) | 2006.05.09 |
