[관리] 오라클 DBA 기술 노트

Oracle Administraton Technical Documents ( 오라클 DBA 기술 노트 )

================================================================================================  

1. DBMS = database(data file & control file & redo log file) +  

                                                   instance(memory & background processes)

================================================================================================  

2. Oracle Architecture Component

================================================================================================  

* Oracle Instance 확인 : v$instance

SQL> select instance_name from v$instance;

INSTANCE_NAME

—————-

IBM

================================================================================================  

* datafile들의 경로 및 정보 : v$datafile

SQL> select name from v$datafile;

NAME

——————————————————————————–

/oracle/ora_data/system/system01.dbf

/oracle/ora_data/data/tools01.dbf

/oracle/ora_data/data/rbs01.dbf

/oracle/ora_data/data/temp01.dbf

/oracle/ora_data/data/users01.dbf

================================================================================================  

* control file의 경로 및 정보 : v$controlfile;

SQL> select name from v$controlfile;

NAME

——————————————————————————–

/oracle/ora_data/contr1/ora_control1

/oracle/ora_data/contr2/ora_control2

================================================================================================  

* logfile의 경로 및 정보 : v$logfile

SQL> select member from v$logfile;

MEMBER

——————————————————————————–

/oracle/ora_data/redolog_a/redo1a.log

/oracle/ora_data/redolog_b/redo1b.log

/oracle/ora_data/redolog_a/redo2a.log

/oracle/ora_data/redolog_b/redo2b.log

/oracle/ora_data/redolog_a/redo3a.log

/oracle/ora_data/redolog_b/redo3b.log

================================================================================================  

* System Global Area 내용을 조회

SQL> select * from v$sga;

NAME                      VALUE

——————– ———-

Fixed Size               108588

Variable Size          27631616

Database Buffers        2252800

Redo Buffers              77824

SQL> show sga

Total System Global Area   30070828 bytes

Fixed Size                   108588 bytes

Variable Size              27631616 bytes

Database Buffers            2252800 bytes

Redo Buffers                  77824 bytes

================================================================================================  

* 현재 수행중인 background process들을 확인

SQL> select paddr,name,description from v$bgprocess where paddr>’00’;

PADDR            NAME  DESCRIPTION

—————- —– —————————————————————-

070000000139ABC0 PMON  process cleanup

070000000139AFD0 DBW0  db writer process 0

070000000139B3E0 LGWR  Redo etc.

070000000139B7F0 CKPT  checkpoint

070000000139BC00 SMON  System Monitor Process

070000000139C010 RECO  distributed recovery

SQL> !ps -ef|grep ora|grep

oracle 25148     1   0  19:25:34      –  0:00 ora_reco_IBM

oracle 60576     1   0  19:25:34      –  0:00 ora_smon_IBM

oracle 60782     1   0  19:25:34      –  0:00 ora_pmon_IBM

oracle 70166     1   0  19:25:34      –  0:00 ora_lgwr_IBM

oracle 72248     1   0  19:25:34      –  0:00 ora_ckpt_IBM

oracle 84918     1   0  19:25:34      –  0:00 ora_dbw0_IBM

================================================================================================  

* 초기화 파라미터 파일 : init.ora

================================================================================================  

* database log 모드 확인

SQL> connect internal

Connected.

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /oracle/app/oracle/product/8.1.6/dbs/arch

Oldest online log sequence     20

Current log sequence           22

SQL> select log_mode from v$database;

LOG_MODE

————

NOARCHIVELOG

================================================================================================  

3. Managing an Oracle Instance

================================================================================================  

단계별 :

shutdown : oracle이 내려가 있는 상태

nomount : instance started(SGA, B.G process를 시작 init.ora에서 읽어서)

alert, trace file open

– 이 단계에서 할 수 있는 것은  

a. db creation

– 이 상태에서도 볼수있는 view

v$parameter

v$dga

v$option

v$process

v$session

v$version

v$instance

mount : control file opened for this instance

– 이 단계에서 할 수 있는 것은 control file의 내용을 변경하는것

a. archivelog mode로 변환

b. data file/redo log file rename시

c. recovery시

– SQL>alter database open read only;

로 하게되면 data file에 writing을 허용 안함.

open : control file에 기술된 모든 files open

================================================================================================  

* parameter 변경 종류

a. init.ora 에서 변경

b. alter session set ~

c. alter system set ~    => shutdown 될때까지 변경된것 유효

  alter system deffered set ~ => 현재 session에서만 변경된것 유효

================================================================================================  

* 특정 session 죽이기

SQL> select sid, serial#,username,status from v$session; => (특정 user는 where username=’SCOTT’로)

      SID    SERIAL# USERNAME                       STATUS

———- ———- —————————— ——–

        1          1                                ACTIVE

        2          1                                ACTIVE

        3          1                                ACTIVE

        4          1                                ACTIVE

        5          1                                ACTIVE

        6          1                                ACTIVE

        7          1 SYS                            ACTIVE

SQL> alter system kill session ‘7,3’    — 7은 sid, 3은 serial#

================================================================================================  

* alert file 과 trace file

– alert file은 꼭 1개, 중요한사건,시간순으로 (startup,shutdown,recovery)

– trace file은 여러개 가능, background process는 background_dump_dest에 생기고 server process는

user_dump_dest에 생성된다.

================================================================================================  

4. Creating a Database

================================================================================================  

* Create a Database Manually

a. OS Environment setting

.profile에 ORACLE_HOME,ORACLE_SID,ORA_NLS33,PATH,(ORACLE_BASE) 등을 편집한다.

ex)

DISPLAY=swsvrctr:0.0

ORACLE_HOME=/oracle/app/oracle/product/8.1.7

PATH=$ORACLE_PATH/bin:/usr/ccs/bin:$PATH

NLS_LANG=AMERICAN_AMERICA.KO16KSC5601

ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data

ORACLE_SID=IBM

b. init.ora file을 copy하고 편집한다.

file

db_name=KYS

control_files = (/home/oracle/data02/control/control01.ctl,/home/oracle/data02/control/control02.ctl)

db_block_size = 8192

기본적으로 위 두개 parameter외에

rollback_segments=(rbs1,rbs2,..)  =>나중에 rollback segment생성후 DB start시 Online되는 rbs지정

background_dump_dest=/home/oracle/data02/bdump

user_dump_dest=/home/oracle/data02/udump

core_dump_dest=/home/oracle/data02/cdump

c. Starting the Instance

SQL> startup nomount

SQL> startup nomount pfile=initKYS.ora

SQL> create database KYS

2     maxlogfiles 5

3     maxlogmembers 5

4     maxdatafiles 100

5     maxloghistory 100

6  logfile

7     group 1 (‘/home/oracle/data02/redolog/log1a.rdo’,’/home/oracle/data02/redolog2/log1b.rdo’) size 1m,

8     group 2 (‘/home/oracle/data02/redolog/log2a.rdo’,’/home/oracle/data02/redolog2/log2b.rdo’) size 1m

9  datafile

10     ‘/home/oracle/data02/data/system01.dbf’ size 50m autoextend on

11  character set “KO16KSC5601”;

일단 여기까지 database는 생성이 되었다.

이후부터는 추가적인 작업이다.

d. 추가 system rollback segment 생성

SQL> create rollback segment r0 tablespace system

2  storage (initial 16k next 16k minextents 2 maxextents 10);

e. rollback sement online

SQL> alter rollback segment r0 online;

f. rollback segment tablespace 생성 & datafile 저장위치, 크기 및 초기값 지정

SQL> create tablespace rbs

2  datafile ‘/home/oracle/data02/data/rbs01.dbf’ size 300m

3  default storage(

4  initial            4M

5  next               4M

6  pctincrease        0

7  minextents         10

8  maxextents         unlimited);

g. rollback segment 생성

SQL> create rollback segment r01 tablespace rbs

2  storage (minextents 10 optimal 40M);

SQL> create rollback segment r02 tablespace rbs

2  storage (minextents 10 optimal 40M);

SQL> create rollback segment r03 tablespace rbs

2  storage (minextents 10 optimal 40M);

SQL> create rollback segment r04 tablespace rbs

2  storage (minextents 10 optimal 40M);

h. rollback segment online

SQL> alter rollback segment r01 online;

SQL> alter rollback segment r02 online;

SQL> alter rollback segment r03 online;

SQL> alter rollback segment r04 online;

i. 추가 system rollback segment off-line 및 삭제  

SQL> alter rollback segment r0 offline;

SQL> drop rollback segment r0;

j. sorting 작업시 필요한 temporary tablespace 생성 & datafile 저장 위치, 크기 및 초기값 지정

SQL> create tablespace temp

2  datafile ‘/home/oracle/data02/data/temp01.dbf’ size 300 temporary

3  default storage(

4  initial            4M

5  next               4M

6  maxextents         unlimited

7  pctincrease        0);

k. 추가 tablespace 생성 & data file 저장 위치 및 크기 지정

SQL> create tablespace tools

2  datafile ‘/home/oracle/data02/data/tools.dbf’ size 50m

3  default storage(

4  maxextents 505

5  pctincrease 0);

SQL> create tablespace users

2  datafile ‘/home/oracle/data02/data/user01.dbf’ size 30M

3  default storage(

4  maxextents 505

5  pctincrease 0);

l. 작업 환경에서 추가적으로 필요한 tablespace는 위의 방법으로 생성한다.

================================================================================================  

5. Data Dictionary and Standard Package

================================================================================================  

* database 생성후 돌려줘야 할 script

$ORACLE_HOME/rdbms/admin/catalog.sql ==> dictionary views, export utility views 생성

$ORACLE_HOME/rdbms/admin/catproc.sql ==> procedures, functions 생성

$ORACLE_HOME/rdbms/admin/catdbsyn.sql ==> synonyms 생성

================================================================================================  

* Dictionary list 확인

SQL> col table_name format a30

SQL> col comments format a45

SQL> set pages 800

SQL> spool dictionary.lst

SQL> select * from dictionary order by 1 ==> 전체 dictionary의 list를 볼 수 있다.

SQL> spool off

SQL> ed sictionary.lst

SQL> select * from dictionary where table_name like ‘%TABLE%’; ==> table 관련 dictionary  

SQL> select * from dictionary where table_name like ‘%INDEX%’;  ==> index 관련 dictionary

================================================================================================  

* 유용한 dictionary  

TABLE_NAME                     COMMENTS

—————————— ———————————————

DBA_USERS                      Information about all users of the database

DBA_TABLESPACES                Description of all tablespaces

DBA_DATA_FILES                 Information about database data files

DBA_FREE_SPACE                 Free extents in all tablespaces

DBA_OBJECTS                    All objects in the database

DBA_SEGMENTS                   Storage allocated for all database segments

DBA_ROLLBACK_SEGS              Description of rollback segments

DBA_EXTENTS                    Extents comprising all segments in the database

DBA_TABLES                     Description of all relational tables in the d

                              atabase

DBA_INDEXES                    Description for all indexes in the database

DBA_VIEWS                      Description of all views in the database

DBA_TRIGGERS                   All triggers in the database

DBA_SOURCE                     Source of all stored objects in the database

================================================================================================  

* sample Query

SQL> select username,default_tablespace,temporary_tablespace from dba_users;

SQL> select tablespace_name,bytes,file_name from dba_data_files;

SQL> select tablespace_name,count(*),sum(bytes) from dba_free_space

2  group by tablespace_name;

================================================================================================  

6. Maintiaining the Contorol File

================================================================================================  

* Control File 리스트 조회

SQL> select name from v$controlfile;

NAME

——————————————————————————–

/home/oracle/data01/oradata/IBM/control01.ctl

/home/oracle/data01/oradata/IBM/control02.ctl

/home/oracle/data01/oradata/IBM/control03.ctl

================================================================================================  

* Control File 을 하나 추가해보자

a. database shutdown

SQL> shutdown immediate

b. control file 복사(os상 물리적인 복사)

/home/oracle/data01/oradata/IBM> cp control03.ctl control04.ctl ==> 실제는 다른 disk로 복사해야함

   문제발생을 대비해 분리하는것임.

c. Parameter File 편집

control_files = (“/home/oracle/data01/oradata/IBM/control01.ctl”,  

“/home/oracle/data01/oradata/IBM/control02.ctl”,  

“/home/oracle/data01/oradata/IBM/control03.ctl”,  

“/home/oracle/data01/oradata/IBM/control04,ctl”)

d. database startup & 확인

SQL> startup

SQL> select name from v$controlfile;

NAME

——————————————————————————–

/home/oracle/data01/oradata/IBM/control01.ctl

/home/oracle/data01/oradata/IBM/control02.ctl

/home/oracle/data01/oradata/IBM/control03.ctl

/home/oracle/data01/oradata/IBM/control04.ctl ==> 하나 더 추가되었지요…(실제는 다른disk로)

================================================================================================  

7. Multiplexing Redo Log Files

================================================================================================  

* Redo Log File 리스트 조회

SQL> select group#,sequence#,bytes,members,status from v$log;

   GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS

———- ———- ———- ———- ——————————–

        1        862     512000          1 CURRENT

        2        860     512000          1 INACTIVE

        3        861     512000          1 INACTIVE

SQL> select * from v$logfile;

   GROUP# STATUS         MEMBER

———- ————– ————————————————–

        1                /home/oracle/data01/oradata/IBM/redo03.log

        2                /home/oracle/data01/oradata/IBM/redo02.log

        3                /home/oracle/data01/oradata/IBM/redo01.log          

================================================================================================  

* Log Group 추가(기존 로그 파일과 동일한 사이즈로)

SQL> alter database add logfile

2  ‘/home/oracle/data01/oradata/IBM/redo04.log’ size 200k;

SQL> select group#,sequence#,bytes,members,status from v$log;

   GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS

———- ———- ———- ———- ——————————–

        1        862     512000          1 CURRENT

        2        860     512000          1 INACTIVE

        3        861     512000          1 INACTIVE

        4          0     204800          1 UNUSED

SQL> select * from v$logfile;

   GROUP# STATUS         MEMBER

———- ————– ————————————————–

        1                /home/oracle/data01/oradata/IBM/redo03.log

        2                /home/oracle/data01/oradata/IBM/redo02.log

        3                /home/oracle/data01/oradata/IBM/redo01.log

        4                /home/oracle/data01/oradata/IBM/redo04.log

================================================================================================  

* Log Group 별 멤버 파일 추가    ==> backup 시 risk줄이기 위해 실제는 다른 disk에 해야함.

SQL> alter database add logfile member

2  ‘/home/oracle/data01/oradata/IBM/redo01b.log’ to group 1,

3  ‘/home/oracle/data01/oradata/IBM/redo02b.log’ to group 2,

4  ‘/home/oracle/data01/oradata/IBM/redo03b.log’ to group 3,

5  ‘/home/oracle/data01/oradata/IBM/redo04b.log’ to group 4;

================================================================================================  

* 확인

SQL> !ls /home/oracle/data01/oradata/IBM/*.log

/home/oracle/data01/oradata/IBM/redo01.log   /home/oracle/data01/oradata/IBM/redo03.log

/home/oracle/data01/oradata/IBM/redo01b.log  /home/oracle/data01/oradata/IBM/redo03b.log

/home/oracle/data01/oradata/IBM/redo02.log   /home/oracle/data01/oradata/IBM/redo04.log

/home/oracle/data01/oradata/IBM/redo02b.log  /home/oracle/data01/oradata/IBM/redo04b.log

SQL> select group#,sequence#,bytes,members,status from v$log;

   GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS

———- ———- ———- ———- ——————————–

        1        862     512000          2 CURRENT

        2        860     512000          2 INACTIVE

        3        861     512000          2 INACTIVE

        4          0     204800          2 UNUSED ==> 아직 한번도 사용되지 않음

SQL> select * from v$logfile;

   GROUP# STATUS         MEMBER

———- ————– ————————————————–

        1                /home/oracle/data01/oradata/IBM/redo03.log

        2                /home/oracle/data01/oradata/IBM/redo02.log

        3                /home/oracle/data01/oradata/IBM/redo01.log

        4                /home/oracle/data01/oradata/IBM/redo04.log

        1 INVALID        /home/oracle/data01/oradata/IBM/redo01b.log

        2 INVALID        /home/oracle/data01/oradata/IBM/redo02b.log

        3 INVALID        /home/oracle/data01/oradata/IBM/redo03b.log

        4 INVALID        /home/oracle/data01/oradata/IBM/redo04b.log

==> 현재 사용되고 있는 log group 은 group 1이고 나중에 추가한 member들은 invalid 한 상태이다.

강제로 log switch를 일으켜서 valid하게 바꾸자.

SQL> alter system switch logfile;

SQL> select group#,sequence#,bytes,members,status from v$log;

   GROUP#  SEQUENCE#      BYTES    MEMBERS STATUS

———- ———- ———- ———- ——————————–

        1        862     512000          2 ACTIVE

        2        860     512000          2 INACTIVE

        3        861     512000          2 INACTIVE

        4        863     204800          2 CURRENT ==> unused에서 바뀜.

SQL> select * from v$logfile;

   GROUP# STATUS         MEMBER

———- ————– ————————————————–

        1                /home/oracle/data01/oradata/IBM/redo03.log

        2                /home/oracle/data01/oradata/IBM/redo02.log

        3                /home/oracle/data01/oradata/IBM/redo01.log

        4                /home/oracle/data01/oradata/IBM/redo04.log

        1 INVALID        /home/oracle/data01/oradata/IBM/redo01b.log

        2 INVALID        /home/oracle/data01/oradata/IBM/redo02b.log

        3 INVALID        /home/oracle/data01/oradata/IBM/redo03b.log

        4                /home/oracle/data01/oradata/IBM/redo04b.log ==> valid하게 바뀜

        

================================================================================================  

Log Miner

================================================================================================  

* Parameter File 의 utl_file_dir 편집

a. 확인

SQL> select name,value from v$parameter

2  where name=’utl_file_dir’;

NAME                 VALUE

——————– ——————————

utl_file_dir

SQL> !mkdir $ORACLE_HOME/LOG

b. LogMiner사용을 위해 init.ora file 편집

utl_file_dir=/oracle/app/oracle/product/8.1.7/LOG

c. restart

SQL> shutdown immediate

SQL> startup

확인

SQL> select name,value from v$parameter

2  where name=’utl_file_dir’;

NAME                 VALUE

——————– ——————————

utl_file_dir         /oracle/app/oracle/product/8.1.7/LOG ==> LogMiner 준비를 위한 parameter set

d. LogMiner setting – 반드시 트랜잭션의 첫번째 명령이어야 함

SQL> commit;

SQL> exec dbms_logmnr_d.build(‘v817dict.ora’,’/oracle/app/oracle/product/8.1.7/LOG’);

BEGIN dbms_logmnr_d.build(‘v817dict.ora’,’/oracle/app/oracle/product/8.1.7/LOG’); END;

*

ERROR at line 1:

ORA-06532: Subscript outside of limit

ORA-06512: at “SYS.DBMS_LOGMNR_D”, line 793

ORA-06512: at line 1

SQL> !ls $ORACLE_HOME/LOG

SQL> exec dbms_logmnr.add_logfile(‘/home/oracle/data01/oradata/IBM/redo01.log’,DBMS_LOGMNR.NEW);

SQL> exec dbms_logmnr.add_logfile(‘/home/oracle/data01/oradata/IBM/redo02.log’,DBMS_LOGMNR.ADDFILE);

SQL> exec dbms_logmnr.add_logfile(‘/home/oracle/data01/oradata/IBM/redo03.log’,DBMS_LOGMNR.ADDFILE);

SQL> exec dbms_logmnr.add_logfile(‘/home/oracle/data01/oradata/IBM/redo04.log’,DBMS_LOGMNR.ADDFILE);

SQL> exec dbms_logmnr.start_logmnr(‘/oracle/app/oracle/product/8.1.7/LOG/v817dict.ora’);

e. 트랜잭션 수행

SQL> descc scott.dept

SQL> select * from scott.dept;

SQL> insert into scott.dept values(99,’test’,’test’);

SQL> update scott.dept set loc=’TEST’ where deptno=99;

SQL> commit;

f. log miner 정보 분석

SQL> select timestamp,username,sql_redo from v$logmnr_contents

2  where seg_name=’DEPT’;

g. 로그마이닝 종료

SQL> exec dbms_logmnr.end_logmnr;

================================================================================================  

8. Managing TableSpace and Data Files

================================================================================================  

* tablespace와 datafile 조회

SQL> col tablespace_name format a15

SQL> col file_name format a45

SQL> select tablespace_name,status,contents from dba_tablespaces;

TABLESPACE_NAME STATUS             CONTENTS

————— —————— ——————

SYSTEM          ONLINE             PERMANENT

TOOLS           ONLINE             PERMANENT

RBS             ONLINE             PERMANENT

TEMP            ONLINE             TEMPORARY

USERS           ONLINE             PERMANENT

INDX            ONLINE             PERMANENT

DRSYS           ONLINE             PERMANENT

SQL> select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME      BYTES FILE_NAME

————— ———- ———————————————

TOOLS             10485760 /home/oracle/data01/oradata/IBM/tools01.dbf

DRSYS             20971520 /home/oracle/data01/oradata/IBM/drsys01.dbf

USERS             20971520 /home/oracle/data01/oradata/IBM/users01.dbf

INDX              20971520 /home/oracle/data01/oradata/IBM/indx01.dbf

RBS               52428800 /home/oracle/data01/oradata/IBM/rbs01.dbf

TEMP              20971520 /home/oracle/data01/oradata/IBM/temp01.dbf

SYSTEM           283115520 /home/oracle/data01/oradata/IBM/system01.dbf

================================================================================================  

* tablespace 생성 및 사이즈 변경

SQL> create tablespace data05

2  datafile ‘/home/oracle/data01/oradata/IBM/data05_01.dbf’ size 1m;

Tablespace created.

1m 짜리 datafile 하나를 가진 tablespace data05를 추가하였다. 확인.

SQL> select tablespace_name,bytes,file_name from dba_data_files

2  where tablespace_name=’DATA05′;

TABLESPACE_NAME      BYTES FILE_NAME

————— ———- ———————————————

DATA05             1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf

tablespace가 부족할때 늘리는 방법은 두가지가 있다.  

하나는 datafile을 추가하는 방법이고 다른하나는 datafile의 size를 늘리는 방법이다.

a. datafile을 하나 추가해보자.

SQL> alter tablespace data05

2  add datafile ‘/home/oracle/data01/oradata/IBM/data05_02.dbf’ size 1m;

SQL> select tablespace_name,bytes,file_name from dba_data_files

2  where tablespace_name=’DATA05′;

TABLESPACE_NAME      BYTES FILE_NAME

————— ———- ———————————————

DATA05             1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf

DATA05             1048576 /home/oracle/data01/oradata/IBM/data05_02.dbf

제대로 추가되었다.

b. 그렇다면 하나의 사이즈를 변경해보자.

SQL> alter database datafile

2  ‘/home/oracle/data01/oradata/IBM/data05_02.dbf’ resize 2m;

SQL> select tablespace_name,bytes,file_name from dba_data_files

2  where tablespace_name=’DATA05′;

TABLESPACE_NAME      BYTES FILE_NAME

————— ———- ———————————————

DATA05             1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf

DATA05             2097152 /home/oracle/data01/oradata/IBM/data05_02.dbf

2m로 제대로 변경이 되었다.

다시 원상복구

SQL> alter database datafile

2  ‘/home/oracle/data01/oradata/IBM/data05_02.dbf’ resize 1m;

전체를 다시 확인해보자

SQL> select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME      BYTES FILE_NAME

————— ———- ———————————————

TOOLS             10485760 /home/oracle/data01/oradata/IBM/tools01.dbf

DRSYS             20971520 /home/oracle/data01/oradata/IBM/drsys01.dbf

USERS             20971520 /home/oracle/data01/oradata/IBM/users01.dbf

INDX              20971520 /home/oracle/data01/oradata/IBM/indx01.dbf

RBS               52428800 /home/oracle/data01/oradata/IBM/rbs01.dbf

TEMP              20971520 /home/oracle/data01/oradata/IBM/temp01.dbf

SYSTEM           283115520 /home/oracle/data01/oradata/IBM/system01.dbf

DATA05             1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf

DATA05             1048576 /home/oracle/data01/oradata/IBM/data05_02.dbf

================================================================================================  

* tablespace 삭제 : Dictionary에서만 삭제되는것으로 실제 물리적으로 파일은 os command로 삭제해야한다.

SQL> select tablespace_name from dba_tablespaces

2  where tablespace_name like ‘DATA%’

3  minus

4  select distinct tablespace_name from dba_segments;

TABLESPACE_NAME

—————

DATA05

SQL> drop tablespace data05;

SQL> select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME      BYTES FILE_NAME

————— ———- ———————————————

TOOLS             10485760 /home/oracle/data01/oradata/IBM/tools01.dbf

DRSYS             20971520 /home/oracle/data01/oradata/IBM/drsys01.dbf

USERS             20971520 /home/oracle/data01/oradata/IBM/users01.dbf

INDX              20971520 /home/oracle/data01/oradata/IBM/indx01.dbf

RBS               52428800 /home/oracle/data01/oradata/IBM/rbs01.dbf

TEMP              20971520 /home/oracle/data01/oradata/IBM/temp01.dbf

SYSTEM           283115520 /home/oracle/data01/oradata/IBM/system01.dbf

SQL> !ls //home/oracle/data01/oradata/IBM/*.dbf

//home/oracle/data01/oradata/IBM/data05_01.dbf  //home/oracle/data01/oradata/IBM/system01.dbf

//home/oracle/data01/oradata/IBM/data05_02.dbf  //home/oracle/data01/oradata/IBM/temp01.dbf

//home/oracle/data01/oradata/IBM/drsys01.dbf    //home/oracle/data01/oradata/IBM/tools01.dbf

//home/oracle/data01/oradata/IBM/indx01.dbf     //home/oracle/data01/oradata/IBM/users01.dbf

//home/oracle/data01/oradata/IBM/rbs01.dbf

dictionary에서는 삭제되었으나 여전히 물리적인 file은 존재한다. 삭제하면 된다.

(tablespace생성시에는 file이 그냥 생성되나 삭제시는 dictionary삭제후 강제로 삭제해줘야 한다.)

SQL> !rm /home/oracle/data01/oradata/IBM/data05*

================================================================================================  

* tablespace 의 online/offline, read only/read write

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                                              STATUS

———————————————————— ——————

SYSTEM                                                       ONLINE

TOOLS                                                        ONLINE

RBS                                                          ONLINE

TEMP                                                         ONLINE

USERS                                                        ONLINE

INDX                                                         ONLINE

DRSYS                                                        ONLINE

7 rows selected.

SQL> select tablespace_name from dba_tables

2  where table_name =’DEPT’ and owner=’SCOTT’;

TABLESPACE_NAME

————————————————————

SYSTEM

default로 생성시 scott user의 data가 system tablespace에 생성되었으나 이렇게 쓰면 안된다.

하나 생성해볼까?

SQL> create tablespace data01

2  datafile ‘/home/oracle/data01/oradata/IBM/data01.dbf’ size 1m;

Tablespace created.

SQL> connect scott/tiger

Connected.

SQL> create table dept_tmp tablespace data01

2  as select * from dept;

SQL> connect internal

Connected.

SQL> select tablespace_name from dba_tables

2  where table_name =’DEPT_TMP’ and owner=’SCOTT’;

TABLESPACE_NAME

————————————————————

DATA01

SQL> select * from scott.dept_tmp;

   DEPTNO DNAME                        LOC

———- —————————- ————————–

       10 ACCOUNTING                   NEW YORK

       20 RESEARCH                     DALLAS

       30 SALES                        CHICAGO

       40 OPERATIONS                   BOSTON

      

제대로 된다. 그렇다면 tablespace를 offline으로…

SQL> alter tablespace data01 offline;

SQL> select tablespace_name, status from dba_tablespaces

2  where tablespace_name=’DATA01′;

TABLESPACE_NAME                                              STATUS

———————————————————— ——————

DATA01                                                       OFFLINE

SQL> select * from scott.dept_tmp;

select * from scott.dept_tmp

                   *

ERROR at line 1:

ORA-00376: file 8 cannot be read at this time

ORA-01110: data file 8: ‘/home/oracle/data01/oradata/IBM/data01.dbf’

위와 같이 error가 발생한다.

다시 online으로 해두자.

SQL> alter tablespace data01 online;

이번엔 read only로 변경

SQL> alter tablespace data01 read only;

SQL> select tablespace_name, status from dba_tablespaces

2  where tablespace_name=’DATA01′;

TABLESPACE_NAME                                              STATUS

———————————————————— ——————

DATA01                                                       READ ONLY

변경되었다.

SQL> insert into scott.dept_tmp values(80,’new_dept’,’new_loc’);

insert into scott.dept_tmp values(80,’new_dept’,’new_loc’)

                 *

ERROR at line 1:

ORA-00372: file 8 cannot be modified at this time

ORA-01110: data file 8: ‘/home/oracle/data01/oradata/IBM/data01.dbf’

insert같은 DML(write성) 수행시 위와 같은 error 발생

원상복구

SQL> alter tablespace data01 read write;

SQL> insert into scott.dept_tmp values(80,’test’,’test’);

제대로 된다.

================================================================================================  

9. Storage Structure and Relationships

================================================================================================  

* Extent 정보 조회 : 다음과 같이 각종 extent,segment 등의 정보를 조회해 볼 수 있다.

SQL> col owner format a10

SQL> col segment_type format a12

SQL> col segment_name format a12

SQL> col tablespace_name format a10

SQL> select owner,segment_name,segment_type, tablespace_name,max_extents,extents,pct_increase

2  from dba_segments

3  where max_extents – extents <= 10 and owner !=’SYS’;

no rows selected

SQL> select owner,segment_name,segment_type, tablespace_name,max_extents,extents,pct_increase

2  from dba_segments

3  where owner=’SCOTT’;

OWNER      SEGMENT_NAME SEGMENT_TYPE TABLESPACE MAX_EXTENTS    EXTENTS PCT_INCREASE

———- ———— ———— ———- ———– ———- ————

SCOTT      DEPT_TMP     TABLE        DATA01             505          1           50

SCOTT      DEPT         TABLE        SYSTEM      2147483645          1           50

SCOTT      EMP          TABLE        SYSTEM      2147483645          1           50

SCOTT      BONUS        TABLE        SYSTEM      2147483645          1           50

SCOTT      SALGRADE     TABLE        SYSTEM      2147483645          1           50

SCOTT      PK_DEPT      INDEX        SYSTEM      2147483645          1           50

SCOTT      PK_EMP       INDEX        SYSTEM      2147483645          1           50

SQL> select segment_name,extents, initial_extent, next_extent,pct_increase

2  from dba_segments

3  where owner=’SCOTT’ and segment_name=’EMP’;

SEGMENT_NAME    EXTENTS INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE

———— ———- ————– ———– ————

EMP                   1          65536       65536           50

SQL> select segment_name,extent_id,block_id,bytes,blocks

2  from dba_extents

3  where owner=’SCOTT’ and segment_name=’EMP’;

4  order by 2,3;

SEGMENT_NAME  EXTENT_ID   BLOCK_ID      BYTES     BLOCKS

———— ———- ———- ———- ———-

EMP                   0      33945      65536          8

================================================================================================  

* Free space 관리

tablespace내에 free space를 먼저 확인해본다.

SQL> select * from dba_free_space

2  where tablespace_name =’DATA01′ order by 1,2,3;

TABLESPACE    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO

———- ———- ———- ———- ———- ————

DATA01              8          7     999424        122            8

테이블을 여러개 생성해보자.

SQL> create table scott.dept2 tablespace data01 as select * from scott.dept;

SQL> create table scott.dept3 tablespace data01 as select * from scott.dept;

SQL> create table scott.dept4 tablespace data01 as select * from scott.dept;

SQL> create table scott.dept5 tablespace data01 as select * from scott.dept;

SQL> create table scott.dept6 tablespace data01 as select * from scott.dept;

SQL> select * from dba_free_space

2  where tablespace_name =’DATA01′ order by 1,2,3;

TABLESPACE    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO

———- ———- ———- ———- ———- ————

DATA01              8         32     794624         97            8

사용함에 따라 tablespace내 free space 가 줄어듦을 알 수 있다.

SQL> drop table scott.dept2;

drop table dept2

          *

ERROR at line 1:

ORA-04098: trigger ‘SYS.JIS$ROLE_TRIGGER$’ is invalid and failed re-validation

이건 또 뭐야 ? trigger가 걸려있네요…  

table drop 을 위해

SQL> alter trigger SYS.JIS$ROLE_TRIGGER$ disable;

drop table scott.dept3; ==> dept4 만 빼고 전부 drop

drop table scott.dept5;

drop table scott.dept6;

SQL> select * from dba_free_space

2  where tablespace_name =’DATA01′ order by 1,2,3;

TABLESPACE    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO

———- ———- ———- ———- ———- ————

DATA01              8          7      40960          5            8

DATA01              8         32     794624         97            8

tablespace의 free space가 늘긴 했는데 쪼개졌네요..

빈공간을 병합하자

SQL> alter tablespace data01 coalesce;

SQL> select * from dba_free_space

2  where tablespace_name =’DATA01′ order by 1,2,3;

TABLESPACE    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO

———- ———- ———- ———- ———- ————

DATA01              8          7      40960          5            8

DATA01              8         32     794624         97            8

그래도 두개로 쪼개져 있는 이유는? 중간에 dept4 가 사용하는 space가 coalesce 되지 않았기 때문

SQL> drop table scott.dept4;

SQL> alter tablespace data01 coalesce;

완전히 병합되었다.

================================================================================================  

10. Managing Rollback Segments

================================================================================================  

* rollback segment의 정보 조회

SQL> col owner format a10

SQL> col segment_name format a12

SQL> col segment_type format a12

SQL> col tablespace_name format a10

SQL> col status format a7

SQL> select segment_name,tablespace_name,status,initial_extent,next_extent,min_extents

2  from dba_rollback_segs;

SEGMENT_NAME TABLESPACE STATUS  INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS

———— ———- ——- ————– ———– ———–

SYSTEM       SYSTEM     ONLINE           57344       57344           2

RBS0         RBS        ONLINE          524288      524288           8

RBS1         RBS        ONLINE          524288      524288           8

RBS2         RBS        ONLINE          524288      524288           8

RBS3         RBS        ONLINE          524288      524288           8

RBS4         RBS        ONLINE          524288      524288           8

RBS5         RBS        ONLINE          524288      524288           8

RBS6         RBS        ONLINE          524288      524288           8

================================================================================================  

* rollback segment 생성

SQL> create rollback segment rbs99

2  tablespace rbs

3  storage(initial 20k next 20k minextents 2 optimal 80k);

Rollback segment created.

SQL> select segment_name,tablespace_name,status,initial_extent,next_extent,min_extents

2  from dba_rollback_segs;

SEGMENT_NAME TABLESPACE STATUS  INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS

———— ———- ——- ————– ———– ———–

SYSTEM       SYSTEM     ONLINE           57344       57344           2

RBS0         RBS        ONLINE          524288      524288           8

RBS1         RBS        ONLINE          524288      524288           8

RBS2         RBS        ONLINE          524288      524288           8

RBS3         RBS        ONLINE          524288      524288           8

RBS4         RBS        ONLINE          524288      524288           8

RBS5         RBS        ONLINE          524288      524288           8

RBS6         RBS        ONLINE          524288      524288           8

RBS99        RBS        OFFLINE          24576       32768           2

추가되었다. online으로 전환하자.

SQL> alter rollback segment rbs99 online;

SQL> create table emp2 as select * from emp;

SQL> select name,extents,xacts,shrinks,optsize

2  from v$rollname n, v$rollstat s

3  where n.usn = s.usn;

NAME                      EXTENTS      XACTS    SHRINKS    OPTSIZE

———————  ———– ———- ———- ———-

SYSTEM                          9          0          0

RBS0                            8          0          0    4194304

RBS1                            8          0          0    4194304

RBS2                            8          0          0    4194304

RBS3                            8          0          0    4194304

RBS4                            8          0          0    4194304

RBS5                            8          0          0    4194304

RBS6                            8          0          0    4194304

RBS99                           2          0          0      81920   ==> extents,xacts의 변화 관찰

SQL> set transaction use rollback segment rbs99;

SQL> update emp2 set hiredate=sysdate;

SQL> select name,extents,xacts,shrinks,optsize

2  from v$rollname n, v$rollstat s

3  where n.usn = s.usn;

NAME               EXTENTS      XACTS    SHRINKS    OPTSIZE

————— ———- ———- ———- ———-

SYSTEM                   9          0          0

RBS0                     8          0          0    4194304

RBS1                     8          0          0    4194304

RBS2                     8          0          0    4194304

RBS3                     8          0          0    4194304

RBS4                     8          0          0    4194304

RBS5                     8          0          0    4194304

RBS6                     8          0          0    4194304

RBS99                    2          1          0      81920 ==> transaction이 시작됨

SQL> update emp2 set hiredate=sysdate-1;  

sql> insert into emp2 select * from emp2; ==> 엄청 많이 수행 하자.

SQL> select name,extents,xacts,shrinks,optsize

2  from v$rollname n, v$rollstat s

3  where n.usn = s.usn;

NAME               EXTENTS      XACTS    SHRINKS    OPTSIZE

————— ———- ———- ———- ———-

SYSTEM                   9          0          0

RBS0                     8          0          0    4194304

RBS1                     8          0          0    4194304

RBS2                     8          0          0    4194304

RBS3                     8          0          0    4194304

RBS4                     8          0          0    4194304

RBS5                     8          0          0    4194304

RBS6                     8          0          0    4194304

RBS99                    3          1          0      81920 ==> extents 증가

SQL> rollback;

SQL> set transaction use rollback segment rbs99;

SQL> update emp2 set sal=1000;

SQL> select name,extents,xacts,shrinks,optsize

2  from v$rollname n, v$rollstat s

3  where n.usn = s.usn;

NAME               EXTENTS      XACTS    SHRINKS    OPTSIZE

————— ———- ———- ———- ———-

SYSTEM                   9          0          0

RBS0                     8          0          0    4194304

RBS1                     8          0          0    4194304

RBS2                     8          0          0    4194304

RBS3                     8          0          0    4194304

RBS4                     8          0          0    4194304

RBS5                     8          0          0    4194304

RBS6                     8          0          0    4194304

RBS99                    3          1          0      81920   ==> automatic 하게 shrink되었는지 확인

이전 tranx은 종료되었고 새로운 tranx가 시작됨

================================================================================================  

* rollback segment 삭제

SQL> rollback;

SQL> select name,extents,xacts,shrinks,optsize

2  from v$rollname n, v$rollstat s

3  where n.usn = s.usn; ==> xacts 가 ‘0’ 인지 먼저 확인

SQL> alter rollback segment rbs99 offline;

SQL> drop rollback segment rbs99;

SQL> select segment_name,tablespace_name,status,initial_extent,next_extent,min_extents

2  from dba_rollback_segs;

SEGMENT_NAME TABLESPACE STATUS  INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS

———— ———- ——- ————– ———– ———–

SYSTEM       SYSTEM     ONLINE           57344       57344           2

RBS0         RBS        ONLINE          524288      524288           8

RBS1         RBS        ONLINE          524288      524288           8

RBS2         RBS        ONLINE          524288      524288           8

RBS3         RBS        ONLINE          524288      524288           8

RBS4         RBS        ONLINE          524288      524288           8

RBS5         RBS        ONLINE          524288      524288           8

RBS6         RBS        ONLINE          524288      524288           8

확인해보니 삭제되었다.

================================================================================================  

11.Managing Tables

================================================================================================  

* Temporary Table

a. on commit perserve rows : session내에서 생성한 temp table에 대해서 지속적.

  새로운 session 연결 되면 data 지워짐

Example : Creating a Session-Specific Temporary Table

CREATE GLOBAL TEMPORARY TABLE …

    [ON COMMIT PRESERVE ROWS ]

b. on commit delete rows : tansaction이 끝나면 temp table 내의 data가 지워짐(commit,rollback등)

Example : Creating a Transaction-Specific Temporary Table

CREATE GLOBAL TEMPORARY TABLE …

    [ON COMMIT DELETE ROWS ]

================================================================================================  

* Using Temporary Tables to Improve Performance

You can use temporary tables to improve performance when you run complex queries.  

Running multiple such queries is relatively slow because the tables are accessed multiple times  

for each returned row. It is faster to cache the values from a complex query in a temporary table,  

then run the queries against the temporary table.  

For example, even with a view like this defined to simplify further queries,  

the queries against the view may be slow because the contents of the view are recalculated each time:  

CREATE OR REPLACE VIEW Profile_values_view AS

SELECT d.Profile_option_name, d.Profile_option_id, Profile_option_value,

      u.User_name, Level_id, Level_code

FROM Profile_definitions d, Profile_values v, Profile_users u

WHERE d.Profile_option_id = v.Profile_option_id

  AND ((Level_code = ‘USER’ AND Level_id = U.User_id) OR

       (Level_code = ‘DEPARTMENT’ AND Level_id = U.Department_id) OR

       (Level_code = ‘SITE’))

  AND NOT EXISTS (SELECT 1 FROM PROFILE_VALUES P

                   WHERE P.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID

                     AND ((Level_code = ‘USER’ AND

                           level_id = u.User_id) OR

                          (Level_code = ‘DEPARTMENT’ AND

                           level_id = u.Department_id) OR

                          (Level_code = ‘SITE’))

                     AND INSTR(‘USERDEPARTMENTSITE’, v.Level_code) >

                         INSTR(‘USERDEPARTMENTSITE’, p.Level_code));

A temporary table allows us to run the computation once,  

and cache the result in later SQL queries and joins:  

CREATE GLOBAL TEMPORARY TABLE Profile_values_temp

        (

            Profile_option_name   VARCHAR(60)   NOT NULL,

            Profile_option_id     NUMBER(4)     NOT NULL,

            Profile_option_value  VARCHAR2(20)  NOT NULL,

            Level_code            VARCHAR2(10)          ,

            Level_id              NUMBER(4)             ,

            CONSTRAINT Profile_values_temp_pk

               PRIMARY KEY (Profile_option_id)

        ) ON COMMIT PRESERVE ROWS ORGANIZATION INDEX;

INSERT INTO Profile_values_temp

      (Profile_option_name, Profile_option_id, Profile_option_value,

       Level_code, Level_id)

SELECT Profile_option_name, Profile_option_id, Profile_option_value,

       Level_code, Level_id

FROM Profile_values_view;

COMMIT;

Now the temporary table can be used to speed up queries,  

and the results cached in the temporary table are freed automatically by the database  

when the session ends.  

================================================================================================  

* Row Migration Test

scott/tiger로 접속해서

SQL> create table chain_test(col1 varchar2(100));

Table created.

SQL> insert into chain_test values(‘a’);

1 row created.

SQL> insert into chain_test select * from chain_test;   <====== 1 row created.

SQL> / <====== 2 rows created.

SQL> / <====== 4 rows created.

SQL> / <====== 8 rows created.

SQL> / <====== 16 rows created.

SQL> / <====== 32 rows created.

SQL> / <====== 64 rows created.

SQL> / <====== 128 rows created.

SQL> / <====== 256 rows created.

SQL> / <====== 512 rows created.

SQL> commit;

SQL> @$ORACLE_HOME/rdbms/admin/utlchain                 <====== chanined_rows table생성

Table created.

SQL> desc chained_rows

Name                                      Null?    Type

—————————————– ——– —————————-

OWNER_NAME                                         VARCHAR2(30)

TABLE_NAME                                         VARCHAR2(30)

CLUSTER_NAME                                       VARCHAR2(30)

PARTITION_NAME                                     VARCHAR2(30)

SUBPARTITION_NAME                                  VARCHAR2(30)

HEAD_ROWID                                         ROWID

ANALYZE_TIMESTAMP                                  DATE

SQL> analyze table chain_test list chained rows;

Table analyzed.

SQL> select count(*) from chained_rows;

COUNT(*)

———-

        0                             ======> 아직까지는 chaining이 하나도 없지…

        

SQL> update chain_test

2  set col1 = ‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa’;

1024 rows updated.

SQL> analyze table chain_test list chained rows;

Table analyzed.

SQL> select count(*) from chained_rows;

COUNT(*)

———-

      995                       ==============> row migration이 다량 발생

row migration이 일어나면 여러 block에서 읽어야 하므로 그만큼 performance가 떨어진다.

이를 해결하기 위해 주기적으로 analyze하여 확인해보고 insert를 다시 해주면 된다.

chaining이 일어난 row들의 rowid로 찾아서 임시table을 생성하고 원래 table에서 chaining이 일어난

data를 삭제하고 다시 insert하면 된다.

SQL> create table chain_tmp as select * from chain_test

2  where rowid in (select head_rowid from chained_rows where table_name=’CHAIN_TEST’);

SQL> delete from chain_test

2  where rowid in(select head_rowid from chained_rows where table_name=’CHAIN_TEST’);

SQL> insert into chain_test select *  from chain_tmp;

SQL> commit;

다시 cahined_rows table 을 삭제하고 analyze해보자.

SQL> truncate table chained_rows;

Table truncated.

SQL> analyze table chain_test list chained rows;

Table analyzed.

SQL> select * from chained_rows; <====== no rows selected

chaining이 없어졌다.

================================================================================================  

12. Managing Indexes

================================================================================================  

1. B*Tree Index 생성, 확인

SQL> col table_name format a10

SQL> col index_name format a20

SQL> col index_type format a10

SQL> col column_name format a12

SQL> create index scott.dept_dname_ind on scott.dept(dname);

Index created.

SQL> create unique index scott.dept_deptno_uind on scott.dept(deptno);

create unique index scott.dept_deptno_uind on scott.dept(deptno)

                                                        *

ERROR at line 1:

ORA-01408: such column list already indexed

error가 난 이유는 column이 pk로 지정될때는 unique index가 자동으로 생성되기 때문.

user_constraints, user_cons_columns 등에서 확인해보면 알수있다.

SQL> select table_name,index_name,index_type,uniqueness

2  from dba_indexes

3  where owner=’SCOTT’;

TABLE_NAME INDEX_NAME           INDEX_TYPE UNIQUENESS

———- ——————– ———- ——————

DEPT       DEPT_DNAME_IND       NORMAL     NONUNIQUE ====> 생성한 index

AUDIT_ACTI I_AUDIT_ACTIONS      NORMAL     UNIQUE

ONS

DEPT       PK_DEPT              NORMAL     UNIQUE

EMP        PK_EMP               NORMAL     UNIQUE

DBMS_LOCK_ SYS_C001456          NORMAL     UNIQUE

ALLOCATED

DBMS_ALERT SYS_C001457          NORMAL     UNIQUE

_INFO

SQL> select table_name,index_name,column_position,column_name

2  from dba_ind_columns

3  where index_owner=’SCOTT’;

TABLE_NAME INDEX_NAME           COLUMN_POSITION COLUMN_NAME

———- ——————– ————— ————

DEPT       DEPT_DNAME_IND                     1 DNAME               ====> index가 걸린 column

AUDIT_ACTI I_AUDIT_ACTIONS                    1 ACTION

ONS

AUDIT_ACTI I_AUDIT_ACTIONS                    2 NAME

ONS

DEPT       PK_DEPT                            1 DEPTNO

EMP        PK_EMP                             1 EMPNO

DBMS_LOCK_ SYS_C001456                        1 NAME

ALLOCATED

DBMS_ALERT SYS_C001457                        1 NAME

_INFO

DBMS_ALERT SYS_C001457                        2 SID

_INFO

================================================================================================  

* Bitmap Index 생성, 확인

SQL> select count(*) from scott.emp;

SQL> select distinct job from scott.emp;

JOB

——————

ANALYST

CLERK

MANAGER

PRESIDENT

SALESMAN

SQL> create bitmap index scott.emp_job_bind on scott.emp(job);

SQL> select table_name,index_name,index_type,uniqueness

2  from dba_indexes

3  where owner=’SCOTT’;

TABLE_NAME INDEX_NAME           INDEX_TYPE UNIQUENESS

———- ——————– ———- ——————

DEPT       DEPT_DNAME_IND       NORMAL     NONUNIQUE

EMP        EMP_JOB_BIND         BITMAP     NONUNIQUE =====>

AUDIT_ACTI I_AUDIT_ACTIONS      NORMAL     UNIQUE

SQL> select table_name,index_name,column_position,column_name

2  from dba_ind_columns

3  where index_owner=’SCOTT’;

TABLE_NAME INDEX_NAME           COLUMN_POSITION COLUMN_NAME

———- ——————– ————— ————

DEPT       DEPT_DNAME_IND                     1 DNAME

EMP        EMP_JOB_BIND                       1 JOB =====>

AUDIT_ACTI I_AUDIT_ACTIONS                    1 ACTION

ONS

================================================================================================  

* Reverse Key Index 생성, 확인

SQL> create index scott.emp_hiredate_rind on scott.emp(hiredate) reverse;

SQL> select table_name,index_name,index_type,uniqueness

2  from dba_indexes

3  where owner=’SCOTT’;

TABLE_NAME INDEX_NAME           INDEX_TYPE UNIQUENESS

———- ——————– ———- ——————

DEPT       DEPT_DNAME_IND       NORMAL     NONUNIQUE

EMP        EMP_HIREDATE_RIND    NORMAL/REV NONUNIQUE ==>

EMP        EMP_JOB_BIND         BITMAP     NONUNIQUE

….

SQL> select table_name,index_name,column_position,column_name

2  from dba_ind_columns

3  where index_owner=’SCOTT’;

TABLE_NAME INDEX_NAME           COLUMN_POSITION COLUMN_NAME

———- ——————– ————— ————

DEPT       DEPT_DNAME_IND                     1 DNAME

EMP        EMP_HIREDATE_RIND                  1 HIREDATE ==>

EMP        EMP_JOB_BIND                       1 JOB

….

================================================================================================  

* Funtion-Based Index 생성, 확인 <===== Query Rewrite권한 필요

SQL> create index scott.emp_sal_find on scott.emp(sal * 1.1);

SQL> select table_name,index_name,index_type,uniqueness

2  from dba_indexes

3  where owner=’SCOTT’;

TABLE_NAME INDEX_NAME           INDEX_TYPE UNIQUENESS

———- ——————– ———- ——————

DEPT       DEPT_DNAME_IND       NORMAL     NONUNIQUE

EMP        EMP_HIREDATE_RIND    NORMAL/REV NONUNIQUE

EMP        EMP_JOB_BIND         BITMAP     NONUNIQUE

EMP        EMP_SAL_FIND         FUNCTION-B NONUNIQUE ==>

                               ASED NORMA

                               L

….

SQL> select table_name,index_name,column_position,column_name

2  from dba_ind_columns

3  where index_owner=’SCOTT’;

TABLE_NAME INDEX_NAME           COLUMN_POSITION COLUMN_NAME

———- ——————– ————— ————

DEPT       DEPT_DNAME_IND                     1 DNAME

EMP        EMP_HIREDATE_RIND                  1 HIREDATE

EMP        EMP_JOB_BIND                       1 JOB

EMP        EMP_SAL_FIND                       1 SYS_NC00009$   ====> column name이 내부적으로 바뀐다.

….

================================================================================================  

* Index drop

SQL> drop index scott.dept_dname_ind;

SQL> drop index scott.emp_hiredate_rind;

SQL> drop index scott.emp_job_bind;

SQL> drop index scott.emp_sal_find;

================================================================================================  

13.Maintaining Data Integrity

================================================================================================  

* PK/UK 와 Unique Index

SQL> desc scott.dept

Name                                      Null?    Type

—————————————– ——– —————————-

DEPTNO                                    NOT NULL NUMBER(2)

DNAME                                              VARCHAR2(14)

LOC                                                VARCHAR2(13)

PK생성은 다음과 같이 할 수 있다.

SQL> alter table scott.dept

2  add constraint dept_deptno_pk primary key(deptno);

add constraint dept_deptno_pk primary key(deptno)

                             *

ERROR at line 2:

ORA-02260: table can have only one primary key

이미 pk가 설정이 되어있어서 한테이블에 두개의 pk를 설정할 수 없다는 error.

SQL> alter table scott.dept

2  add constraint dept_dname_uk unique (dname);

Table altered.

SQL> select table_name,constraint_name,constraint_type, status

2  from dba_constraints

3  where owner=’SCOTT’;

TABLE_NAME           CONSTRAINT_NAME CO STATUS

——————– ————— — —————-

DEPT                 DEPT_DNAME_UK   U  ENABLED <=== 새로 생성한 UK

EMP                  FK_DEPTNO       R  ENABLED

DEPT                 PK_DEPT         P  ENABLED

EMP                  PK_EMP          P  ENABLED

AUDIT_ACTIONS        SYS_C001454     C  ENABLED

AUDIT_ACTIONS        SYS_C001455     C  ENABLED

DBMS_LOCK_ALLOCATED  SYS_C001456     P  ENABLED

DBMS_ALERT_INFO      SYS_C001457     P  ENABLED

SQL> select table_name,index_name,index_type,uniqueness

2  from dba_indexes

3  where owner=’SCOTT’;

TABLE_NAME INDEX_NAME           INDEX_TYPE UNIQUENESS

———- ——————– ———- ——————

DEPT       DEPT_DNAME_UK        NORMAL     UNIQUE <==

AUDIT_ACTI I_AUDIT_ACTIONS      NORMAL     UNIQUE

ONS

SQL> select table_name,index_name,column_position,column_name

2  from dba_ind_columns

3  where index_owner=’SCOTT’;

TABLE_NAME INDEX_NAME           COLUMN_POSITION COLUMN_NAME

———- ——————– ————— ————

DEPT       DEPT_DNAME_UK                      1 DNAME  <==  

AUDIT_ACTI I_AUDIT_ACTIONS                    1 ACTION

ONS

================================================================================================  

* Constraint Check

SQL> insert into scott.dept values(50,’HR’,’SEOUL’);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into scott.dept values(50,’HR Dept’,’SEOUL’);

insert into scott.dept values(50,’HR Dept’,’SEOUL’)

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

UK 값에 같은 값 insert하려다가 error가 난다.

================================================================================================  

* Constriaint 비활성화/활성화

SQL> alter table scott.dept

2  disable constraint dept_dname_uk;

SQL> alter table scott.dept

2  enable constraint dept_dname_uk;

================================================================================================  

* Deferred Constraint(?) ==> 자료좀 찾아보자..

================================================================================================  

* Constraint 삭제

alter table <테이블명> drop constraint

================================================================================================  

14. Loading Data

================================================================================================  

* 사용법

Usage: SQLLOAD keyword=value [,keyword=value,…]

Valid Keywords:

   userid — ORACLE username/password

  control — Control file name

      log — Log file name

      bad — Bad file name

     data — Data file name

  discard — Discard file name

discardmax — Number of discards to allow          (Default all)

     skip — Number of logical records to skip    (Default 0)

     load — Number of logical records to load    (Default all)

   errors — Number of errors to allow            (Default 50)

     rows — Number of rows in conventional path bind array or between direct path data saves

              (Default: Conventional path 64, Direct path all)

bindsize — Size of conventional path bind array in bytes  (Default 65536)

   silent — Suppress messages during run (header,feedback,errors,discards,partitions)

   direct — use direct path                      (Default FALSE)

  parfile — parameter file: name of file that contains parameter specifications

parallel — do parallel load                     (Default FALSE)

     file — File to allocate extents from

skip_unusable_indexes — disallow/allow unusable indexes or index partitions  (Default FALSE)

skip_index_maintenance — do not maintain indexes, mark affected indexes as unusable  (Default FALSE)

commit_discontinued — commit loaded rows when load is discontinued  (Default FALSE)

readsize — Size of Read buffer                  (Default 1048576)

PLEASE NOTE: Command-line parameters may be specified either by

position or by keywords.  An example of the former case is ‘sqlload

scott/tiger foo’; an example of the latter is ‘sqlload control=foo

userid=scott/tiger’.  One may specify parameters by position before

but not after parameters specified by keywords.  For example,

‘sqlload scott/tiger control=foo logfile=log’ is allowed, but

‘sqlload scott/tiger control=foo log’ is not, even though the

position of the parameter ‘log’ is correct.

================================================================================================  

* case 별로 sqlldr을 사용하는법은  

http://technet.oracle.com/doc/server.815/a67792/ch04.htm#1364

에 자세히 나와있다. 그중 두가지 정도의 case만 기본적으로 다루어보자.

================================================================================================  

* case1 : data를 control file에 직접 입력하여 load하기

a. data 입력될 table 있어야함 (dept_tmp 라는 table을 생성하여 입력해보자)

SQL> create table dept_tmp as select * from dept;

Table created.

SQL> truncate table dept_tmp;

Table truncated.

b. control file을 구성한다.

load data

infile * ==> data 가 ctl file 끝에 있다는 의미

replace                   ==> 이게 없으면 빈테이블일때만 load된다.

into table dept_tmp

fileds terminated by ‘,’ optionally enclosed by ‘”‘ ==> field 구분자와 “가 들어가면 빼고 입력된다.

(deptno,dname,loc)

begindata

12,research,”saratoga”

10,”accounting”,cleveland

11,”art”,”salem”

13,finance,boston

c. 다음과 같이 sqlldr을 실행

oracle@swsvrctr:/home/oracle> sqlldr scott/tiger control=test.ctl log=test.log bad=test.bad

error가 생기면 log가 남고 loading 되지 않은 data만 bad file에 남는다.

================================================================================================  

* case 2 : Fixed-format records의 Loading

a. data 입력될 table 있어야함 (dept_tmp 라는 table을 생성하여 입력해보자)

SQL> create table emp_tmp as select * from emp;

Table created.

b. data file의 내용을 보고 그에 맞게 control file을 구성한다.

data file은 다음과 같다고 하자.

1111    joo     Manager         1111    19191.00        10

2222    hwang   salesman        2222    294974.50       20

3333    test    test            3333    4984.00         40

4444    kwon    engineer        4444    49.90           50

control file을 만들어보자.

load data

infile ‘/home/oracle/test.dat’

replace

into table emp_tmp

(empno position(01:04) integer external, ==> position을 일일이 맞추어 준다.

ename position(09:14) char,

job position(17:24) char,

mgr position(33:36) integer external,

sal position(41:49) decimal external,

comm position(51:54) decimal external,

deptno position(57:58) integer external)

주의 : data file의 data가 공백이 아닌 tab 으로 되어있으면 position에서 한칸으로 인식되니까 주의

c. 다음과 같이 sqlldr을 실행

home/oracle> sqlldr scott/tiger control=test.ctl data=test.dat log=test.log bad=test.bad

================================================================================================  

15. Reorganizing Data

================================================================================================  

* 오래 사용한 table 주로 DML성 문장이 자주 일어나 performance에 영향을 미치므로 주기적으로

Reorganize 를 해주는것이 좋다.

Export => table drop => import 순으로 한다.

export 는 user 별로 table별로 받을 수 있다.

a. Export

oracle@swsvrctr:/home/oracle> exp scott/tiger tables=’dept,emp’ file=test.dmp

Export: Release 8.1.6.0.0 – Production on Wed Jul 4 14:32:21 2001

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0, 64 bit – Production

With the Partitioning option

JServer Release 8.1.6.0.0 – Production

Export done in KO16KSC5601 character set and WE8ISO8859P1 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path …

. . exporting table                           DEPT          7 rows exported

. . exporting table                            EMP         14 rows exported

Export terminated successfully without warnings.

b. drop table

oracle@swsvrctr:/home/oracle> sqlplus scott/tiger

SQL> drop table emp;

SQL> drop table dept;

c. Import

oracle@swsvrctr:/home/oracle> imp scott/tiger tables=’dept,emp’ file=test.dmp

Import: Release 8.1.6.0.0 – Production on Wed Jul 4 14:34:25 2001

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0, 64 bit – Production

With the Partitioning option

JServer Release 8.1.6.0.0 – Production

Export file created by EXPORT:V08.01.06 via conventional path

import done in KO16KSC5601 character set and WE8ISO8859P1 NCHAR character set

import server uses WE8ISO8859P1 character set (possible charset conversion)

. importing SCOTT’s objects into SCOTT

. . importing table                         “DEPT”          7 rows imported

. . importing table                          “EMP”         14 rows imported

About to enable constraints…

Import terminated successfully without warnings.

d. 제대로 되었나 조회

SQL> select * from dept;

SQL> select * from emp;

================================================================================================  

16. Managing Password Security and Resources

================================================================================================  

* verify_function 생성을 위해 돌려줘야 할 script : $ORACLE_HOME/rdbms/admin/utlpwdmg.sql

  NAME

    utlpwdmg.sql – script for Default Password Resource Limits

  DESCRIPTION

    This is a script for enabling the password management features

    by setting the default password resource limits.

  NOTES

    This file contains a function for minimum checking of password

    complexity. This is more of a sample function that the customer

    can use to develop the function for actual complexity checks that the

    customer wants to make on the new password.

  MODIFIED   (MM/DD/YY)

  asurpur     04/17/97 – Fix for bug479763

  asurpur     12/12/96 – Changing the name of password_verify_function

  asurpur     05/30/96 – New script for default password management

  asurpur     05/30/96 – Created

================================================================================================  

* verify_function 생성 및 패스워드 관리기능 활성화

SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg

Function created.

Profile altered.

================================================================================================  

* 사용자 생성

utlpwdmg.sql script를 돌려 verify_function을 생성후 user를 생성할때는 몇가지를 check하여 좀더  

password 관리를 할 수 있도록 해준다.  

다음과 같이 user/passwd를 같게 하면 error가 나서 생성되지 않는다.

SQL> create user myuser identified by myuser

2  default tablespace TS_USER1

3  temporary tablespace TEMP;

create user myuser identified by myuser

*

ERROR at line 1:

ORA-28003: password verification for the specified password failed

ORA-20001: Password same as user

다시 시도

SQL> create user myuser identified by mypasswd9$

2  default tablespace ts_user1

3  temporary tablespace temp;

User created.

SQL> grant connect,resource to myuser;

확인

SQL> connect myuser/mypasswd9$

================================================================================================  

* 패스워드 관리/ expire 시키기

SQL> alter user myuser password expire;

User altered.

admin이 강제로 expire시켰기 때문에 다음과 같이 password변경을 뭍는다.

SQL> connect myuser/mypassword9$

Changing password for test

New password:

Retype new password:

Password changed

Connected.

password를 또 규칙에 맞지 않게 넣으면 다음과 같은 error가 발생한다.

ERROR:

ORA-00988: missing or invalid password(s)

================================================================================================  

* dictionary 조회

SQL> select resource_name,limit from dba_profiles  

2  where profile=’DEFAULT’ and resource_type=’PASSWORD’;

RESOURCE_NAME        LIMIT

——————– —————

FAILED_LOGIN_ATTEMPT 3

S

PASSWORD_LIFE_TIME   60

PASSWORD_REUSE_TIME  1800

PASSWORD_REUSE_MAX   UNLIMITED

PASSWORD_VERIFY_FUNC VERIFY_FUNCTION

TION

PASSWORD_LOCK_TIME   .0006

PASSWORD_GRACE_TIME  10

RESOURCE_NAME        LIMIT

——————– —————

FAILED_LOGIN_ATTEMPT 3

S

PASSWORD_LIFE_TIME   60

PASSWORD_REUSE_TIME  1800

PASSWORD_REUSE_MAX   UNLIMITED

PASSWORD_VERIFY_FUNC VERIFY_FUNCTION

TION

PASSWORD_LOCK_TIME   .0006

PASSWORD_GRACE_TIME  10

================================================================================================  

17. Managing Users

================================================================================================  

* OS 인증 : os에 login 한 id/passwd로 oracle의 id/passwd로 함께 사용하기 위한방법

a. user를 생성하는데 identified externally로 생성한다.

oracle@swsvrctr:/home/oracle> sqlplus internal

SQL> create user oracle identified externally

2  default tablespace ts_user1

3  temporary tablespace temp;

User created.

SQL> grant connect,resource to oracle;

SQL> revoke unlimited tablespace from oracle;

SQL> shutdown immediate

b. init.ora 에서 다음을 편집한다.(추가)

os_authent_prefix=””

c. startup하고 확인해본다.

SQL> startup

SQL> exit

oracle@swsvrctr:/home/oracle> sqlplus / ==> id/passwd를 넣을 필요없이 접속

SQL> select user from dual;

USER

————————————————————

ORACLE

제대로 접속이 된다.

================================================================================================  

* tablespace 사용량 통제

SQL> connect internal

Connected.

SQL> col tablespacename format a10

SQL> col username format a10

SQL> alter user oracle quota 20k on ts_user1;  ==> oracle user는 ts_user1에 20k 만 사용 가능하다.

User altered.

SQL> select * from dba_ts_quotas;

TABLESPACE_NAME                                              USERNAME

———————————————————— ———-

    BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS

———- ———- ———- ———-

TS_USER1                                                     ORACLE

        0      20480          0          5

SQL> connect /

Connected.

SQL> select user from dual;

USER

————————————————————

ORACLE

SQL> create table test

2  (id number(10))

3  (tablespace ts_user1

4  storage(initial 20k)

까지는 생성이 되나 다음과 같이 늘리려고 하면 error가 난다.

SQL> alter table test allocate extent(size 4k); ==> error

================================================================================================  

18. Managing Privileges

================================================================================================  

* with grant option과 with admin option  

(둘다 실행 권한을 받은 user가 다시 실행 권한을 다른 user에게 줄 수 있게 해주는 option이다.)

— 차이는 with admin option으로 권한을 받은 user1이 다른 user2에게 권한을 부여한 후 user1으로부터

  권한을 revoke하면 user1의 권한만 revoke되나

  with grant option으로 부여하면 user1에게 revoke 될 시 user2의 권한도 cascade로 revoke된다.

oracle@swsvrctr:/home/oracle> sqlplus internal

SQL> col grantor format a10

SQL> col grantee format a10

SQL> col table_name format a10

SQL> col table_schema format a10

SQL> col privilege format a25

SQL> grant create user to scott with admin option; ==> with admin option으로 권한 부여후

SQL> connect scott/tiger

SQL> grant create user to oracle; ==> 다시 oracle 에게 같은 권한 부여후

SQL> connect internal

SQL> select * from dba_sys_privs

2  where grantee in (‘SCOTT’,’ORACLE’);

GRANTEE    PRIVILEGE                 ADMIN_

———- ————————- ——

ORACLE     CREATE USER               NO

SCOTT      CREATE USER               YES

SCOTT      UNLIMITED TABLESPACE      NO

SQL> revoke create user from scott; ==> scott의 권한을 revoke

SQL> select * from dba_sys_privs

2  where grantee in (‘SCOTT’,’ORACLE’);

GRANTEE    PRIVILEGE                 ADMIN_

———- ————————- ——

ORACLE     CREATE USER               NO ==> scott의 create user 권한만 revoke되었다.  

SCOTT      UNLIMITED TABLESPACE      NO     oracle권한은 그대로

SQL> grant select on dept to oracle with grant option; ==> with grant option 으로 권한 부여후

SQL> connect /

SQL> create user myuser identified by myuser1$

2  default tablespace ts_user1

3  temporary tablespace temp;

SQL> grant select on scott.dept to myuser; ==> 다시 같은 권한을 다른 myuser에게 부여

Grant succeeded.

SQL> select * from all_tab_privs

2  where table_name=’DEPT’;

GRANTOR    GRANTEE    TABLE_SCHE TABLE_NAME PRIVILEGE                 GRANTA

———- ———- ———- ———- ————————- ——

SCOTT      ORACLE     SCOTT      DEPT       SELECT                    YES

ORACLE     MYUSER     SCOTT      DEPT       SELECT                    NO

SQL> revoke select on dept from oracle; ==> oracle의 권한을 revoke

Revoke succeeded.

SQL> select * from all_tab_privs           ==> with grant option으로 생성된 이하 myuser의 권한도

2  where table_name=’DEPT’;      revoke 되었다.

no rows selected

================================================================================================  

* Database Auditing : user 사용 시간정보 확인, 동시사용자측정 등 여러가지에 필요

SQL> connect internal

SQL> shutdown immediate

init.ora file에서 편집

audit_trail = true            # if you want auditing ==> 주석기호(#) 삭제

SQL> startup

SQL> show parameter audit_trail

NAME                                 TYPE     VALUE                          

———————————— ————– ——————————

audit_trail                          string         TRUE

SQL> audit connect;

SQL> select * from dba_stmt_audit_opts;

USER_NAME            PROXY_NAME           AUDIT_OPTION    SUCCESS    FAILURE

——————– ——————– ————— ———- ———-

                                         CREATE SESSION  BY ACCESS  BY ACCESS

                                        

SQL> connect scott/tiger

SQL> connect scott/fail

SQL> connect internal

SQL> select username,timestamp,action_name,logoff_time,returncode

2  from dba_audit_session;

USERNAME   TIMESTAMP ACTION_NAME     LOGOFF_TI RETURNCODE

———- ——— ————— ——— ———-

SCOTT      05-JUL-01 LOGOFF          05-JUL-01          0 ==> login 성공하면 0 return

SCOTT      05-JUL-01 LOGON                           1017 ==> login 실패한 returncode

SQL> shutdown

파라미터 이전대로 돌려두자(#audit_trail=true : 주석처리)

SQL> startup 하고

SQL> show parameter audit_trail

NAME                                 TYPE     VALUE                          

———————————— ————– ——————————

audit_trail                          string         NONE

================================================================================================  

19. Managing Roles

================================================================================================  

* Role

resource role에 포함된 권한을 살펴보자

SQL> select  * from dba_sys_privs

2  where grantee=’RESOURCE’;

GRANTEE    PRIVILEGE            ADMIN_

———- ——————– ——

RESOURCE   CREATE CLUSTER       NO

RESOURCE   CREATE INDEXTYPE     NO

RESOURCE   CREATE OPERATOR      NO

RESOURCE   CREATE PROCEDURE     NO

RESOURCE   CREATE SEQUENCE      NO

RESOURCE   CREATE TABLE         NO

RESOURCE   CREATE TRIGGER       NO

RESOURCE   CREATE TYPE          NO

8 rows selected.

다음은 dev라는 role을 만들어서

SQL> create role dev;

SQL> grant create table,create view to dev;

SQL> grant select on emp to dev;

SQL> connect internal

oracle이라는 user에게 dev,resource role, create session권한 부여

SQL> connect internal

SQL> grant dev to oracle;

SQL> grant resource to oracle;

SQL> grant create session to oracle;

SQL> alter user oracle default role resource;            ==> session의 연결 끊김에 상관없이 지속적으로

SQL> grant select_catalog_role to oracle;                logon 후 resource role이 enable되게 함

                                                            (set 할 필요 없이)

SQL> select segment_name,status from  dba_rollback_segs; ==> 현재 session에서 select_catalog_role이  

select segment_name,status from  dba_rollback_segs           disabled됨

                                *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> set role select_catalog_role; ==> set은 현재session에서 role을 사용가능하게 해줌.

   이를 위해 이 role은 이미 user에게 grant 되어있어야함.

SQL> select segment_name,status from  dba_rollback_segs;

————— ——————————–

SEGMENT_NAME    STATUS

SYSTEM          ONLINE

RBS1            ONLINE

RBS2            ONLINE

================================================================================================  

20. Using National Language Support

================================================================================================  

* sysdate format 변경

SQL> connect internal

SQL> select sysdate from dual;

SYSDATE

———

05-JUL-01

SQL> alter session set nls_date_format=’YY/MM/DD:HH24:MI:SS’;

SQL> select sysdate from dual;

SYSDATE

—————–

01/07/05:12:25:37

================================================================================================  

* Character set 변경하고 한글명 table 만들기

— 가급적 한글명 table은 만들지 않는것이 좋으나 만들수 없는것은 아니다.

SQL> create table scott.부서 as select scott.dept;

create table scott.부서 as select scott.dept

                  *

ERROR at line 1:

ORA-00911: invalid character ==> 테이블명이 한글이어서 error난다.

* Database Characterset을 변경해 보자. ==> 매우 조심스러운 작업

    (DATA 보존 못할 위험성 있다.backup 필요)

SQL> select * from nls_database_parameters ==> nls_database_parameters 에서 현재 DB의  

2  where parameter like ‘%CHARACTERSET%’;     characterset관련을 parameter를 확인

PARAMETER                 VALUE

————————- ——————–

NLS_CHARACTERSET          WE8ISO8859P1

NLS_NCHAR_CHARACTERSET    WE8ISO8859P1

SQL> select value from v$nls_valid_values ==> 234건의 data가 있다.

2  where parameter like ‘%CHARACTERSET’;

— warning : character set을 변경 할수는 있지만 기존에 들어가있는 데이타에 대해서는 책임 못짐.

a.  

SQL> shutdown immediate

SQL> connect internal

SQL> startup mount exclusive;

SQL> alter system enable restricted session;

SQL> alter database open;

b.  

SQL> alter database character set ko16ksc5601;

SQL> alter database national chartacter charcter set ko16ksc5601;

— 확인

SQL> select * from nls_database_parameters ==> nls_database_parameters 에서 현재 DB의  

2  where parameter like ‘%CHARACTERSET%’;     characterset관련을 parameter를 확인

shutdown immediate;

c. .profile edit

NLS_LANG=Amerian_America.us7ascii; export NLS_LANG을

NLS_LANG=korean_korea.ko16ksc5601; export NLS_LANG로 변경

d. Database startup

서진우

서진우

슈퍼컴퓨팅 전문 기업 클루닉스/ 상무(기술이사)/ 정보시스템감리사/ 시스존 블로그 운영자

You may also like...

페이스북/트위트/구글 계정으로 댓글 가능합니다.