<![CDATA[
Oracle Administration을 정리하다가 간단히 찾아고 조금이나마 도움이 되시라고 정리해서 올립니다. <BR>너무 단시간에 두서없이 써서 보기도 좋지 않지만 필요하신 분들 심심할때 하나씩 <BR>해보세요.(다들 아시는거지만~~) <BR>아래 tips 는 하나의 database에서 작성한 것이 아니므로 각종 정보들(file들의 위치등)이 <BR>tip마다 다를 수 있습니다. 각 tip은 개개의 것으로 생각하시고 응용하시기 바랍니다. <BR>혹시 틀린 내용 발견되면 mail주세요. 바로 수정하겠습니다. <BR>편집 이쁘게 못해서 죄송합니다. <BR>나름대로 사연있는 글입니다. <BR>정리하다가 날려먹어서 한 몇일 더 고생해서 작성한겁니다.^^; <BR><BR>님들도 좋은 정보 있으시면 공유하시죠. <BR><BR><BR>================================================================================================ <BR>1. DBMS = database(data file & control file & redo log file) + <BR> instance(memory & background processes) <BR>================================================================================================ <BR><BR><BR>2. Oracle Architecture Component <BR><BR>================================================================================================ <BR><BR>* Oracle Instance 확인 : v$instance <BR><BR>SQL> select instance_name from v$instance; <BR><BR>INSTANCE_NAME <BR>---------------- <BR>IBM <BR><BR>================================================================================================ <BR><BR>* datafile들의 경로 및 정보 : v$datafile <BR>SQL> select name from v$datafile; <BR><BR>NAME <BR>-------------------------------------------------------------------------------- <BR>/oracle/ora_data/system/system01.dbf <BR>/oracle/ora_data/data/tools01.dbf <BR>/oracle/ora_data/data/rbs01.dbf <BR>/oracle/ora_data/data/temp01.dbf <BR>/oracle/ora_data/data/users01.dbf <BR><BR>================================================================================================ <BR><BR>* control file의 경로 및 정보 : v$controlfile; <BR><BR>SQL> select name from v$controlfile; <BR><BR>NAME <BR>-------------------------------------------------------------------------------- <BR>/oracle/ora_data/contr1/ora_control1 <BR>/oracle/ora_data/contr2/ora_control2 <BR><BR>================================================================================================ <BR><BR>* logfile의 경로 및 정보 : v$logfile <BR><BR>SQL> select member from v$logfile; <BR><BR>MEMBER <BR>-------------------------------------------------------------------------------- <BR>/oracle/ora_data/redolog_a/redo1a.log <BR>/oracle/ora_data/redolog_b/redo1b.log <BR>/oracle/ora_data/redolog_a/redo2a.log <BR>/oracle/ora_data/redolog_b/redo2b.log <BR>/oracle/ora_data/redolog_a/redo3a.log <BR>/oracle/ora_data/redolog_b/redo3b.log <BR><BR>================================================================================================ <BR><BR>* System Global Area 내용을 조회 <BR><BR>SQL> select * from v$sga; <BR><BR>NAME VALUE <BR>-------------------- ---------- <BR>Fixed Size 108588 <BR>Variable Size 27631616 <BR>Database Buffers 2252800 <BR>Redo Buffers 77824 <BR><BR>SQL> show sga <BR><BR>Total System Global Area 30070828 bytes <BR>Fixed Size 108588 bytes <BR>Variable Size 27631616 bytes <BR>Database Buffers 2252800 bytes <BR>Redo Buffers 77824 bytes <BR><BR>================================================================================================ <BR><BR>* 현재 수행중인 background process들을 확인 <BR><BR>SQL> select paddr,name,description from v$bgprocess where paddr>'00'; <BR><BR>PADDR NAME DESCRIPTION <BR>---------------- ----- ---------------------------------------------------------------- <BR>070000000139ABC0 PMON process cleanup <BR>070000000139AFD0 DBW0 db writer process 0 <BR>070000000139B3E0 LGWR Redo etc. <BR>070000000139B7F0 CKPT checkpoint <BR>070000000139BC00 SMON System Monitor Process <BR>070000000139C010 RECO distributed recovery <BR><BR>SQL> !ps -ef|grep ora|grep <SID><BR><BR> oracle 25148 1 0 19:25:34 - 0:00 ora_reco_IBM <BR> oracle 60576 1 0 19:25:34 - 0:00 ora_smon_IBM <BR> oracle 60782 1 0 19:25:34 - 0:00 ora_pmon_IBM <BR> oracle 70166 1 0 19:25:34 - 0:00 ora_lgwr_IBM <BR> oracle 72248 1 0 19:25:34 - 0:00 ora_ckpt_IBM <BR> oracle 84918 1 0 19:25:34 - 0:00 ora_dbw0_IBM <BR> <BR>================================================================================================ <BR><BR>* 초기화 파라미터 파일 : init<SID>.ora <BR><BR>================================================================================================ <BR><BR>* database log 모드 확인 <BR><BR>SQL> connect internal <BR>Connected. <BR><BR>SQL> archive log list <BR>Database log mode No Archive Mode <BR>Automatic archival Disabled <BR>Archive destination /oracle/app/oracle/product/8.1.6/dbs/arch <BR>Oldest online log sequence 20 <BR>Current log sequence 22 <BR><BR>SQL> select log_mode from v$database; <BR><BR>LOG_MODE <BR>------------ <BR>NOARCHIVELOG <BR><BR>================================================================================================ <BR><BR><BR>3. Managing an Oracle Instance <BR><BR>================================================================================================ <BR><BR>단계별 : <BR>shutdown : oracle이 내려가 있는 상태 <BR>nomount : instance started(SGA, B.G process를 시작 init<SID>.ora에서 읽어서) <BR>alert, trace file open <BR>- 이 단계에서 할 수 있는 것은 <BR> a. db creation <BR>- 이 상태에서도 볼수있는 view <BR> v$parameter <BR> v$dga <BR> v$option <BR> v$process <BR> v$session <BR> v$version <BR> v$instance <BR><BR>mount : control file opened for this instance <BR>- 이 단계에서 할 수 있는 것은 control file의 내용을 변경하는것 <BR> a. archivelog mode로 변환 <BR> b. data file/redo log file rename시 <BR> c. recovery시 <BR><BR>- SQL>alter database open read only; <BR> 로 하게되면 data file에 writing을 허용 안함. <BR><BR>open : control file에 기술된 모든 files open <BR><BR>================================================================================================ <BR><BR>* parameter 변경 종류 <BR><BR>a. init<SID>.ora 에서 변경 <BR>b. alter session set ~ <BR>c. alter system set ~ => shutdown 될때까지 변경된것 유효 <BR> alter system deffered set ~ => 현재 session에서만 변경된것 유효 <BR><BR>================================================================================================ <BR><BR>* 특정 session 죽이기 <BR><BR>SQL> select sid, serial#,username,status from v$session; => (특정 user는 where username='SCOTT'로) <BR> SID SERIAL# USERNAME STATUS <BR>---------- ---------- ------------------------------ -------- <BR> 1 1 ACTIVE <BR> 2 1 ACTIVE <BR> 3 1 ACTIVE <BR> 4 1 ACTIVE <BR> 5 1 ACTIVE <BR> 6 1 ACTIVE <BR> 7 1 SYS ACTIVE <BR><BR>SQL> alter system kill session '7,3' -- 7은 sid, 3은 serial# <BR><BR>================================================================================================ <BR><BR>* alert file 과 trace file <BR>- alert file은 꼭 1개, 중요한사건,시간순으로 (startup,shutdown,recovery) <BR>- trace file은 여러개 가능, background process는 background_dump_dest에 생기고 server process는 <BR> user_dump_dest에 생성된다. <BR><BR>================================================================================================ <BR><BR><BR>4. Creating a Database <BR><BR>================================================================================================ <BR><BR>* Create a Database Manually <BR><BR>a. OS Environment setting <BR><BR>.profile에 ORACLE_HOME,ORACLE_SID,ORA_NLS33,PATH,(ORACLE_BASE) 등을 편집한다. <BR><BR>ex) <BR>DISPLAY=swsvrctr:0.0 <BR>ORACLE_HOME=/oracle/app/oracle/product/8.1.7 <BR>PATH=$ORACLE_PATH/bin:/usr/ccs/bin:$PATH <BR>NLS_LANG=AMERICAN_AMERICA.KO16KSC5601 <BR>ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data <BR>ORACLE_SID=IBM <BR><BR>b. init.ora file을 copy하고 편집한다. <BR><INITKYS.ORA>file <BR>db_name=KYS <BR>control_files = (/home/oracle/data02/control/control01.ctl,/home/oracle/data02/control/control02.ctl) <BR>db_block_size = 8192 <BR><BR>기본적으로 위 두개 parameter외에 <BR>rollback_segments=(rbs1,rbs2,..) =>나중에 rollback segment생성후 DB start시 Online되는 rbs지정 <BR>background_dump_dest=/home/oracle/data02/bdump <BR>user_dump_dest=/home/oracle/data02/udump <BR>core_dump_dest=/home/oracle/data02/cdump <BR><BR>c. Starting the Instance <BR><BR>SQL> startup nomount <BR>SQL> startup nomount pfile=initKYS.ora <BR><BR>SQL> create database KYS <BR> 2 maxlogfiles 5 <BR> 3 maxlogmembers 5 <BR> 4 maxdatafiles 100 <BR> 5 maxloghistory 100 <BR> 6 logfile <BR> 7 group 1 ('/home/oracle/data02/redolog/log1a.rdo','/home/oracle/data02/redolog2/log1b.rdo') size 1m, <BR> 8 group 2 ('/home/oracle/data02/redolog/log2a.rdo','/home/oracle/data02/redolog2/log2b.rdo') size 1m <BR> 9 datafile <BR>10 '/home/oracle/data02/data/system01.dbf' size 50m autoextend on <BR>11 character set "KO16KSC5601"; <BR><BR>일단 여기까지 database는 생성이 되었다. <BR>이후부터는 추가적인 작업이다. <BR><BR>d. 추가 system rollback segment 생성 <BR><BR>SQL> create rollback segment r0 tablespace system <BR> 2 storage (initial 16k next 16k minextents 2 maxextents 10); <BR><BR> <BR>e. rollback sement online <BR><BR>SQL> alter rollback segment r0 online; <BR><BR><BR>f. rollback segment tablespace 생성 & datafile 저장위치, 크기 및 초기값 지정 <BR><BR>SQL> create tablespace rbs <BR> 2 datafile '/home/oracle/data02/data/rbs01.dbf' size 300m <BR> 3 default storage( <BR> 4 initial 4M <BR> 5 next 4M <BR> 6 pctincrease 0 <BR> 7 minextents 10 <BR> 8 maxextents unlimited); <BR><BR>g. rollback segment 생성 <BR><BR>SQL> create rollback segment r01 tablespace rbs <BR> 2 storage (minextents 10 optimal 40M); <BR>SQL> create rollback segment r02 tablespace rbs <BR> 2 storage (minextents 10 optimal 40M); <BR>SQL> create rollback segment r03 tablespace rbs <BR> 2 storage (minextents 10 optimal 40M); <BR>SQL> create rollback segment r04 tablespace rbs <BR> 2 storage (minextents 10 optimal 40M); <BR><BR><BR>h. rollback segment online <BR><BR>SQL> alter rollback segment r01 online; <BR>SQL> alter rollback segment r02 online; <BR>SQL> alter rollback segment r03 online; <BR>SQL> alter rollback segment r04 online; <BR><BR><BR>i. 추가 system rollback segment off-line 및 삭제 <BR><BR>SQL> alter rollback segment r0 offline; <BR>SQL> drop rollback segment r0; <BR><BR>j. sorting 작업시 필요한 temporary tablespace 생성 & datafile 저장 위치, 크기 및 초기값 지정 <BR><BR>SQL> create tablespace temp <BR> 2 datafile '/home/oracle/data02/data/temp01.dbf' size 300 temporary <BR> 3 default storage( <BR> 4 initial 4M <BR> 5 next 4M <BR> 6 maxextents unlimited <BR> 7 pctincrease 0); <BR> <BR> <BR>k. 추가 tablespace 생성 & data file 저장 위치 및 크기 지정 <BR><BR>SQL> create tablespace tools <BR> 2 datafile '/home/oracle/data02/data/tools.dbf' size 50m <BR> 3 default storage( <BR> 4 maxextents 505 <BR> 5 pctincrease 0); <BR> <BR>SQL> create tablespace users <BR> 2 datafile '/home/oracle/data02/data/user01.dbf' size 30M <BR> 3 default storage( <BR> 4 maxextents 505 <BR> 5 pctincrease 0); <BR> <BR>l. 작업 환경에서 추가적으로 필요한 tablespace는 위의 방법으로 생성한다. <BR><BR><BR>================================================================================================ <BR><BR><BR>5. Data Dictionary and Standard Package <BR><BR>================================================================================================ <BR><BR>* database 생성후 돌려줘야 할 script <BR><BR>$ORACLE_HOME/rdbms/admin/catalog.sql ==> dictionary views, export utility views 생성 <BR>$ORACLE_HOME/rdbms/admin/catproc.sql ==> procedures, functions 생성 <BR>$ORACLE_HOME/rdbms/admin/catdbsyn.sql ==> synonyms 생성 <BR><BR>================================================================================================ <BR><BR>* Dictionary list 확인 <BR><BR>SQL> col table_name format a30 <BR>SQL> col comments format a45 <BR>SQL> set pages 800 <BR>SQL> spool dictionary.lst <BR>SQL> select * from dictionary order by 1 ==> 전체 dictionary의 list를 볼 수 있다. <BR>SQL> spool off <BR>SQL> ed sictionary.lst <BR>SQL> select * from dictionary where table_name like '%TABLE%'; ==> table 관련 dictionary <BR>SQL> select * from dictionary where table_name like '%INDEX%'; ==> index 관련 dictionary <BR><BR>================================================================================================ <BR><BR>* 유용한 dictionary <BR><BR>TABLE_NAME COMMENTS <BR>------------------------------ --------------------------------------------- <BR>DBA_USERS Information about all users of the database <BR>DBA_TABLESPACES Description of all tablespaces <BR>DBA_DATA_FILES Information about database data files <BR>DBA_FREE_SPACE Free extents in all tablespaces <BR>DBA_OBJECTS All objects in the database <BR>DBA_SEGMENTS Storage allocated for all database segments <BR>DBA_ROLLBACK_SEGS Description of rollback segments <BR>DBA_EXTENTS Extents comprising all segments in the database <BR>DBA_TABLES Description of all relational tables in the d <BR> atabase <BR>DBA_INDEXES Description for all indexes in the database <BR>DBA_VIEWS Description of all views in the database <BR>DBA_TRIGGERS All triggers in the database <BR>DBA_SOURCE Source of all stored objects in the database <BR><BR>================================================================================================ <BR><BR>* sample Query <BR><BR>SQL> select username,default_tablespace,temporary_tablespace from dba_users; <BR>SQL> select tablespace_name,bytes,file_name from dba_data_files; <BR>SQL> select tablespace_name,count(*),sum(bytes) from dba_free_space <BR> 2 group by tablespace_name; <BR> <BR>================================================================================================ <BR><BR><BR>6. Maintiaining the Contorol File <BR><BR>================================================================================================ <BR><BR>* Control File 리스트 조회 <BR><BR>SQL> select name from v$controlfile; <BR><BR>NAME <BR>-------------------------------------------------------------------------------- <BR>/home/oracle/data01/oradata/IBM/control01.ctl <BR>/home/oracle/data01/oradata/IBM/control02.ctl <BR>/home/oracle/data01/oradata/IBM/control03.ctl <BR><BR>================================================================================================ <BR><BR>* Control File 을 하나 추가해보자 <BR><BR>a. database shutdown <BR>SQL> shutdown immediate <BR><BR>b. control file 복사(os상 물리적인 복사) <BR>/home/oracle/data01/oradata/IBM> cp control03.ctl control04.ctl ==> 실제는 다른 disk로 복사해야함 <BR> 문제발생을 대비해 분리하는것임. <BR><BR>c. Parameter File 편집 <BR>control_files = ("/home/oracle/data01/oradata/IBM/control01.ctl", <BR>"/home/oracle/data01/oradata/IBM/control02.ctl", <BR>"/home/oracle/data01/oradata/IBM/control03.ctl", <BR>"/home/oracle/data01/oradata/IBM/control04,ctl") <BR><BR>d. database startup & 확인 <BR>SQL> startup <BR>SQL> select name from v$controlfile; <BR><BR>NAME <BR>-------------------------------------------------------------------------------- <BR>/home/oracle/data01/oradata/IBM/control01.ctl <BR>/home/oracle/data01/oradata/IBM/control02.ctl <BR>/home/oracle/data01/oradata/IBM/control03.ctl <BR>/home/oracle/data01/oradata/IBM/control04.ctl ==> 하나 더 추가되었지요...(실제는 다른disk로) <BR><BR>================================================================================================ <BR><BR><BR>7. Multiplexing Redo Log Files <BR><BR>================================================================================================ <BR><BR>* Redo Log File 리스트 조회 <BR><BR>SQL> select group#,sequence#,bytes,members,status from v$log; <BR><BR> GROUP# SEQUENCE# BYTES MEMBERS STATUS <BR>---------- ---------- ---------- ---------- -------------------------------- <BR> 1 862 512000 1 CURRENT <BR> 2 860 512000 1 INACTIVE <BR> 3 861 512000 1 INACTIVE <BR><BR>SQL> select * from v$logfile; <BR><BR> GROUP# STATUS MEMBER <BR>---------- -------------- -------------------------------------------------- <BR> 1 /home/oracle/data01/oradata/IBM/redo03.log <BR> 2 /home/oracle/data01/oradata/IBM/redo02.log <BR> 3 /home/oracle/data01/oradata/IBM/redo01.log <BR><BR>================================================================================================ <BR><BR>* Log Group 추가(기존 로그 파일과 동일한 사이즈로) <BR><BR>SQL> alter database add logfile <BR> 2 '/home/oracle/data01/oradata/IBM/redo04.log' size 200k; <BR><BR>SQL> select group#,sequence#,bytes,members,status from v$log; <BR><BR> GROUP# SEQUENCE# BYTES MEMBERS STATUS <BR>---------- ---------- ---------- ---------- -------------------------------- <BR> 1 862 512000 1 CURRENT <BR> 2 860 512000 1 INACTIVE <BR> 3 861 512000 1 INACTIVE <BR> 4 0 204800 1 UNUSED <BR><BR>SQL> select * from v$logfile; <BR><BR> GROUP# STATUS MEMBER <BR>---------- -------------- -------------------------------------------------- <BR> 1 /home/oracle/data01/oradata/IBM/redo03.log <BR> 2 /home/oracle/data01/oradata/IBM/redo02.log <BR> 3 /home/oracle/data01/oradata/IBM/redo01.log <BR> 4 /home/oracle/data01/oradata/IBM/redo04.log <BR><BR>================================================================================================ <BR><BR>* Log Group 별 멤버 파일 추가 ==> backup 시 risk줄이기 위해 실제는 다른 disk에 해야함. <BR><BR>SQL> alter database add logfile member <BR> 2 '/home/oracle/data01/oradata/IBM/redo01b.log' to group 1, <BR> 3 '/home/oracle/data01/oradata/IBM/redo02b.log' to group 2, <BR> 4 '/home/oracle/data01/oradata/IBM/redo03b.log' to group 3, <BR> 5 '/home/oracle/data01/oradata/IBM/redo04b.log' to group 4; <BR><BR>================================================================================================ <BR><BR>* 확인 <BR><BR>SQL> !ls /home/oracle/data01/oradata/IBM/*.log <BR>/home/oracle/data01/oradata/IBM/redo01.log /home/oracle/data01/oradata/IBM/redo03.log <BR>/home/oracle/data01/oradata/IBM/redo01b.log /home/oracle/data01/oradata/IBM/redo03b.log <BR>/home/oracle/data01/oradata/IBM/redo02.log /home/oracle/data01/oradata/IBM/redo04.log <BR>/home/oracle/data01/oradata/IBM/redo02b.log /home/oracle/data01/oradata/IBM/redo04b.log <BR><BR>SQL> select group#,sequence#,bytes,members,status from v$log; <BR><BR> GROUP# SEQUENCE# BYTES MEMBERS STATUS <BR>---------- ---------- ---------- ---------- -------------------------------- <BR> 1 862 512000 2 CURRENT <BR> 2 860 512000 2 INACTIVE <BR> 3 861 512000 2 INACTIVE <BR> 4 0 204800 2 UNUSED ==> 아직 한번도 사용되지 않음 <BR><BR>SQL> select * from v$logfile; <BR><BR> GROUP# STATUS MEMBER <BR>---------- -------------- -------------------------------------------------- <BR> 1 /home/oracle/data01/oradata/IBM/redo03.log <BR> 2 /home/oracle/data01/oradata/IBM/redo02.log <BR> 3 /home/oracle/data01/oradata/IBM/redo01.log <BR> 4 /home/oracle/data01/oradata/IBM/redo04.log <BR> 1 INVALID /home/oracle/data01/oradata/IBM/redo01b.log <BR> 2 INVALID /home/oracle/data01/oradata/IBM/redo02b.log <BR> 3 INVALID /home/oracle/data01/oradata/IBM/redo03b.log <BR> 4 INVALID /home/oracle/data01/oradata/IBM/redo04b.log <BR><BR><BR>==> 현재 사용되고 있는 log group 은 group 1이고 나중에 추가한 member들은 invalid 한 상태이다. <BR>강제로 log switch를 일으켜서 valid하게 바꾸자. <BR><BR>SQL> alter system switch logfile; <BR><BR>SQL> select group#,sequence#,bytes,members,status from v$log; <BR><BR> GROUP# SEQUENCE# BYTES MEMBERS STATUS <BR>---------- ---------- ---------- ---------- -------------------------------- <BR> 1 862 512000 2 ACTIVE <BR> 2 860 512000 2 INACTIVE <BR> 3 861 512000 2 INACTIVE <BR> 4 863 204800 2 CURRENT ==> unused에서 바뀜. <BR><BR>SQL> select * from v$logfile; <BR><BR> GROUP# STATUS MEMBER <BR>---------- -------------- -------------------------------------------------- <BR> 1 /home/oracle/data01/oradata/IBM/redo03.log <BR> 2 /home/oracle/data01/oradata/IBM/redo02.log <BR> 3 /home/oracle/data01/oradata/IBM/redo01.log <BR> 4 /home/oracle/data01/oradata/IBM/redo04.log <BR> 1 INVALID /home/oracle/data01/oradata/IBM/redo01b.log <BR> 2 INVALID /home/oracle/data01/oradata/IBM/redo02b.log <BR> 3 INVALID /home/oracle/data01/oradata/IBM/redo03b.log <BR> 4 /home/oracle/data01/oradata/IBM/redo04b.log ==> valid하게 바뀜 <BR> <BR>================================================================================================ <BR><BR><BR>Log Miner <BR><BR>================================================================================================ <BR><BR>* Parameter File 의 utl_file_dir 편집 <BR><BR>a. 확인 <BR><BR>SQL> select name,value from v$parameter <BR> 2 where name='utl_file_dir'; <BR> <BR>NAME VALUE <BR>-------------------- ------------------------------ <BR>utl_file_dir <BR><BR>SQL> !mkdir $ORACLE_HOME/LOG <BR><BR>b. LogMiner사용을 위해 init<SID>.ora file 편집 <BR>utl_file_dir=/oracle/app/oracle/product/8.1.7/LOG <BR><BR>c. restart <BR><BR>SQL> shutdown immediate <BR>SQL> startup <BR><BR>확인 <BR>SQL> select name,value from v$parameter <BR> 2 where name='utl_file_dir'; <BR> <BR>NAME VALUE <BR>-------------------- ------------------------------ <BR>utl_file_dir /oracle/app/oracle/product/8.1.7/LOG ==> LogMiner 준비를 위한 parameter set <BR><BR>d. LogMiner setting - 반드시 트랜잭션의 첫번째 명령이어야 함 <BR><BR>SQL> commit; <BR>SQL> exec dbms_logmnr_d.build('v817dict.ora','/oracle/app/oracle/product/8.1.7/LOG'); <BR>BEGIN dbms_logmnr_d.build('v817dict.ora','/oracle/app/oracle/product/8.1.7/LOG'); END; <BR><BR>* <BR>ERROR at line 1: <BR>ORA-06532: Subscript outside of limit <BR>ORA-06512: at "SYS.DBMS_LOGMNR_D", line 793 <BR>ORA-06512: at line 1 <BR><BR>SQL> !ls $ORACLE_HOME/LOG <BR><BR>SQL> exec dbms_logmnr.add_logfile('/home/oracle/data01/oradata/IBM/redo01.log',DBMS_LOGMNR.NEW); <BR>SQL> exec dbms_logmnr.add_logfile('/home/oracle/data01/oradata/IBM/redo02.log',DBMS_LOGMNR.ADDFILE); <BR>SQL> exec dbms_logmnr.add_logfile('/home/oracle/data01/oradata/IBM/redo03.log',DBMS_LOGMNR.ADDFILE); <BR>SQL> exec dbms_logmnr.add_logfile('/home/oracle/data01/oradata/IBM/redo04.log',DBMS_LOGMNR.ADDFILE); <BR>SQL> exec dbms_logmnr.start_logmnr('/oracle/app/oracle/product/8.1.7/LOG/v817dict.ora'); <BR><BR><BR>e. 트랜잭션 수행 <BR><BR>SQL> descc scott.dept <BR>SQL> select * from scott.dept; <BR>SQL> insert into scott.dept values(99,'test','test'); <BR>SQL> update scott.dept set loc='TEST' where deptno=99; <BR>SQL> commit; <BR><BR>f. log miner 정보 분석 <BR>SQL> select timestamp,username,sql_redo from v$logmnr_contents <BR> 2 where seg_name='DEPT'; <BR><BR>g. 로그마이닝 종료 <BR><BR>SQL> exec dbms_logmnr.end_logmnr; <BR><BR>================================================================================================ <BR><BR><BR>8. Managing TableSpace and Data Files <BR><BR>================================================================================================ <BR><BR>* tablespace와 datafile 조회 <BR><BR>SQL> col tablespace_name format a15 <BR>SQL> col file_name format a45 <BR>SQL> select tablespace_name,status,contents from dba_tablespaces; <BR><BR>TABLESPACE_NAME STATUS CONTENTS <BR>--------------- ------------------ ------------------ <BR>SYSTEM ONLINE PERMANENT <BR>TOOLS ONLINE PERMANENT <BR>RBS ONLINE PERMANENT <BR>TEMP ONLINE TEMPORARY <BR>USERS ONLINE PERMANENT <BR>INDX ONLINE PERMANENT <BR>DRSYS ONLINE PERMANENT <BR><BR>SQL> select tablespace_name,bytes,file_name from dba_data_files; <BR><BR>TABLESPACE_NAME BYTES FILE_NAME <BR>--------------- ---------- --------------------------------------------- <BR>TOOLS 10485760 /home/oracle/data01/oradata/IBM/tools01.dbf <BR>DRSYS 20971520 /home/oracle/data01/oradata/IBM/drsys01.dbf <BR>USERS 20971520 /home/oracle/data01/oradata/IBM/users01.dbf <BR>INDX 20971520 /home/oracle/data01/oradata/IBM/indx01.dbf <BR>RBS 52428800 /home/oracle/data01/oradata/IBM/rbs01.dbf <BR>TEMP 20971520 /home/oracle/data01/oradata/IBM/temp01.dbf <BR>SYSTEM 283115520 /home/oracle/data01/oradata/IBM/system01.dbf <BR><BR>================================================================================================ <BR><BR>* tablespace 생성 및 사이즈 변경 <BR><BR>SQL> create tablespace data05 <BR> 2 datafile '/home/oracle/data01/oradata/IBM/data05_01.dbf' size 1m; <BR><BR>Tablespace created. <BR><BR>1m 짜리 datafile 하나를 가진 tablespace data05를 추가하였다. 확인. <BR><BR>SQL> select tablespace_name,bytes,file_name from dba_data_files <BR> 2 where tablespace_name='DATA05'; <BR><BR>TABLESPACE_NAME BYTES FILE_NAME <BR>--------------- ---------- --------------------------------------------- <BR>DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf <BR><BR>tablespace가 부족할때 늘리는 방법은 두가지가 있다. <BR>하나는 datafile을 추가하는 방법이고 다른하나는 datafile의 size를 늘리는 방법이다. <BR><BR>a. datafile을 하나 추가해보자. <BR><BR>SQL> alter tablespace data05 <BR> 2 add datafile '/home/oracle/data01/oradata/IBM/data05_02.dbf' size 1m; <BR> <BR>SQL> select tablespace_name,bytes,file_name from dba_data_files <BR> 2 where tablespace_name='DATA05'; <BR><BR>TABLESPACE_NAME BYTES FILE_NAME <BR>--------------- ---------- --------------------------------------------- <BR>DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf <BR>DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_02.dbf <BR><BR>제대로 추가되었다. <BR><BR><BR>b. 그렇다면 하나의 사이즈를 변경해보자. <BR><BR>SQL> alter database datafile <BR> 2 '/home/oracle/data01/oradata/IBM/data05_02.dbf' resize 2m; <BR> <BR>SQL> select tablespace_name,bytes,file_name from dba_data_files <BR> 2 where tablespace_name='DATA05'; <BR><BR>TABLESPACE_NAME BYTES FILE_NAME <BR>--------------- ---------- --------------------------------------------- <BR>DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf <BR>DATA05 2097152 /home/oracle/data01/oradata/IBM/data05_02.dbf <BR><BR>2m로 제대로 변경이 되었다. <BR><BR>다시 원상복구 <BR>SQL> alter database datafile <BR> 2 '/home/oracle/data01/oradata/IBM/data05_02.dbf' resize 1m; <BR><BR>전체를 다시 확인해보자 <BR><BR>SQL> select tablespace_name,bytes,file_name from dba_data_files; <BR><BR>TABLESPACE_NAME BYTES FILE_NAME <BR>--------------- ---------- --------------------------------------------- <BR>TOOLS 10485760 /home/oracle/data01/oradata/IBM/tools01.dbf <BR>DRSYS 20971520 /home/oracle/data01/oradata/IBM/drsys01.dbf <BR>USERS 20971520 /home/oracle/data01/oradata/IBM/users01.dbf <BR>INDX 20971520 /home/oracle/data01/oradata/IBM/indx01.dbf <BR>RBS 52428800 /home/oracle/data01/oradata/IBM/rbs01.dbf <BR>TEMP 20971520 /home/oracle/data01/oradata/IBM/temp01.dbf <BR>SYSTEM 283115520 /home/oracle/data01/oradata/IBM/system01.dbf <BR>DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf <BR>DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_02.dbf <BR><BR>================================================================================================ <BR><BR>* tablespace 삭제 : Dictionary에서만 삭제되는것으로 실제 물리적으로 파일은 os command로 삭제해야한다. <BR><BR><BR>SQL> select tablespace_name from dba_tablespaces <BR> 2 where tablespace_name like 'DATA%' <BR> 3 minus <BR> 4 select distinct tablespace_name from dba_segments; <BR><BR>TABLESPACE_NAME <BR>--------------- <BR>DATA05 <BR><BR>SQL> drop tablespace data05; <BR>SQL> select tablespace_name,bytes,file_name from dba_data_files; <BR><BR>TABLESPACE_NAME BYTES FILE_NAME <BR>--------------- ---------- --------------------------------------------- <BR>TOOLS 10485760 /home/oracle/data01/oradata/IBM/tools01.dbf <BR>DRSYS 20971520 /home/oracle/data01/oradata/IBM/drsys01.dbf <BR>USERS 20971520 /home/oracle/data01/oradata/IBM/users01.dbf <BR>INDX 20971520 /home/oracle/data01/oradata/IBM/indx01.dbf <BR>RBS 52428800 /home/oracle/data01/oradata/IBM/rbs01.dbf <BR>TEMP 20971520 /home/oracle/data01/oradata/IBM/temp01.dbf <BR>SYSTEM 283115520 /home/oracle/data01/oradata/IBM/system01.dbf <BR><BR>SQL> !ls //home/oracle/data01/oradata/IBM/*.dbf <BR>//home/oracle/data01/oradata/IBM/data05_01.dbf //home/oracle/data01/oradata/IBM/system01.dbf <BR>//home/oracle/data01/oradata/IBM/data05_02.dbf //home/oracle/data01/oradata/IBM/temp01.dbf <BR>//home/oracle/data01/oradata/IBM/drsys01.dbf //home/oracle/data01/oradata/IBM/tools01.dbf <BR>//home/oracle/data01/oradata/IBM/indx01.dbf //home/oracle/data01/oradata/IBM/users01.dbf <BR>//home/oracle/data01/oradata/IBM/rbs01.dbf <BR><BR>dictionary에서는 삭제되었으나 여전히 물리적인 file은 존재한다. 삭제하면 된다. <BR>(tablespace생성시에는 file이 그냥 생성되나 삭제시는 dictionary삭제후 강제로 삭제해줘야 한다.) <BR><BR>SQL> !rm /home/oracle/data01/oradata/IBM/data05* <BR><BR>================================================================================================ <BR><BR>* tablespace 의 online/offline, read only/read write <BR><BR>SQL> select tablespace_name, status from dba_tablespaces; <BR><BR>TABLESPACE_NAME STATUS <BR>------------------------------------------------------------ ------------------ <BR>SYSTEM ONLINE <BR>TOOLS ONLINE <BR>RBS ONLINE <BR>TEMP ONLINE <BR>USERS ONLINE <BR>INDX ONLINE <BR>DRSYS ONLINE <BR><BR>7 rows selected. <BR><BR>SQL> select tablespace_name from dba_tables <BR> 2 where table_name ='DEPT' and owner='SCOTT'; <BR><BR>TABLESPACE_NAME <BR>------------------------------------------------------------ <BR>SYSTEM <BR><BR>default로 생성시 scott user의 data가 system tablespace에 생성되었으나 이렇게 쓰면 안된다. <BR>하나 생성해볼까? <BR><BR>SQL> create tablespace data01 <BR> 2 datafile '/home/oracle/data01/oradata/IBM/data01.dbf' size 1m; <BR><BR>Tablespace created. <BR><BR>SQL> connect scott/tiger <BR>Connected. <BR><BR>SQL> create table dept_tmp tablespace data01 <BR> 2 as select * from dept; <BR> <BR>SQL> connect internal <BR>Connected. <BR>SQL> select tablespace_name from dba_tables <BR> 2 where table_name ='DEPT_TMP' and owner='SCOTT'; <BR><BR>TABLESPACE_NAME <BR>------------------------------------------------------------ <BR>DATA01 <BR><BR>SQL> select * from scott.dept_tmp; <BR><BR> DEPTNO DNAME LOC <BR>---------- ---------------------------- -------------------------- <BR> 10 ACCOUNTING NEW YORK <BR> 20 RESEARCH DALLAS <BR> 30 SALES CHICAGO <BR> 40 OPERATIONS BOSTON <BR> <BR>제대로 된다. 그렇다면 tablespace를 offline으로... <BR><BR>SQL> alter tablespace data01 offline; <BR>SQL> select tablespace_name, status from dba_tablespaces <BR> 2 where tablespace_name='DATA01'; <BR><BR>TABLESPACE_NAME STATUS <BR>------------------------------------------------------------ ------------------ <BR>DATA01 OFFLINE <BR><BR>SQL> select * from scott.dept_tmp; <BR>select * from scott.dept_tmp <BR> * <BR>ERROR at line 1: <BR>ORA-00376: file 8 cannot be read at this time <BR>ORA-01110: data file 8: '/home/oracle/data01/oradata/IBM/data01.dbf' <BR><BR>위와 같이 error가 발생한다. <BR>다시 online으로 해두자. <BR>SQL> alter tablespace data01 online; <BR><BR>이번엔 read only로 변경 <BR>SQL> alter tablespace data01 read only; <BR><BR>SQL> select tablespace_name, status from dba_tablespaces <BR> 2 where tablespace_name='DATA01'; <BR><BR>TABLESPACE_NAME STATUS <BR>------------------------------------------------------------ ------------------ <BR>DATA01 READ ONLY <BR><BR>변경되었다. <BR><BR>SQL> insert into scott.dept_tmp values(80,'new_dept','new_loc'); <BR>insert into scott.dept_tmp values(80,'new_dept','new_loc') <BR> * <BR>ERROR at line 1: <BR>ORA-00372: file 8 cannot be modified at this time <BR>ORA-01110: data file 8: '/home/oracle/data01/oradata/IBM/data01.dbf' <BR><BR>insert같은 DML(write성) 수행시 위와 같은 error 발생 <BR><BR>원상복구 <BR>SQL> alter tablespace data01 read write; <BR>SQL> insert into scott.dept_tmp values(80,'test','test'); <BR><BR>제대로 된다. <BR>================================================================================================ <BR><BR><BR>9. Storage Structure and Relationships <BR><BR>================================================================================================ <BR><BR>* Extent 정보 조회 : 다음과 같이 각종 extent,segment 등의 정보를 조회해 볼 수 있다. <BR><BR>SQL> col owner format a10 <BR>SQL> col segment_type format a12 <BR>SQL> col segment_name format a12 <BR>SQL> col tablespace_name format a10 <BR><BR>SQL> select owner,segment_name,segment_type, tablespace_name,max_extents,extents,pct_increase <BR> 2 from dba_segments <BR> 3 where max_extents - extents <= 10 and owner !='SYS'; <BR><BR>no rows selected <BR><BR>SQL> select owner,segment_name,segment_type, tablespace_name,max_extents,extents,pct_increase <BR> 2 from dba_segments <BR> 3 where owner='SCOTT'; <BR><BR>OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE MAX_EXTENTS EXTENTS PCT_INCREASE <BR>---------- ------------ ------------ ---------- ----------- ---------- ------------ <BR>SCOTT DEPT_TMP TABLE DATA01 505 1 50 <BR>SCOTT DEPT TABLE SYSTEM 2147483645 1 50 <BR>SCOTT EMP TABLE SYSTEM 2147483645 1 50 <BR>SCOTT BONUS TABLE SYSTEM 2147483645 1 50 <BR>SCOTT SALGRADE TABLE SYSTEM 2147483645 1 50 <BR>SCOTT PK_DEPT INDEX SYSTEM 2147483645 1 50 <BR>SCOTT PK_EMP INDEX SYSTEM 2147483645 1 50 <BR><BR><BR>SQL> select segment_name,extents, initial_extent, next_extent,pct_increase <BR> 2 from dba_segments <BR> 3 where owner='SCOTT' and segment_name='EMP'; <BR><BR>SEGMENT_NAME EXTENTS INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE <BR>------------ ---------- -------------- ----------- ------------ <BR>EMP 1 65536 65536 50 <BR><BR><BR>SQL> select segment_name,extent_id,block_id,bytes,blocks <BR> 2 from dba_extents <BR> 3 where owner='SCOTT' and segment_name='EMP'; <BR> 4 order by 2,3; <BR> <BR>SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES BLOCKS <BR>------------ ---------- ---------- ---------- ---------- <BR>EMP 0 33945 65536 8 <BR><BR>================================================================================================ <BR><BR>* Free space 관리 <BR><BR>tablespace내에 free space를 먼저 확인해본다. <BR>SQL> select * from dba_free_space <BR> 2 where tablespace_name ='DATA01' order by 1,2,3; <BR><BR>TABLESPACE FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO <BR>---------- ---------- ---------- ---------- ---------- ------------ <BR>DATA01 8 7 999424 122 8 <BR><BR>테이블을 여러개 생성해보자. <BR>SQL> create table scott.dept2 tablespace data01 as select * from scott.dept; <BR>SQL> create table scott.dept3 tablespace data01 as select * from scott.dept; <BR>SQL> create table scott.dept4 tablespace data01 as select * from scott.dept; <BR>SQL> create table scott.dept5 tablespace data01 as select * from scott.dept; <BR>SQL> create table scott.dept6 tablespace data01 as select * from scott.dept; <BR><BR>SQL> select * from dba_free_space <BR> 2 where tablespace_name ='DATA01' order by 1,2,3; <BR><BR>TABLESPACE FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO <BR>---------- ---------- ---------- ---------- ---------- ------------ <BR>DATA01 8 32 794624 97 8 <BR><BR>사용함에 따라 tablespace내 free space 가 줄어듦을 알 수 있다. <BR><BR>SQL> drop table scott.dept2; <BR>drop table dept2 <BR> * <BR>ERROR at line 1: <BR>ORA-04098: trigger 'SYS.JIS$ROLE_TRIGGER$' is invalid and failed re-validation <BR>이건 또 뭐야 ? trigger가 걸려있네요... <BR>table drop 을 위해 <BR>SQL> alter trigger SYS.JIS$ROLE_TRIGGER$ disable; <BR>drop table scott.dept3; ==> dept4 만 빼고 전부 drop <BR>drop table scott.dept5; <BR>drop table scott.dept6; <BR><BR>SQL> select * from dba_free_space <BR> 2 where tablespace_name ='DATA01' order by 1,2,3; <BR><BR>TABLESPACE FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO <BR>---------- ---------- ---------- ---------- ---------- ------------ <BR>DATA01 8 7 40960 5 8 <BR>DATA01 8 32 794624 97 8 <BR><BR>tablespace의 free space가 늘긴 했는데 쪼개졌네요.. <BR>빈공간을 병합하자 <BR>SQL> alter tablespace data01 coalesce; <BR><BR>SQL> select * from dba_free_space <BR> 2 where tablespace_name ='DATA01' order by 1,2,3; <BR><BR>TABLESPACE FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO <BR>---------- ---------- ---------- ---------- ---------- ------------ <BR>DATA01 8 7 40960 5 8 <BR>DATA01 8 32 794624 97 8 <BR><BR>그래도 두개로 쪼개져 있는 이유는? 중간에 dept4 가 사용하는 space가 coalesce 되지 않았기 때문 <BR><BR>SQL> drop table scott.dept4; <BR>SQL> alter tablespace data01 coalesce; <BR><BR>완전히 병합되었다. <BR><BR>================================================================================================ <BR><BR><BR><BR>10. Managing Rollback Segments <BR><BR>================================================================================================ <BR><BR>* rollback segment의 정보 조회 <BR><BR>SQL> col owner format a10 <BR>SQL> col segment_name format a12 <BR>SQL> col segment_type format a12 <BR>SQL> col tablespace_name format a10 <BR>SQL> col status format a7 <BR><BR>SQL> select segment_name,tablespace_name,status,initial_extent,next_extent,min_extents <BR> 2 from dba_rollback_segs; <BR><BR>SEGMENT_NAME TABLESPACE STATUS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS <BR>------------ ---------- ------- -------------- ----------- ----------- <BR>SYSTEM SYSTEM ONLINE 57344 57344 2 <BR>RBS0 RBS ONLINE 524288 524288 8 <BR>RBS1 RBS ONLINE 524288 524288 8 <BR>RBS2 RBS ONLINE 524288 524288 8 <BR>RBS3 RBS ONLINE 524288 524288 8 <BR>RBS4 RBS ONLINE 524288 524288 8 <BR>RBS5 RBS ONLINE 524288 524288 8 <BR>RBS6 RBS ONLINE 524288 524288 8 <BR><BR>================================================================================================ <BR><BR>* rollback segment 생성 <BR><BR>SQL> create rollback segment rbs99 <BR> 2 tablespace rbs <BR> 3 storage(initial 20k next 20k minextents 2 optimal 80k); <BR><BR>Rollback segment created. <BR><BR>SQL> select segment_name,tablespace_name,status,initial_extent,next_extent,min_extents <BR> 2 from dba_rollback_segs; <BR><BR>SEGMENT_NAME TABLESPACE STATUS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS <BR>------------ ---------- ------- -------------- ----------- ----------- <BR>SYSTEM SYSTEM ONLINE 57344 57344 2 <BR>RBS0 RBS ONLINE 524288 524288 8 <BR>RBS1 RBS ONLINE 524288 524288 8 <BR>RBS2 RBS ONLINE 524288 524288 8 <BR>RBS3 RBS ONLINE 524288 524288 8 <BR>RBS4 RBS ONLINE 524288 524288 8 <BR>RBS5 RBS ONLINE 524288 524288 8 <BR>RBS6 RBS ONLINE 524288 524288 8 <BR>RBS99 RBS OFFLINE 24576 32768 2 <BR><BR>추가되었다. online으로 전환하자. <BR><BR>SQL> alter rollback segment rbs99 online; <BR><BR><SCOTT session><BR>SQL> create table emp2 as select * from emp; <BR><BR><DBA session><BR>SQL> select name,extents,xacts,shrinks,optsize <BR> 2 from v$rollname n, v$rollstat s <BR> 3 where n.usn = s.usn; <BR><BR>NAME EXTENTS XACTS SHRINKS OPTSIZE <BR>--------------------- ----------- ---------- ---------- ---------- <BR>SYSTEM 9 0 0 <BR>RBS0 8 0 0 4194304 <BR>RBS1 8 0 0 4194304 <BR>RBS2 8 0 0 4194304 <BR>RBS3 8 0 0 4194304 <BR>RBS4 8 0 0 4194304 <BR>RBS5 8 0 0 4194304 <BR>RBS6 8 0 0 4194304 <BR>RBS99 2 0 0 81920 ==> extents,xacts의 변화 관찰 <BR><BR><SCOTT session><BR>SQL> set transaction use rollback segment rbs99; <BR>SQL> update emp2 set hiredate=sysdate; <BR><BR><DBA session><BR>SQL> select name,extents,xacts,shrinks,optsize <BR> 2 from v$rollname n, v$rollstat s <BR> 3 where n.usn = s.usn; <BR><BR>NAME EXTENTS XACTS SHRINKS OPTSIZE <BR>--------------- ---------- ---------- ---------- ---------- <BR>SYSTEM 9 0 0 <BR>RBS0 8 0 0 4194304 <BR>RBS1 8 0 0 4194304 <BR>RBS2 8 0 0 4194304 <BR>RBS3 8 0 0 4194304 <BR>RBS4 8 0 0 4194304 <BR>RBS5 8 0 0 4194304 <BR>RBS6 8 0 0 4194304 <BR>RBS99 2 1 0 81920 ==> transaction이 시작됨 <BR><BR><SCOTT session><BR>SQL> update emp2 set hiredate=sysdate-1; <BR>sql> insert into emp2 select * from emp2; ==> 엄청 많이 수행 하자. <BR><BR><DBA session><BR>SQL> select name,extents,xacts,shrinks,optsize <BR> 2 from v$rollname n, v$rollstat s <BR> 3 where n.usn = s.usn; <BR><BR>NAME EXTENTS XACTS SHRINKS OPTSIZE <BR>--------------- ---------- ---------- ---------- ---------- <BR>SYSTEM 9 0 0 <BR>RBS0 8 0 0 4194304 <BR>RBS1 8 0 0 4194304 <BR>RBS2 8 0 0 4194304 <BR>RBS3 8 0 0 4194304 <BR>RBS4 8 0 0 4194304 <BR>RBS5 8 0 0 4194304 <BR>RBS6 8 0 0 4194304 <BR>RBS99 3 1 0 81920 ==> extents 증가 <BR><BR><SCOTT session><BR>SQL> rollback; <BR>SQL> set transaction use rollback segment rbs99; <BR>SQL> update emp2 set sal=1000; <BR><BR><DBA session><BR>SQL> select name,extents,xacts,shrinks,optsize <BR> 2 from v$rollname n, v$rollstat s <BR> 3 where n.usn = s.usn; <BR><BR>NAME EXTENTS XACTS SHRINKS OPTSIZE <BR>--------------- ---------- ---------- ---------- ---------- <BR>SYSTEM 9 0 0 <BR>RBS0 8 0 0 4194304 <BR>RBS1 8 0 0 4194304 <BR>RBS2 8 0 0 4194304 <BR>RBS3 8 0 0 4194304 <BR>RBS4 8 0 0 4194304 <BR>RBS5 8 0 0 4194304 <BR>RBS6 8 0 0 4194304 <BR>RBS99 3 1 0 81920 ==> automatic 하게 shrink되었는지 확인 <BR><BR>이전 tranx은 종료되었고 새로운 tranx가 시작됨 <BR><BR>================================================================================================ <BR><BR>* rollback segment 삭제 <BR><BR><SCOTT session><BR><BR>SQL> rollback; <BR><BR><DBA session><BR>SQL> select name,extents,xacts,shrinks,optsize <BR> 2 from v$rollname n, v$rollstat s <BR> 3 where n.usn = s.usn; ==> xacts 가 '0' 인지 먼저 확인 <BR> <BR>SQL> alter rollback segment rbs99 offline; <BR>SQL> drop rollback segment rbs99; <BR>SQL> select segment_name,tablespace_name,status,initial_extent,next_extent,min_extents <BR> 2 from dba_rollback_segs; <BR><BR>SEGMENT_NAME TABLESPACE STATUS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS <BR>------------ ---------- ------- -------------- ----------- ----------- <BR>SYSTEM SYSTEM ONLINE 57344 57344 2 <BR>RBS0 RBS ONLINE 524288 524288 8 <BR>RBS1 RBS ONLINE 524288 524288 8 <BR>RBS2 RBS ONLINE 524288 524288 8 <BR>RBS3 RBS ONLINE 524288 524288 8 <BR>RBS4 RBS ONLINE 524288 524288 8 <BR>RBS5 RBS ONLINE 524288 524288 8 <BR>RBS6 RBS ONLINE 524288 524288 8 <BR> <BR>확인해보니 삭제되었다. <BR><BR>================================================================================================ <BR><BR><BR>11.Managing Tables <BR><BR>================================================================================================ <BR><BR>* Temporary Table <BR><BR>a. on commit perserve rows : session내에서 생성한 temp table에 대해서 지속적. <BR> 새로운 session 연결 되면 data 지워짐 <BR><BR>Example : Creating a Session-Specific Temporary Table <BR>CREATE GLOBAL TEMPORARY TABLE ... <BR> [ON COMMIT PRESERVE ROWS ] <BR><BR>b. on commit delete rows : tansaction이 끝나면 temp table 내의 data가 지워짐(commit,rollback등) <BR><BR>Example : Creating a Transaction-Specific Temporary Table <BR>CREATE GLOBAL TEMPORARY TABLE ... <BR> [ON COMMIT DELETE ROWS ] <BR><BR>================================================================================================ <BR><BR>* Using Temporary Tables to Improve Performance <BR><BR>You can use temporary tables to improve performance when you run complex queries. <BR>Running multiple such queries is relatively slow because the tables are accessed multiple times <BR>for each returned row. It is faster to cache the values from a complex query in a temporary table, <BR>then run the queries against the temporary table. <BR><BR>For example, even with a view like this defined to simplify further queries, <BR>the queries against the view may be slow because the contents of the view are recalculated each time: <BR><BR>CREATE OR REPLACE VIEW Profile_values_view AS <BR>SELECT d.Profile_option_name, d.Profile_option_id, Profile_option_value, <BR> u.User_name, Level_id, Level_code <BR> FROM Profile_definitions d, Profile_values v, Profile_users u <BR>WHERE d.Profile_option_id = v.Profile_option_id <BR> AND ((Level_code = 'USER' AND Level_id = U.User_id) OR <BR> (Level_code = 'DEPARTMENT' AND Level_id = U.Department_id) OR <BR> (Level_code = 'SITE')) <BR> AND NOT EXISTS (SELECT 1 FROM PROFILE_VALUES P <BR> WHERE P.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID <BR> AND ((Level_code = 'USER' AND <BR> level_id = u.User_id) OR <BR> (Level_code = 'DEPARTMENT' AND <BR> level_id = u.Department_id) OR <BR> (Level_code = 'SITE')) <BR> AND INSTR('USERDEPARTMENTSITE', v.Level_code) > <BR> INSTR('USERDEPARTMENTSITE', p.Level_code)); <BR><BR><BR>A temporary table allows us to run the computation once, <BR>and cache the result in later SQL queries and joins: <BR><BR>CREATE GLOBAL TEMPORARY TABLE Profile_values_temp <BR> ( <BR> Profile_option_name VARCHAR(60) NOT NULL, <BR> Profile_option_id NUMBER(4) NOT NULL, <BR> Profile_option_value VARCHAR2(20) NOT NULL, <BR> Level_code VARCHAR2(10) , <BR> Level_id NUMBER(4) , <BR> CONSTRAINT Profile_values_temp_pk <BR> PRIMARY KEY (Profile_option_id) <BR> ) ON COMMIT PRESERVE ROWS ORGANIZATION INDEX; <BR><BR>INSERT INTO Profile_values_temp <BR> (Profile_option_name, Profile_option_id, Profile_option_value, <BR> Level_code, Level_id) <BR>SELECT Profile_option_name, Profile_option_id, Profile_option_value, <BR> Level_code, Level_id <BR> FROM Profile_values_view; <BR>COMMIT; <BR><BR><BR>Now the temporary table can be used to speed up queries, <BR>and the results cached in the temporary table are freed automatically by the database <BR>when the session ends. <BR><BR>================================================================================================ <BR><BR>* Row Migration Test <BR><BR>scott/tiger로 접속해서 <BR><BR>SQL> create table chain_test(col1 varchar2(100)); <BR><BR>Table created. <BR><BR>SQL> insert into chain_test values('a'); <BR><BR>1 row created. <BR><BR>SQL> insert into chain_test select * from chain_test; <====== 1 row created. <BR>SQL> / <====== 2 rows created. <BR>SQL> / <====== 4 rows created. <BR>SQL> / <====== 8 rows created. <BR>SQL> / <====== 16 rows created. <BR>SQL> / <====== 32 rows created. <BR>SQL> / <====== 64 rows created. <BR>SQL> / <====== 128 rows created. <BR>SQL> / <====== 256 rows created. <BR>SQL> / <====== 512 rows created. <BR>SQL> commit; <BR><BR>SQL> @$ORACLE_HOME/rdbms/admin/utlchain <====== chanined_rows table생성 <BR><BR>Table created. <BR><BR>SQL> desc chained_rows <BR>Name Null? Type <BR>----------------------------------------- -------- ---------------------------- <BR>OWNER_NAME VARCHAR2(30) <BR>TABLE_NAME VARCHAR2(30) <BR>CLUSTER_NAME VARCHAR2(30) <BR>PARTITION_NAME VARCHAR2(30) <BR>SUBPARTITION_NAME VARCHAR2(30) <BR>HEAD_ROWID ROWID <BR>ANALYZE_TIMESTAMP DATE <BR><BR>SQL> analyze table chain_test list chained rows; <BR><BR>Table analyzed. <BR><BR>SQL> select count(*) from chained_rows; <BR><BR> COUNT(*) <BR>---------- <BR> 0 ======> 아직까지는 chaining이 하나도 없지... <BR> <BR>SQL> update chain_test <BR> 2 set col1 = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'; <BR><BR>1024 rows updated. <BR><BR>SQL> analyze table chain_test list chained rows; <BR><BR>Table analyzed. <BR><BR>SQL> select count(*) from chained_rows; <BR><BR> COUNT(*) <BR>---------- <BR> 995 ==============> row migration이 다량 발생 <BR><BR><BR>row migration이 일어나면 여러 block에서 읽어야 하므로 그만큼 performance가 떨어진다. <BR>이를 해결하기 위해 주기적으로 analyze하여 확인해보고 insert를 다시 해주면 된다. <BR>chaining이 일어난 row들의 rowid로 찾아서 임시table을 생성하고 원래 table에서 chaining이 일어난 <BR>data를 삭제하고 다시 insert하면 된다. <BR><BR><BR>SQL> create table chain_tmp as select * from chain_test <BR> 2 where rowid in (select head_rowid from chained_rows where table_name='CHAIN_TEST'); <BR><BR>SQL> delete from chain_test <BR> 2 where rowid in(select head_rowid from chained_rows where table_name='CHAIN_TEST'); <BR><BR>SQL> insert into chain_test select * from chain_tmp; <BR><BR>SQL> commit; <BR><BR>다시 cahined_rows table 을 삭제하고 analyze해보자. <BR>SQL> truncate table chained_rows; <BR><BR>Table truncated. <BR><BR>SQL> analyze table chain_test list chained rows; <BR><BR>Table analyzed. <BR><BR>SQL> select * from chained_rows; <====== no rows selected <BR><BR>chaining이 없어졌다. <BR><BR>================================================================================================ <BR><BR><BR>12. Managing Indexes <BR><BR>================================================================================================ <BR><BR>1. B*Tree Index 생성, 확인 <BR><BR>SQL> col table_name format a10 <BR>SQL> col index_name format a20 <BR>SQL> col index_type format a10 <BR>SQL> col column_name format a12 <BR>SQL> create index scott.dept_dname_ind on scott.dept(dname); <BR><BR>Index created. <BR><BR>SQL> create unique index scott.dept_deptno_uind on scott.dept(deptno); <BR>create unique index scott.dept_deptno_uind on scott.dept(deptno) <BR> * <BR>ERROR at line 1: <BR>ORA-01408: such column list already indexed <BR><BR>error가 난 이유는 column이 pk로 지정될때는 unique index가 자동으로 생성되기 때문. <BR><BR>user_constraints, user_cons_columns 등에서 확인해보면 알수있다. <BR><BR>SQL> select table_name,index_name,index_type,uniqueness <BR> 2 from dba_indexes <BR> 3 where owner='SCOTT'; <BR><BR>TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS <BR>---------- -------------------- ---------- ------------------ <BR>DEPT DEPT_DNAME_IND NORMAL NONUNIQUE ====> 생성한 index <BR>AUDIT_ACTI I_AUDIT_ACTIONS NORMAL UNIQUE <BR>ONS <BR><BR>DEPT PK_DEPT NORMAL UNIQUE <BR>EMP PK_EMP NORMAL UNIQUE <BR>DBMS_LOCK_ SYS_C001456 NORMAL UNIQUE <BR>ALLOCATED <BR><BR>DBMS_ALERT SYS_C001457 NORMAL UNIQUE <BR>_INFO <BR><BR><BR>SQL> select table_name,index_name,column_position,column_name <BR> 2 from dba_ind_columns <BR> 3 where index_owner='SCOTT'; <BR><BR>TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME <BR>---------- -------------------- --------------- ------------ <BR>DEPT DEPT_DNAME_IND 1 DNAME ====> index가 걸린 column <BR>AUDIT_ACTI I_AUDIT_ACTIONS 1 ACTION <BR>ONS <BR><BR>AUDIT_ACTI I_AUDIT_ACTIONS 2 NAME <BR>ONS <BR><BR>DEPT PK_DEPT 1 DEPTNO <BR>EMP PK_EMP 1 EMPNO <BR>DBMS_LOCK_ SYS_C001456 1 NAME <BR>ALLOCATED <BR><BR>DBMS_ALERT SYS_C001457 1 NAME <BR>_INFO <BR><BR>DBMS_ALERT SYS_C001457 2 SID <BR>_INFO <BR><BR>================================================================================================ <BR><BR>* Bitmap Index 생성, 확인 <BR><BR>SQL> select count(*) from scott.emp; <BR>SQL> select distinct job from scott.emp; <BR><BR>JOB <BR>------------------ <BR>ANALYST <BR>CLERK <BR>MANAGER <BR>PRESIDENT <BR>SALESMAN <BR><BR>SQL> create bitmap index scott.emp_job_bind on scott.emp(job); <BR><BR>SQL> select table_name,index_name,index_type,uniqueness <BR> 2 from dba_indexes <BR> 3 where owner='SCOTT'; <BR><BR>TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS <BR>---------- -------------------- ---------- ------------------ <BR>DEPT DEPT_DNAME_IND NORMAL NONUNIQUE <BR>EMP EMP_JOB_BIND BITMAP NONUNIQUE =====> <BR>AUDIT_ACTI I_AUDIT_ACTIONS NORMAL UNIQUE <BR>... <BR><BR>SQL> select table_name,index_name,column_position,column_name <BR> 2 from dba_ind_columns <BR> 3 where index_owner='SCOTT'; <BR><BR>TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME <BR>---------- -------------------- --------------- ------------ <BR>DEPT DEPT_DNAME_IND 1 DNAME <BR>EMP EMP_JOB_BIND 1 JOB =====> <BR>AUDIT_ACTI I_AUDIT_ACTIONS 1 ACTION <BR>ONS <BR><BR>================================================================================================ <BR><BR>* Reverse Key Index 생성, 확인 <BR><BR>SQL> create index scott.emp_hiredate_rind on scott.emp(hiredate) reverse; <BR><BR>SQL> select table_name,index_name,index_type,uniqueness <BR> 2 from dba_indexes <BR> 3 where owner='SCOTT'; <BR><BR>TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS <BR>---------- -------------------- ---------- ------------------ <BR>DEPT DEPT_DNAME_IND NORMAL NONUNIQUE <BR>EMP EMP_HIREDATE_RIND NORMAL/REV NONUNIQUE ==> <BR>EMP EMP_JOB_BIND BITMAP NONUNIQUE <BR>.... <BR><BR>SQL> select table_name,index_name,column_position,column_name <BR> 2 from dba_ind_columns <BR> 3 where index_owner='SCOTT'; <BR><BR>TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME <BR>---------- -------------------- --------------- ------------ <BR>DEPT DEPT_DNAME_IND 1 DNAME <BR>EMP EMP_HIREDATE_RIND 1 HIREDATE ==> <BR>EMP EMP_JOB_BIND 1 JOB <BR>.... <BR><BR>================================================================================================ <BR><BR>* Funtion-Based Index 생성, 확인 <===== Query Rewrite권한 필요 <BR><BR>SQL> create index scott.emp_sal_find on scott.emp(sal * 1.1); <BR><BR><BR>SQL> select table_name,index_name,index_type,uniqueness <BR> 2 from dba_indexes <BR> 3 where owner='SCOTT'; <BR><BR>TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS <BR>---------- -------------------- ---------- ------------------ <BR>DEPT DEPT_DNAME_IND NORMAL NONUNIQUE <BR>EMP EMP_HIREDATE_RIND NORMAL/REV NONUNIQUE <BR>EMP EMP_JOB_BIND BITMAP NONUNIQUE <BR>EMP EMP_SAL_FIND FUNCTION-B NONUNIQUE ==> <BR> ASED NORMA <BR> L <BR>.... <BR><BR>SQL> select table_name,index_name,column_position,column_name <BR> 2 from dba_ind_columns <BR> 3 where index_owner='SCOTT'; <BR><BR>TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME <BR>---------- -------------------- --------------- ------------ <BR>DEPT DEPT_DNAME_IND 1 DNAME <BR>EMP EMP_HIREDATE_RIND 1 HIREDATE <BR>EMP EMP_JOB_BIND 1 JOB <BR>EMP EMP_SAL_FIND 1 SYS_NC00009$ ====> column name이 내부적으로 바뀐다. <BR><BR>.... <BR><BR>================================================================================================ <BR><BR>* Index drop <BR><BR>SQL> drop index scott.dept_dname_ind; <BR>SQL> drop index scott.emp_hiredate_rind; <BR>SQL> drop index scott.emp_job_bind; <BR>SQL> drop index scott.emp_sal_find; <BR><BR>================================================================================================ <BR><BR><BR>13.Maintaining Data Integrity <BR><BR>================================================================================================ <BR><BR>* PK/UK 와 Unique Index <BR><BR>SQL> desc scott.dept <BR>Name Null? Type <BR>----------------------------------------- -------- ---------------------------- <BR>DEPTNO NOT NULL NUMBER(2) <BR>DNAME VARCHAR2(14) <BR>LOC VARCHAR2(13) <BR><BR>PK생성은 다음과 같이 할 수 있다. <BR><BR>SQL> alter table scott.dept <BR> 2 add constraint dept_deptno_pk primary key(deptno); <BR>add constraint dept_deptno_pk primary key(deptno) <BR> * <BR>ERROR at line 2: <BR>ORA-02260: table can have only one primary key <BR><BR>이미 pk가 설정이 되어있어서 한테이블에 두개의 pk를 설정할 수 없다는 error. <BR><BR>SQL> alter table scott.dept <BR> 2 add constraint dept_dname_uk unique (dname); <BR><BR>Table altered. <BR><BR>SQL> select table_name,constraint_name,constraint_type, status <BR> 2 from dba_constraints <BR> 3 where owner='SCOTT'; <BR><BR>TABLE_NAME CONSTRAINT_NAME CO STATUS <BR>-------------------- --------------- -- ---------------- <BR>DEPT DEPT_DNAME_UK U ENABLED <=== 새로 생성한 UK <BR>EMP FK_DEPTNO R ENABLED <BR>DEPT PK_DEPT P ENABLED <BR>EMP PK_EMP P ENABLED <BR>AUDIT_ACTIONS SYS_C001454 C ENABLED <BR>AUDIT_ACTIONS SYS_C001455 C ENABLED <BR>DBMS_LOCK_ALLOCATED SYS_C001456 P ENABLED <BR>DBMS_ALERT_INFO SYS_C001457 P ENABLED <BR><BR><BR>SQL> select table_name,index_name,index_type,uniqueness <BR> 2 from dba_indexes <BR> 3 where owner='SCOTT'; <BR><BR>TABLE_NAME INDEX_NAME INDEX_TYPE UNIQUENESS <BR>---------- -------------------- ---------- ------------------ <BR>DEPT DEPT_DNAME_UK NORMAL UNIQUE <== <BR>AUDIT_ACTI I_AUDIT_ACTIONS NORMAL UNIQUE <BR>ONS <BR><BR>SQL> select table_name,index_name,column_position,column_name <BR> 2 from dba_ind_columns <BR> 3 where index_owner='SCOTT'; <BR><BR>TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME <BR>---------- -------------------- --------------- ------------ <BR>DEPT DEPT_DNAME_UK 1 DNAME <== <BR>AUDIT_ACTI I_AUDIT_ACTIONS 1 ACTION <BR>ONS <BR><BR>================================================================================================ <BR><BR>* Constraint Check <BR><BR>SQL> insert into scott.dept values(50,'HR','SEOUL'); <BR><BR>1 row created. <BR><BR>SQL> commit; <BR><BR>Commit complete. <BR><BR>SQL> insert into scott.dept values(50,'HR Dept','SEOUL'); <BR>insert into scott.dept values(50,'HR Dept','SEOUL') <BR>* <BR>ERROR at line 1: <BR>ORA-00001: unique constraint (SCOTT.PK_DEPT) violated <BR><BR>UK 값에 같은 값 insert하려다가 error가 난다. <BR><BR>================================================================================================ <BR><BR>* Constriaint 비활성화/활성화 <BR><BR><BR>SQL> alter table scott.dept <BR> 2 disable constraint dept_dname_uk; <BR><BR>SQL> alter table scott.dept <BR> 2 enable constraint dept_dname_uk; <BR><BR>================================================================================================ <BR><BR>* Deferred Constraint(?) ==> 자료좀 찾아보자.. <BR><BR>================================================================================================ <BR><BR>* Constraint 삭제 <BR><BR>alter table <테이블명> drop constraint <CONSTRAINT명><BR><BR>================================================================================================ <BR><BR><BR>14. Loading Data <BR><BR>================================================================================================ <BR><BR>* 사용법 <BR><BR>Usage: SQLLOAD keyword=value [,keyword=value,...] <BR><BR>Valid Keywords: <BR><BR> userid -- ORACLE username/password <BR> control -- Control file name <BR> log -- Log file name <BR> bad -- Bad file name <BR> data -- Data file name <BR> discard -- Discard file name <BR>discardmax -- Number of discards to allow (Default all) <BR> skip -- Number of logical records to skip (Default 0) <BR> load -- Number of logical records to load (Default all) <BR> errors -- Number of errors to allow (Default 50) <BR> rows -- Number of rows in conventional path bind array or between direct path data saves <BR> (Default: Conventional path 64, Direct path all) <BR> bindsize -- Size of conventional path bind array in bytes (Default 65536) <BR> silent -- Suppress messages during run (header,feedback,errors,discards,partitions) <BR> direct -- use direct path (Default FALSE) <BR> parfile -- parameter file: name of file that contains parameter specifications <BR> parallel -- do parallel load (Default FALSE) <BR> file -- File to allocate extents from <BR>skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE) <BR>skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE) <BR>commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE) <BR> readsize -- Size of Read buffer (Default 1048576) <BR><BR>PLEASE NOTE: Command-line parameters may be specified either by <BR>position or by keywords. An example of the former case is 'sqlload <BR>scott/tiger foo'; an example of the latter is 'sqlload control=foo <BR>userid=scott/tiger'. One may specify parameters by position before <BR>but not after parameters specified by keywords. For example, <BR>'sqlload scott/tiger control=foo logfile=log' is allowed, but <BR>'sqlload scott/tiger control=foo log' is not, even though the <BR>position of the parameter 'log' is correct. <BR><BR>================================================================================================ <BR>* case 별로 sqlldr을 사용하는법은 <BR><A class=con_link href="http://technet.oracle.com/doc/server.815/a67792/ch04.htm#1364" target=_blank><U><FONT color=#208fff>http://technet.oracle.com/doc/server.815/a67792/ch04.htm#1364</FONT></U></A> <BR>에 자세히 나와있다. 그중 두가지 정도의 case만 기본적으로 다루어보자. <BR><BR>================================================================================================ <BR><BR>* case1 : data를 control file에 직접 입력하여 load하기 <BR><BR>a. data 입력될 table 있어야함 (dept_tmp 라는 table을 생성하여 입력해보자) <BR><BR>SQL> create table dept_tmp as select * from dept; <BR><BR>Table created. <BR><BR>SQL> truncate table dept_tmp; <BR><BR>Table truncated. <BR><BR>b. control file을 구성한다. <BR><BR><TEST.CTL><BR>load data <BR>infile * ==> data 가 ctl file 끝에 있다는 의미 <BR>replace ==> 이게 없으면 빈테이블일때만 load된다. <BR>into table dept_tmp <BR>fileds terminated by ',' optionally enclosed by '"' ==> field 구분자와 "가 들어가면 빼고 입력된다. <BR>(deptno,dname,loc) <BR>begindata <BR>12,research,"saratoga" <BR>10,"accounting",cleveland <BR>11,"art","salem" <BR>13,finance,boston <BR><BR>c. 다음과 같이 sqlldr을 실행 <BR>oracle@swsvrctr:/home/oracle> sqlldr scott/tiger control=test.ctl log=test.log bad=test.bad <BR>error가 생기면 log가 남고 loading 되지 않은 data만 bad file에 남는다. <BR><BR>================================================================================================ <BR><BR>* case 2 : Fixed-format records의 Loading <BR><BR>a. data 입력될 table 있어야함 (dept_tmp 라는 table을 생성하여 입력해보자) <BR>SQL> create table emp_tmp as select * from emp; <BR><BR>Table created. <BR><BR>b. data file의 내용을 보고 그에 맞게 control file을 구성한다. <BR><BR>data file은 다음과 같다고 하자. <BR><TEST.DAT><BR>1111 joo Manager 1111 19191.00 10 <BR>2222 hwang salesman 2222 294974.50 20 <BR>3333 test test 3333 4984.00 40 <BR>4444 kwon engineer 4444 49.90 50 <BR><BR>control file을 만들어보자. <BR><TEST.CTL><BR>load data <BR>infile '/home/oracle/test.dat' <BR>replace <BR>into table emp_tmp <BR><BR>(empno position(01:04) integer external, ==> position을 일일이 맞추어 준다. <BR>ename position(09:14) char, <BR>job position(17:24) char, <BR>mgr position(33:36) integer external, <BR>sal position(41:49) decimal external, <BR>comm position(51:54) decimal external, <BR>deptno position(57:58) integer external) <BR><BR>주의 : data file의 data가 공백이 아닌 tab 으로 되어있으면 position에서 한칸으로 인식되니까 주의 <BR><BR>c. 다음과 같이 sqlldr을 실행 <BR>home/oracle> sqlldr scott/tiger control=test.ctl data=test.dat log=test.log bad=test.bad <BR><BR>================================================================================================ <BR><BR><BR>15. Reorganizing Data <BR><BR>================================================================================================ <BR><BR>* 오래 사용한 table 주로 DML성 문장이 자주 일어나 performance에 영향을 미치므로 주기적으로 <BR> Reorganize 를 해주는것이 좋다. <BR> <BR> Export => table drop => import 순으로 한다. <BR> <BR> export 는 user 별로 table별로 받을 수 있다. <BR> <BR> a. Export <BR><BR>oracle@swsvrctr:/home/oracle> exp scott/tiger tables='dept,emp' file=test.dmp <BR><BR>Export: Release 8.1.6.0.0 - Production on Wed Jul 4 14:32:21 2001 <BR><BR>(c) Copyright 1999 Oracle Corporation. All rights reserved. <BR><BR><BR>Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0, 64 bit - Production <BR>With the Partitioning option <BR>JServer Release 8.1.6.0.0 - Production <BR>Export done in KO16KSC5601 character set and WE8ISO8859P1 NCHAR character set <BR>server uses WE8ISO8859P1 character set (possible charset conversion) <BR><BR>About to export specified tables via Conventional Path ... <BR>. . exporting table DEPT 7 rows exported <BR>. . exporting table EMP 14 rows exported <BR>Export terminated successfully without warnings. <BR><BR> b. drop table <BR> <BR>oracle@swsvrctr:/home/oracle> sqlplus scott/tiger <BR><BR>SQL> drop table emp; <BR>SQL> drop table dept; <BR><BR> c. Import <BR> <BR>oracle@swsvrctr:/home/oracle> imp scott/tiger tables='dept,emp' file=test.dmp <BR><BR>Import: Release 8.1.6.0.0 - Production on Wed Jul 4 14:34:25 2001 <BR><BR>(c) Copyright 1999 Oracle Corporation. All rights reserved. <BR><BR><BR>Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0, 64 bit - Production <BR>With the Partitioning option <BR>JServer Release 8.1.6.0.0 - Production <BR><BR>Export file created by EXPORT:V08.01.06 via conventional path <BR>import done in KO16KSC5601 character set and WE8ISO8859P1 NCHAR character set <BR>import server uses WE8ISO8859P1 character set (possible charset conversion) <BR>. importing SCOTT's objects into SCOTT <BR>. . importing table "DEPT" 7 rows imported <BR>. . importing table "EMP" 14 rows imported <BR>About to enable constraints... <BR>Import terminated successfully without warnings. <BR><BR><BR> d. 제대로 되었나 조회 <BR><BR>SQL> select * from dept; <BR>SQL> select * from emp; <BR><BR>================================================================================================ <BR><BR><BR>16. Managing Password Security and Resources <BR><BR>================================================================================================ <BR><BR><BR>* verify_function 생성을 위해 돌려줘야 할 script : $ORACLE_HOME/rdbms/admin/utlpwdmg.sql <BR><BR> NAME <BR> utlpwdmg.sql - script for Default Password Resource Limits <BR><BR> DESCRIPTION <BR> This is a script for enabling the password management features <BR> by setting the default password resource limits. <BR><BR> NOTES <BR> This file contains a function for minimum checking of password <BR> complexity. This is more of a sample function that the customer <BR> can use to develop the function for actual complexity checks that the <BR> customer wants to make on the new password. <BR><BR> MODIFIED (MM/DD/YY) <BR> asurpur 04/17/97 - Fix for bug479763 <BR> asurpur 12/12/96 - Changing the name of password_verify_function <BR> asurpur 05/30/96 - New script for default password management <BR> asurpur 05/30/96 - Created <BR><BR>================================================================================================ <BR><BR>* verify_function 생성 및 패스워드 관리기능 활성화 <BR><BR>SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg <BR><BR>Function created. <BR><BR><BR>Profile altered. <BR><BR>================================================================================================ <BR><BR>* 사용자 생성 <BR><BR>utlpwdmg.sql script를 돌려 verify_function을 생성후 user를 생성할때는 몇가지를 check하여 좀더 <BR>password 관리를 할 수 있도록 해준다. <BR>다음과 같이 user/passwd를 같게 하면 error가 나서 생성되지 않는다. <BR><BR>SQL> create user myuser identified by myuser <BR> 2 default tablespace TS_USER1 <BR> 3 temporary tablespace TEMP; <BR>create user myuser identified by myuser <BR>* <BR>ERROR at line 1: <BR>ORA-28003: password verification for the specified password failed <BR>ORA-20001: Password same as user <BR><BR>다시 시도 <BR>SQL> create user myuser identified by mypasswd9$ <BR> 2 default tablespace ts_user1 <BR> 3 temporary tablespace temp; <BR><BR>User created. <BR><BR>SQL> grant connect,resource to myuser; <BR><BR>확인 <BR>SQL> connect myuser/mypasswd9$ <BR><BR>================================================================================================ <BR><BR>* 패스워드 관리/ expire 시키기 <BR><BR>SQL> alter user myuser password expire; <BR><BR>User altered. <BR><BR>admin이 강제로 expire시켰기 때문에 다음과 같이 password변경을 뭍는다. <BR>SQL> connect myuser/mypassword9$ <BR>Changing password for test <BR>New password: <BR>Retype new password: <BR>Password changed <BR>Connected. <BR><BR>password를 또 규칙에 맞지 않게 넣으면 다음과 같은 error가 발생한다. <BR><BR>ERROR: <BR>ORA-00988: missing or invalid password(s) <BR><BR>================================================================================================ <BR><BR>* dictionary 조회 <BR><BR>SQL> select resource_name,limit from dba_profiles <BR> 2 where profile='DEFAULT' and resource_type='PASSWORD'; <BR><BR>RESOURCE_NAME LIMIT <BR>-------------------- --------------- <BR>FAILED_LOGIN_ATTEMPT 3 <BR>S <BR><BR>PASSWORD_LIFE_TIME 60 <BR>PASSWORD_REUSE_TIME 1800 <BR>PASSWORD_REUSE_MAX UNLIMITED <BR>PASSWORD_VERIFY_FUNC VERIFY_FUNCTION <BR>TION <BR><BR>PASSWORD_LOCK_TIME .0006 <BR>PASSWORD_GRACE_TIME 10 <BR><BR>RESOURCE_NAME LIMIT <BR>-------------------- --------------- <BR>FAILED_LOGIN_ATTEMPT 3 <BR>S <BR><BR>PASSWORD_LIFE_TIME 60 <BR>PASSWORD_REUSE_TIME 1800 <BR>PASSWORD_REUSE_MAX UNLIMITED <BR>PASSWORD_VERIFY_FUNC VERIFY_FUNCTION <BR>TION <BR><BR>PASSWORD_LOCK_TIME .0006 <BR>PASSWORD_GRACE_TIME 10 <BR><BR>================================================================================================ <BR><BR><BR>17. Managing Users <BR><BR>================================================================================================ <BR><BR>* OS 인증 : os에 login 한 id/passwd로 oracle의 id/passwd로 함께 사용하기 위한방법 <BR><BR>a. user를 생성하는데 identified externally로 생성한다. <BR><BR>oracle@swsvrctr:/home/oracle> sqlplus internal <BR>SQL> create user oracle identified externally <BR> 2 default tablespace ts_user1 <BR> 3 temporary tablespace temp; <BR><BR>User created. <BR><BR>SQL> grant connect,resource to oracle; <BR>SQL> revoke unlimited tablespace from oracle; <BR>SQL> shutdown immediate <BR><BR>b. init<SID>.ora 에서 다음을 편집한다.(추가) <BR>os_authent_prefix="" <BR><BR>c. startup하고 확인해본다. <BR><BR>SQL> startup <BR>SQL> exit <BR>oracle@swsvrctr:/home/oracle> sqlplus / ==> id/passwd를 넣을 필요없이 접속 <BR><BR>SQL> select user from dual; <BR><BR>USER <BR>------------------------------------------------------------ <BR>ORACLE <BR><BR>제대로 접속이 된다. <BR><BR>================================================================================================ <BR><BR>* tablespace 사용량 통제 <BR><BR>SQL> connect internal <BR>Connected. <BR>SQL> col tablespacename format a10 <BR>SQL> col username format a10 <BR>SQL> alter user oracle quota 20k on ts_user1; ==> oracle user는 ts_user1에 20k 만 사용 가능하다. <BR><BR>User altered. <BR><BR>SQL> select * from dba_ts_quotas; <BR><BR>TABLESPACE_NAME USERNAME <BR>------------------------------------------------------------ ---------- <BR> BYTES MAX_BYTES BLOCKS MAX_BLOCKS <BR>---------- ---------- ---------- ---------- <BR>TS_USER1 ORACLE <BR> 0 20480 0 5 <BR><BR>SQL> connect / <BR>Connected. <BR>SQL> select user from dual; <BR><BR>USER <BR>------------------------------------------------------------ <BR>ORACLE <BR><BR>SQL> create table test <BR> 2 (id number(10)) <BR> 3 (tablespace ts_user1 <BR> 4 storage(initial 20k) <BR> <BR>까지는 생성이 되나 다음과 같이 늘리려고 하면 error가 난다. <BR><BR>SQL> alter table test allocate extent(size 4k); ==> error <BR><BR>================================================================================================ <BR><BR><BR>18. Managing Privileges <BR><BR>================================================================================================ <BR><BR>* with grant option과 with admin option <BR><BR>(둘다 실행 권한을 받은 user가 다시 실행 권한을 다른 user에게 줄 수 있게 해주는 option이다.) <BR>-- 차이는 with admin option으로 권한을 받은 user1이 다른 user2에게 권한을 부여한 후 user1으로부터 <BR> 권한을 revoke하면 user1의 권한만 revoke되나 <BR> with grant option으로 부여하면 user1에게 revoke 될 시 user2의 권한도 cascade로 revoke된다. <BR><BR><BR>oracle@swsvrctr:/home/oracle> sqlplus internal <BR><BR>SQL> col grantor format a10 <BR>SQL> col grantee format a10 <BR>SQL> col table_name format a10 <BR>SQL> col table_schema format a10 <BR>SQL> col privilege format a25 <BR>SQL> grant create user to scott with admin option; ==> with admin option으로 권한 부여후 <BR>SQL> connect scott/tiger <BR>SQL> grant create user to oracle; ==> 다시 oracle 에게 같은 권한 부여후 <BR>SQL> connect internal <BR>SQL> select * from dba_sys_privs <BR> 2 where grantee in ('SCOTT','ORACLE'); <BR><BR>GRANTEE PRIVILEGE ADMIN_ <BR>---------- ------------------------- ------ <BR>ORACLE CREATE USER NO <BR>SCOTT CREATE USER YES <BR>SCOTT UNLIMITED TABLESPACE NO <BR><BR>SQL> revoke create user from scott; ==> scott의 권한을 revoke <BR>SQL> select * from dba_sys_privs <BR> 2 where grantee in ('SCOTT','ORACLE'); <BR><BR>GRANTEE PRIVILEGE ADMIN_ <BR>---------- ------------------------- ------ <BR>ORACLE CREATE USER NO ==> scott의 create user 권한만 revoke되었다. <BR>SCOTT UNLIMITED TABLESPACE NO oracle권한은 그대로 <BR><BR>SQL> grant select on dept to oracle with grant option; ==> with grant option 으로 권한 부여후 <BR>SQL> connect / <BR>SQL> create user myuser identified by myuser1$ <BR> 2 default tablespace ts_user1 <BR> 3 temporary tablespace temp; <BR><BR>SQL> grant select on scott.dept to myuser; ==> 다시 같은 권한을 다른 myuser에게 부여 <BR><BR>Grant succeeded. <BR><BR>SQL> select * from all_tab_privs <BR> 2 where table_name='DEPT'; <BR><BR>GRANTOR GRANTEE TABLE_SCHE TABLE_NAME PRIVILEGE GRANTA <BR>---------- ---------- ---------- ---------- ------------------------- ------ <BR>SCOTT ORACLE SCOTT DEPT SELECT YES <BR>ORACLE MYUSER SCOTT DEPT SELECT NO <BR><BR>SQL> revoke select on dept from oracle; ==> oracle의 권한을 revoke <BR><BR>Revoke succeeded. <BR><BR>SQL> select * from all_tab_privs ==> with grant option으로 생성된 이하 myuser의 권한도 <BR> 2 where table_name='DEPT'; revoke 되었다. <BR><BR>no rows selected <BR><BR><BR>================================================================================================ <BR><BR>* Database Auditing : user 사용 시간정보 확인, 동시사용자측정 등 여러가지에 필요 <BR><BR>SQL> connect internal <BR>SQL> shutdown immediate <BR><BR>init<SID>.ora file에서 편집 <BR>audit_trail = true # if you want auditing ==> 주석기호(#) 삭제 <BR><BR>SQL> startup <BR><BR>SQL> show parameter audit_trail <BR>NAME TYPE VALUE <BR>------------------------------------ -------------- ------------------------------ <BR>audit_trail string TRUE <BR><BR>SQL> audit connect; <BR>SQL> select * from dba_stmt_audit_opts; <BR>USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE <BR>-------------------- -------------------- --------------- ---------- ---------- <BR> CREATE SESSION BY ACCESS BY ACCESS <BR> <BR>SQL> connect scott/tiger <BR>SQL> connect scott/fail <BR>SQL> connect internal <BR>SQL> select username,timestamp,action_name,logoff_time,returncode <BR> 2 from dba_audit_session; <BR><BR>USERNAME TIMESTAMP ACTION_NAME LOGOFF_TI RETURNCODE <BR>---------- --------- --------------- --------- ---------- <BR>SCOTT 05-JUL-01 LOGOFF 05-JUL-01 0 ==> login 성공하면 0 return <BR>SCOTT 05-JUL-01 LOGON 1017 ==> login 실패한 returncode <BR><BR>SQL> shutdown <BR><BR>파라미터 이전대로 돌려두자(#audit_trail=true : 주석처리) <BR><BR>SQL> startup 하고 <BR>SQL> show parameter audit_trail <BR>NAME TYPE VALUE <BR>------------------------------------ -------------- ------------------------------ <BR>audit_trail string NONE <BR><BR>================================================================================================ <BR><BR><BR>19. Managing Roles <BR><BR>================================================================================================ <BR><BR>* Role <BR><BR>resource role에 포함된 권한을 살펴보자 <BR>SQL> select * from dba_sys_privs <BR> 2 where grantee='RESOURCE'; <BR> <BR><BR>GRANTEE PRIVILEGE ADMIN_ <BR>---------- -------------------- ------ <BR>RESOURCE CREATE CLUSTER NO <BR>RESOURCE CREATE INDEXTYPE NO <BR>RESOURCE CREATE OPERATOR NO <BR>RESOURCE CREATE PROCEDURE NO <BR>RESOURCE CREATE SEQUENCE NO <BR>RESOURCE CREATE TABLE NO <BR>RESOURCE CREATE TRIGGER NO <BR>RESOURCE CREATE TYPE NO <BR><BR>8 rows selected. <BR><BR>다음은 dev라는 role을 만들어서 <BR>SQL> create role dev; <BR>SQL> grant create table,create view to dev; <BR>SQL> grant select on emp to dev; <BR>SQL> connect internal <BR><BR>oracle이라는 user에게 dev,resource role, create session권한 부여 <BR>SQL> connect internal <BR>SQL> grant dev to oracle; <BR>SQL> grant resource to oracle; <BR>SQL> grant create session to oracle; <BR>SQL> alter user oracle default role resource; ==> session의 연결 끊김에 상관없이 지속적으로 <BR>SQL> grant select_catalog_role to oracle; logon 후 resource role이 enable되게 함 <BR> (set 할 필요 없이) <BR>SQL> select segment_name,status from dba_rollback_segs; ==> 현재 session에서 select_catalog_role이 <BR>select segment_name,status from dba_rollback_segs disabled됨 <BR> * <BR>ERROR at line 1: <BR>ORA-00942: table or view does not exist <BR><BR>SQL> set role select_catalog_role; ==> set은 현재session에서 role을 사용가능하게 해줌. <BR> 이를 위해 이 role은 이미 user에게 grant 되어있어야함. <BR>SQL> select segment_name,status from dba_rollback_segs; <BR><BR>--------------- -------------------------------- <BR>SEGMENT_NAME STATUS <BR>SYSTEM ONLINE <BR>RBS1 ONLINE <BR>RBS2 ONLINE <BR><BR>================================================================================================ <BR><BR><BR>20. Using National Language Support <BR><BR>================================================================================================ <BR><BR>* sysdate format 변경 <BR><BR>SQL> connect internal <BR>SQL> select sysdate from dual; <BR><BR>SYSDATE <BR>--------- <BR>05-JUL-01 <BR><BR>SQL> alter session set nls_date_format='YY/MM/DD:HH24:MI:SS'; <BR>SQL> select sysdate from dual; <BR><BR>SYSDATE <BR>----------------- <BR>01/07/05:12:25:37 <BR><BR>================================================================================================ <BR><BR>* Character set 변경하고 한글명 table 만들기 <BR>-- 가급적 한글명 table은 만들지 않는것이 좋으나 만들수 없는것은 아니다. <BR><BR>SQL> create table scott.부서 as select scott.dept; <BR>create table scott.부서 as select scott.dept <BR> * <BR>ERROR at line 1: <BR>ORA-00911: invalid character ==> 테이블명이 한글이어서 error난다. <BR><BR><BR>* Database Characterset을 변경해 보자. ==> 매우 조심스러운 작업 <BR> (DATA 보존 못할 위험성 있다.backup 필요) <BR><BR>SQL> select * from nls_database_parameters ==> nls_database_parameters 에서 현재 DB의 <BR> 2 where parameter like '%CHARACTERSET%'; characterset관련을 parameter를 확인 <BR> <BR>PARAMETER VALUE <BR>------------------------- -------------------- <BR>NLS_CHARACTERSET WE8ISO8859P1 <BR>NLS_NCHAR_CHARACTERSET WE8ISO8859P1 <BR><BR>SQL> select value from v$nls_valid_values ==> 234건의 data가 있다. <BR> 2 where parameter like '%CHARACTERSET'; <BR><BR>-- warning : character set을 변경 할수는 있지만 기존에 들어가있는 데이타에 대해서는 책임 못짐. <BR><BR>a. <BR>SQL> shutdown immediate <BR>SQL> connect internal <BR>SQL> startup mount exclusive; <BR>SQL> alter system enable restricted session; <BR>SQL> alter database open; <BR><BR>b. <BR>SQL> alter database character set ko16ksc5601; <BR>SQL> alter database national chartacter charcter set ko16ksc5601; <BR>-- 확인 <BR>SQL> select * from nls_database_parameters ==> nls_database_parameters 에서 현재 DB의 <BR> 2 where parameter like '%CHARACTERSET%'; characterset관련을 parameter를 확인 <BR><BR>shutdown immediate; <BR><BR>c. .profile edit <BR>NLS_LANG=Amerian_America.us7ascii; export NLS_LANG을 <BR>NLS_LANG=korean_korea.ko16ksc5601; export NLS_LANG로 변경 <BR><BR>d. Database startup <BR>
<P>출처 : <A class=con_link href="http://www.xguru.pe.kr/techbrd/read.cgi?board=ksko&y_number=6" target=_blank><FONT color=#0033cc>http://www.xguru.pe.kr/techbrd/read.cgi?board=ksko&y_number=6</FONT></A></P>
<!-- -->
]]>
카페 게시글
◆ 컴관련강좌
[Oracle]오라클 어드민 팁
자바짱
추천 0
조회 379
05.07.20 11:24
댓글 7
다음검색
첫댓글 정말 괜찮은 자료네요..^^
안방에서 팬티노출 아줌마와 섯다의 짜릿함!
http://sdg938.net
승부사들의 진정한 한판 승부! “ 화투 “
회원가입만 하셔도 5000원의 무료머니를 드립니다
귀찮은 다운로드 이제그만,웹에서 바로 즐기는 화투!
http://sdg938.net
국내 최다 회원보유 고객만족도1위!!!
화투의 짜릿한 손맛을 즐겨보세요.
안방에서 팬티노출 아줌마와 섯다의 짜릿함!
http://sdg938.net
승부사들의 진정한 한판 승부! “ 화투 “
회원가입만 하셔도 5000원의 무료머니를 드립니다
귀찮은 다운로드 이제그만,웹에서 바로 즐기는 화투!
http://sdg938.net
국내 최다 회원보유 고객만족도1위!!!
화투의 짜릿한 손맛을 즐겨보세요.
안방에서 팬티노출 아줌마와 섯다의 짜릿함!
http://sdg938.net
승부사들의 진정한 한판 승부! “ 화투 “
회원가입만 하셔도 5000원의 무료머니를 드립니다
귀찮은 다운로드 이제그만,웹에서 바로 즐기는 화투!
http://sdg938.net
국내 최다 회원보유 고객만족도1위!!!
화투의 짜릿한 손맛을 즐겨보세요.
안방에서 팬티노출 아줌마와 섯다의 짜릿함!
http://kkt98.net
승부사들의 진정한 한판 승부! “ 화투 “
회원가입만 하셔도 5000원의 무료머니를 드립니다
귀찮은 다운로드 이제그만,웹에서 바로 즐기는 화투!
http://kkt98.net
국내 최다 회원보유 고객만족도1위!!!
화투의 짜릿한 손맛을 즐겨보세요.
안방에서 팬티노출 아줌마와 섯다의 짜릿함!
http://kkt98.net
승부사들의 진정한 한판 승부! “ 화투 “
회원가입만 하셔도 5000원의 무료머니를 드립니다
귀찮은 다운로드 이제그만,웹에서 바로 즐기는 화투!
http://kkt98.net
국내 최다 회원보유 고객만족도1위!!!
화투의 짜릿한 손맛을 즐겨보세요.
안방에서 팬티노출 아줌마와 섯다의 짜릿함!
http://kkt98.net
승부사들의 진정한 한판 승부! “ 화투 “
회원가입만 하셔도 5000원의 무료머니를 드립니다
귀찮은 다운로드 이제그만,웹에서 바로 즐기는 화투!
http://kkt98.net
국내 최다 회원보유 고객만족도1위!!!
화투의 짜릿한 손맛을 즐겨보세요.