About DATABASE

1. SID 란

A name that identifies a specific instance of a running pre-release 8.1 Oracle database.
For any database, there is at least one instance referencing the database.

For pre-release 8.1 databases, SID is used to identify the database.
The SID is included in the connect descriptor of a tnsnames.ora file and
in the definition of the listener in the listener.ora file. 

쉽게 이야기해서 SID 란 Oracle Instance 라고 생각하면 된다.
  ( 3 NODE 일 경우 SID 와 DB 와의 관계 )
  SID ---|
  SID ------- DB
  SID ---|

2. SID 확인 방법
select THREAD#,INSTANCE,STATUS from v$thread;
1    TG2010D    OPEN
--> Single DB 일 경우 Return 되는 Row 가 하나
1    TG2010D1    OPEN
2    TG2010D2    OPEN
--> RAC (2Node) 일 경우 Return 되는 Row 는 두개 이다.

3. DB_NAME 이란 ?
DB_NAME 은 8자리로 제한되는 Database 구분자이다.
이 파라미터는 CREATE DATABASE 일때 지정된다.
하나의 System 에 다수의 Database 가 운영되는 환경이라며,
SID(Oracle Instance Identifier) 와 DB Name 을 Matching 시켜 줘야 한다.
일반적으로 Single Database 일 경우 SID 와 DB Name 을 동일 하게 운영한다.
SID 지정을 통해서 접속 하고자 하는 DB NAME 을 지정한다고 생각하면 된다.
알파벳, _(Underscore), #(Number Signe), $(Dollar Singe) 만 가능하다.

EX) A(A), B(B), C(C)  -- DB_NAME ( SID )순서
    상기와 같은 구성 이라면 C 에 접속 하고자 한다면
    EXPORT ORACLE_SID=C [ UNIX, LINUX ]
    SET $ORACLE_SID=C   [ NT ]
    를 통해서 SID 지정 후 접속하면 원하는 DATABASE 에 접속이 가능하다.


4. DB_NAME 확인
select NAME, DB_UNIQUE_NAME from v$database ;
NAME      DB_UNIQUE_NAME
--------- ------------------------------
TG2010D   TG2010D

5. RAC 일 경우 INIT 파라미터 예제
*.cluster_database_instances=2
*.cluster_database=true
*.db_domain=''
*.db_name='TG2010D'
TG2010D1.instance_number=1
TG2010D2.instance_number=2

6. 기타
   일반적으로 SID 변경이란 SID 및 DB_NAME 을 변경 한다구 생각하면 된다.
   SID(INSTACNE) 만 변경 할 일은 극히 드물다..
   전 해본적이 없음


7. Global_Name
 네트워크 환경에서 데이타베이스 이름이 동일하여 구분하기 어려울 때,
 사용하는 전역 데이터베이스 이름을 사용할 것인지를 결정하는 파라미터이다.
 DB_NAME.DB_DOMAIN 이름으로 구성된다.
 INIT PARAMETER 화일 예제 )
  *.db_domain=''
  -- DB_DOMAIN 은 128 자 까지 가능, Default 값은 WORLD
  *.db_name='TG2010T'
 
8. Global_Name 관련 파라미터
8.1 GLOBAL_NAMES
SYS>show parameters global_names
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     FALSE
==> Default 는 FALSE 값이다.
    DB LINK 를 통해서 접속 시에 GLLBAL_NAME 과 일치해야 하는지,
    FALSE 이면 DB LINK 값과 GLOBAL_NAME 이 달라도 접속이 된다.
    GLOBAL_NAME 의 효과(10번항목) 같이 생각하면
    GLOBAL_NAME 을 TRUE 로 설정하고, 같은 DOMAIN NAME 을 사용한다면
    같은 DOMAIN 내에서만 DB LINK 사용이 가능할것으로 예상된다.

8.2 GLOBAL_NAMES 변경
  SYS> ALTER SESSION SET global_names = TRUE;
  SYS> ALTER SYSTEM SET global_names = FALSE;

8.3 DB_DOMAIN    
SYS>show parameter domain
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string
==>  DB_DOMAIN 은 128 자 까지 가능, Default 값은 WORLD

8.4 DB_NAME
SYS>show parameters db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
db_name                              string      TG2010T
==> GLOBAL_NAME = DB_NAME.DB_DOMAIN 이니 TG2010T 가 GLOBAL_NAME 이고
    GLOBAL_NAME 의 설정은 FALSE 이다.


9. Global_Name 확인 및 변경
SYS>ALTER DATABASE RENAME GLOBAL_NAME TO TG2010Z ;
Database altered.

SYS>select * from global_name ;
GLOBAL_NAME
--------------------------------------------------------------------------------
TG2010Z

SYS>ALTER DATABASE RENAME GLOBAL_NAME TO TG2010T ;
Database altered.

SYS>select * from global_name ;

GLOBAL_NAME
--------------------------------------------------------------------------------
TG2010T

10. Global_Name의 효과 
10. 1 Global_Name 효과
 DB LINK 에 영향을 준다.
 DB LINK 생성 시에 DB_DOMAIN 값이 아닌, GLOBAL_NAME 의 접미사 부분이
 자동으로 DBA_DB_LINKS.DB_LINK 에 붙는다.
 예제) 일반적으로 .WORLD 를 붙이지 않는다.
       테스트로 구별이 쉽게 하기 위해서 .WORLD 로 설정
      
 SYS> SELECT GLOBAL_NAME  FROM GLOBAL_NAME ;
 TG.WORLD
 SYS> CREATE DATABASE LINK JT USING 'JT' ;
 SYS> SELECT OWNER,DB_LINK,HOST FROM DBA_DB_LINKS  ;
 SYS    JT2010D.WORLD   JT010d
 ==> 이런식으로 LOCAL DATABASE 의 아래의 값이 DB LINK 생성 시 자동으로 할당된다.
     예제에선 .WORLD
 ( SELECT GLOBAL_NAME  FROM GLOBAL_NAME ) - ( SELECT INSTANCE_NAME FROM V$INSTANCE )
 
10.2  DB_DOMAIN 확인 방법
 SYS> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'db_domain';

출처 : http://www.oracleclub.com/article/23879

결론 :
1. Oracle 에서 칼럼 단위로 권한 부여가 가능한것은 Insert , Update 에 대해서다
2. Delete 의 경우 Row의 구성 Column 전체에 대한 작업이니 칼럼 단위 권한 부여 자체가 불가능한것으로 생각된다.
3. select 의 경우 View 를 통한 대체가 가능하다.
         

SYS> create table myemp as select * from scott.emp ;

SYS> grant select on myemp to scott ;

SYS> grant update ( sal ) on myemp to scott ;

SYS> create public synonym myemp for myemp ;

SCOTT> select * from myemp ;
==> 조회 가능

SCOTT>delete myemp ;
delete myemp
       *
ERROR at line 1:
ORA-01031: insufficient privileges
==> 삭제 불가능

SCOTT>insert into myemp ( empno, ename) values ( 9999, ’TG’);
insert into myemp ( empno, ename) values ( 9999, ’TG’)
            *
ERROR at line 1:
ORA-01031: insufficient privileges
==> 입력 불가능

SCOTT>update myemp  set ENAME=’TG’ where empno=7369 ;
update myemp  set ENAME=’TG’ where empno=7369
       *
ERROR at line 1:
ORA-01031: insufficient privileges
==> 권한 없는 칼럼 Update 불가능

SCOTT>update myemp  set sal = sal*2 where EMPNO=7369;
1 row updated.
==> 권한 있는 칼럼 Update 가능

SYS>grant update (job) on myemp to scott ;
Grant succeeded.
==> 다른 칼럼에 대한 Update 권한 추가적으로 부여

-- 칼럼에 대한 선택적인 권한 부여가 가능하다.

[ 추가 테스트 ]
1. 선택적 Column 에 대한 Select 권한 부여
SYS>grant select ( empno, ename ) on emp to scott ;
grant select ( empno, ename ) on emp to scott
             *
ERROR at line 1:
ORA-00969: missing ON keyword


SYS>grant select ( empno ) on emp to scott ;
grant select ( empno ) on emp to scott
             *
ERROR at line 1:
ORA-00969: missing ON keyword

==> Select 권한에 대해서는 칼럼 단위로 권한을 부여 할수 없다.

2. 선택적 Column 에 대한 Insert 권한 부여
SYS>grant insert ( empno, ename ) on myemp to scott ;
Grant succeeded.

SCOTT>insert into myemp ( empno,ename) values (9999,’TG’);
1 row created.
==> 권한 있는 칼럼에 대한 Insert 가능
SCOTT>insert into myemp ( empno,ename,job) values (9998,’TG2’,’Clerk’);
insert into myemp ( empno,ename,job) values (9998,’TG2’,’Clerk’)
            *
ERROR at line 1:
ORA-01031: insufficient privileges
==> 권한 없는 칼럼에 대한 Insert 불가능

3. 권한 체크

SYS>select GRANTEE, TABLE_NAME, PRIVILEGE from dba_Tab_privs where table_name =’MYEMP’;
no rows selected

SYS> select GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE
     from dba_col_privs where table_name =’MYEMP’
     order by TABLE_NAME, PRIVILEGE,COLUMN_NAME, GRANTEE ;

GRANTEE      TABLE_NAME    COLUMN_NAME  PRIVILEGE
------------ ------------- ------------ ----------
SCOTT        MYEMP         ENAME        INSERT
SCOTT        MYEMP         EMPNO        INSERT
SCOTT        MYEMP         SAL          UPDATE
SCOTT        MYEMP         JOB          UPDATE

출처 : http://www.oracleclub.com/article/24483

* 사용자 비밀번호를 변경하는 방법.

        ALTER USER <username> IDENTIFIED BY <new_password>;

오라클 8 부터는 sqlplus 를 통해, 유닉스처럼 비밀번호를 변경한다.

         SQL> password
         Changing password for SCOTT
         Old password:
         New password:
         Retype new password:

만약 다른 사용자의 비밀번호를 변경하고 싶다면, 'password 사용자이름' 으로 한다.

 

* 사용자를 삭제하고, 만드는 방법.
생성)
  CREATE USER scott
  IDENTIFIED BY tiger  -- 비밀번호
  DEFAULT TABLESACE  tools -- 테이블스페이스 설정
  TEMPORARY TABLESPACE temp; -- 임시테이블 스페이스 설정 ( 임시테이블스페이스가 정렬시사용되므로 정렬공간확보 )
삭제)
        DROP USER scott CASCADE; 

생성후 권한 주기)
        GRANT CONNECT, RESOURCE TO scott;
        GRANT DBA TO scott; 

사용자의 테이블 스페이스 사용량 지정하기)
        ALTER USER scott QUOTA UNLIMITED ON tools;

 

* 사용자 관리방법

DBA 는 특정사용자를 Lock, Unlock, 비밀번호를 바꾸도록 할 수 있다.
오라클 9i 는 디비를 만들때 dbca(DB Configuration Assistant) 를 사용해서, SYS, SYSTEM 을 제외한 모든 계정을 Lock 할 수 있다.
사용하려면, 각 계정들을 Unlock 해줘야 한다.

         ALTER USER scott ACCOUNT LOCK       -- lock a user account
         ALTER USER scott ACCOUNT UNLOCK;    -- unlocks a locked users account
         ALTER USER scott PASSWORD EXPIRE;   -- 새로운 비밀번호로 바꾸도록 한다.

 

* 비밀번호 관리 방법.
오라클은 한자리 비밀번호나, 사용자 계정 그대로 비밀번호를 만드는 것을 허용한다. 별로 좋지 않다.

오라클 8 부터는 profile 을 통해 비밀번호를 관리한다.

 CREATE PROFILE my_profile LIMIT
  PASSWORD_LIFE_TIME 30;
 ALTER USER scott PROFILE my_profile;

설정 가능한 값들)
  FAILED_LOGIN_ATTEMPTS - 로그인 몇번 실패시 Lock 할 것인지?
  PASSWORD_LIFE_TIME    - 패스워드 유효일수
  PASSWORD_REUSE_TIME   - 패스워드 재사용일수 (number of days before a password can be reused)
  PASSWORD_REUSE_MAX    - 현재 패스워드를 재사용한다면, 변경할때까지의 날수.
                         (number of password changes required before the current password can be reused )
  PASSWORD_LOCK_TIME    - 로그인 실패로 인한 Lock 일수
  PASSWORD_GRACE_TIME   - 로그인은 허용되고 경고가 주어지는 기간.
  PASSWORD_VERIFY_FUNCTION - 패스워드 변경 함수.

* administrative (privileged) 사용자란.
SYSOPER 혹은 SYSDBA 의 권한을 가진 사용자를 의미한다. 이런 권한이 있으면 디비가 꺼져있어도 디비에 접근해서 작업을 할 수 있다.
이 권한을 관리하는 것은 디비가 아닌 패스워드 파일을 사용해서 하는데, 패스워드 파일은 orapwd 유틸리티를 사용해서 만들어낸다.

 

* administrative user 로 연결하는 방법.
administrative user는 유닉스에서는 DBA 그룹이고, 윈도우즈 NT 계열에서는 ORA_DBA 그룹이다.
         connect / as sysdba

         connect sys/password as sysdba

 

* 패스워드파일을 만드는 방법.
패스워드 파일은 $ORACLE_HOME/dbs/orapw or orapwSID 에 있다.
원격의 접속자를 위해 패스워드 파일을 생성한다.

1) 로그인 해서
2) 다음 명령실행. :
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd
3) Shutdown the database (SQLPLUS> SHUTDOWN IMMEDIATE)
4) INIT.ORA file 을 열어 REMOTE_LOGIN_PASSWORDFILE=exclusive 라고 수정한다.
5) Startup the database (SQLPLUS> STARTUP)

 

* 패스워드파일에 사용자 추가하는 방법.
패스워드파일에 어떤 사용자가 있는지는 SYS.V_$PWFILE_USERS 에 쿼리해서 알아낸다.
sysdba 나 sysoper 권한을 주면 추가되고, 혹은 orapwd 유틸을 사용한다.
         GRANT SYSDBA TO scott;

 

* 다른 사용자로 바꾸고 싶다면.

        SQL> select password from dba_users where username='SCOTT';
        PASSWORD
         -----------------------------
         F894844C34402B67

        SQL> alter user scott identified by lion;
        User altered.

        SQL> connect scott/lion
        Connected.

        REM Do whatever you like...

        SQL> connect system/manager
        Connected.

        SQL> alter user scott identified by values 'F894844C34402B67';
        User altered.

        SQL> connect scott/tiger
        Connected.

 

* 원격 로그인시에 반드시 비밀번호를 입력하도록 한다.

REMOTE_OS_AUTHENT = FALSE 로 한다.

 

* 오라클 생성시 만들어지는 유저들... (이렇게나 많았나????)

 

SYS/CHANGE_ON_INSTALL or INTERNAL
Oracle Data Dictionary/ Catalog
생성 스크립트: ?/rdbms/admin/sql.bsq and various cat*.sql scripts
패스워드수정가능(디비만든 직후)
삭제불가

 

SYSTEM/MANAGER
The default DBA user name (please do not use SYS)
생성 스크립트: ?/rdbms/admin/sql.bsq
패스워드수정가능(디비만든 직후)
삭제불가

OUTLN/OUTLN
Stored outlines for optimizer plan stability
생성 스크립트: ?/rdbms/admin/sql.bsq
패스워드수정가능(디비만든 직후)
삭제불가

SCOTT/TIGER, ADAMS/WOOD, JONES/STEEL, CLARK/CLOTH and BLAKE/PAPER.
Training/ demonstration users containing the popular EMP and DEPT tables
생성 스크립트: ?/rdbms/admin/utlsampl.sql
패스워드수정가능(디비만든 직후)
삭제가능

HR/HR (Human Resources), OE/OE (Order Entry), SH/SH (Sales History).
Training/ demonstration users containing the popular EMPLOYEES and DEPARTMENTS tables
생성 스크립트: ?/demo/schema/mksample.sql
패스워드수정가능
삭제가능

CTXSYS/CTXSYS
Oracle interMedia (ConText Cartridge) administrator user
생성 스크립트: ?/ctx/admin/dr0csys.sql

TRACESVR/TRACE
Oracle Trace server
생성 스크립트: ?/rdbms/admin/otrcsvr.sql

DBSNMP/DBSNMP
Oracle Intelligent agent
생성 스크립트: ?/rdbms/admin/catsnmp.sql, called from catalog.sql
패스워드수정가능 - snmp_rw.ora 파일에 새로운 비밀번호를 넣는다
삭제가능 - Only if you do not use the Intelligent Agents

ORDPLUGINS/ORDPLUGINS
Object Relational Data (ORD) User used by Time Series, etc.
생성 스크립트: ?/ord/admin/ordinst.sql

ORDSYS/ORDSYS
Object Relational Data (ORD) User used by Time Series, etc
생성 스크립트: ?/ord/admin/ordinst.sql

DSSYS/DSSYS
Oracle Dynamic Services and Syndication Server
생성 스크립트: ?/ds/sql/dssys_init.sql

MDSYS/MDSYS
Oracle Spatial administrator user
생성 스크립트: ?/ord/admin/ordinst.sql

AURORA$ORB$UNAUTHENTICATED/INVALID
Used for users who do not authenticate in Aurora/ORB
생성 스크립트: ?/javavm/install/init_orb.sql called from ?/javavm/install/initjvm.sql

PERFSTAT/PERFSTAT
Oracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/UTLESTAT
생성 스크립트: ?/rdbms/admin/statscre.sql

* 의심가는 테이블 감시하는 법.
DBMS_FGA 를 통해 의심가는 테이블에 대한 감시 정책을 생성한다.
DBA_AUDIT_POLICIES 에서 감시정책의 리스트를 볼 수 있고,
감시 기록은 DBA_FGA_AUDIT_TRAIL 에 남는다.
DBMS_FGA PL/SQL패키지는 테이블이나 뷰에 대한 정책을 적용하기 위한 패키지 안의 프로시져 즉 인터페이스이다.

 -- Add policy on table with auditing condition...
 execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');
 -- Must ANALYZE, this feature works with CBO (Cost Based Optimizer)
 analyze table EMP compute statistics;

 select * from EMP where c1 = 11;  -- Will trigger auditing
 select * from EMP where c1 = 09;  -- No auditing

 -- Now we can see the statments that triggered the auditing condition...
 select sqltext from sys.fga_log$
 delete from sys.fga_log$;

 

* 뒤에 두개는 무시.. 머리 아픔. 뭔지 모르겠음.
* What is a Virtual Private Database?
* What is Oracle Label Security?

출처 : Tong - jackie92님의 ◐ Database Tip통

Data Pump

1.1. Oracle Data pump란?

 

Oracle Data Pump는 Oracle Database 10g 버전에서 제공되는Utility로 향상된 데이터 이동을 가능하게 한다.
이전 버전의 오라클을 설치한 홈 디렉토리에는 ‘imp’,’exp’라는 실행 파일이 있다. 이는 오라클에서 제공하는 backup 및 recovery 에 사용되는 도구 이다.
Exp는 데이터베이스에 저장되어 있는 데이터들을 OS의 바이너리 파일로 전환하는 도구이고, imp는 바이너리 파일을 데이터베이스 안의 데이터로 전환하는 도구이다. 새로 등장한Data Pump는 exp 와 imp를 대체하기 위하여 오라클 10g 버전부터 제공되는 유틸리티로 Exp / Imp 와 유사한 동작을 하지만 data pump 가 훨신 효율적으로 동작한다.
Exp/Imp와 비교하여 그 효율성을 예를 들자면 exp시 single thread 에서 2배가 빠르고 imp시 15~45배 빠르므로 데이터베이스간의 bulk data 와 meta data의 전송시간을 줄이는데 효율적으로 사용될 수 있다.

 

1.2. Data pump Key features

 

1.2.1. Fast Performance

앞에서 말한 것과 같이Data Pump Export and Import 유틸리티는 기존의 Export and Import 유틸리티보다 훨씬 빠르다. Data Pump Export 에서 direct path method를 사용시 single stream data unload에서 기존의 export 보다 2배가 빠르다. 이는 direct path API가 더 효과적으로 수정 되었기 때문이다. Parallelism 의 level에 따라서는 더욱 향상된 performance를 보일 수 있다.

 

Data pump import 에서는 single stream 의 data load 시 기존의 import 보다 15~45배가 빠르다. 이는 기존의 import 에서 단순히 export dump파일에서 레코드를 읽고 일반적인 insert into 명령을 사용해서 대상 테이블에 삽입 하는 대신에 Data pump import 는Direct path method loading 을 사용하기 때문이다.

 

1.2.2. Improved Management Restart

 

모든 Data Pump operation은 Data Pump job을 실행하는 스키마에 만들어진 master table을 가지고 있다. Master table은 현재 수행중인 모든 export또는 import시 객체의 상태정보와 dump file set에서의 위치정보를 가지고 있다. 이는 갑작스런 job의 중단에도 job 의 성공적인 종료에 상관 없이 어떤 object의 작업이 진행 중이었는지 알 수 있게 해 준다. 그래서 master table 과 dump file set 이 있는 한 모든 정지된 data pump job은 데이터 손실 없이 다시 시작할 수 있다.

 

1.2.3. Fine-Grained Object Selection

 

Data Pump job 은 거의 모든 type의 object를 exclude 또는 include 시킬 수 있다.
아래의 parameter 가 사용된다.
* EXCLUDE - 특정 객체 유형을 제외한다. (예: EXCLUDE=TABLE:EMP)
* INCLUDE - 특정 객체 유형을 포함한다. (예: INCLUDE=TABLE_DATA)
* CONTENT - 로드를 취소할 데이터를 지정한다.
적합한 키: (ALL), DATA_ONLY 및 METADATA_ONLY.
* QUERY - 테이블의 부분 집합을 엑스포트하기 위해 사용되는 술어 절이다.

 

1.2.4. Monitoring and Estimating Capability

 

Data Pump는 Standard progress , error message를 log file에 기록할 뿐만 아니라 현재 operation의 상태를 대화식모드 ‘command line’으로 보여 준다. Job의 completion percentage를 측정하여 보여주며 초 단위의 지정한 time period에 따라 자동으로 update하여 표시한다.

1개 이상의 client가 running job에 attach 수 있기 때문에 업무환경에서 job을 실행하고, detach 한 후 집에 가서 job을 reattach 하여 끊김 없이 모든 job을 모니터링 할 수 있다.
모든export job이 시작할 때 대략적인 전체unload양을 측정해 준다. 이는 사용자가 dump file set을 위한 충분한양의 disk space를 할당할 수 있게 한다.

 

1.2.5. Network Mode

 

Data Pump Export and Import는 job의 source가 리모트 인스턴스 일 경우를 위한 network mode를 지원한다.

 

Network을 통해 import를 할 때 source가 dump file set이 아닌 다른 database에 있기 때문에 dump file이 없다.

 

Network를 통해 export를 할 때 souce가 다른시스템에 있는 read-only database 일 수 있다. Dumpfile은 local(non-networked)export 처럼 local 시스템에 쓰이게 된다.

 

1.3. Data pump overview

 

1.3.1. Data Pump Overview

 

- expdp/impdp로 제공 되어 진다.
- exp/imp의 superset 이다.
- Data 와 metadata를 매우 빠른 속도로 load/unload 하는 Server-based facility이다.
                    ==> dump file sets은 Server에 생성
- DBMS_DATAPUMP PL/SQL Package를 이용하여 사용 가능 하다.
- Web-based interface <--access from EM Database Control이 가능하다.
- Data Pump job을 실행하는 schema에 master table(MT)이 만들어 진다.

MT는 해당 job의 모든 것(aspects)을 관리하며 data pump(expdp)의 마지막 단계에서 pump file sets에 기록된다.

file based import 작업(impdp)시 dump file에 있는 MT 내용을 current user의 schema에 제일먼저 loading한다.

계획 또는 예상치 못한 job의 중단 시 재가동수 있게 하는 Data Pump의 핵심이 MT 이다.

Client process는 Data Pump API를 call한다.

-여러 개의 clients가 모니터링하고 control하기 위해서 job을 attach/detach 한다.

 

1.3.2. Data Pump Benefit

 

- Data Access Methods : Direct Path, External Tables
- Detach from, reattach to log-running jobs
- Restart Data Pump Jobs
- Find-grained object selection <-- 원하는 rows만(EXCLUDE, INCLUDE, CONTENT)
- Explicit database version specification
- Parallel execution
- Estimate export job space <--ESTIMATE_ONLY
- Network Mode에서는 Remote의 server process가 DB link를 이용하여 Local에 dump file을 직접 만들어 준다..
- Import 과정에서 target data file name, schema, tablespace 을 변경할 수 있다.

1.3.3. Data Pump File Locations

 

- Data pump file 종류

- DUMP file : data와 metadata를 포함한다.
- LOG file : operation과 관련된 message를 기록한다.
- SQL file : impdp에서 SQLFILE operation의 결과를 기록한다.
- Data Pump는 server-based 이므로 Oracle directory path를 통해서 Data Pump file에 access한다.
Absolute path는 보안상 지원되지 않는다.

- Order of precedence of file locations

 

1) per-file directory
- dump file, log file, sql file 마다 지정될 수 있다. 콜론(:)으로 directory 와 file name 을 구분한다.
예) dumpfile=AA:A.dmp

 

2) DIRECTORY parameter

- directory object를 사용한다.
Create Directory DIR_PJH as '/home/oracle10g/test/';
Grant read, write On Directory DIR_PJH to SCOTT;
Directory=AA
Dumpfile=A.dmp

3) DATA_PUMP_DIR 환경변수

- DIRECTORY Parameter를 대신하여 directory object name을 설정한다.
export DATA_PUMP_DIR=AA
Dumpfile=A.dmp
- 위의 모든 경우에 시도하려는 operation에 대해 directory object에 대해 적절한 access privs가 있어야 한다.
Export할 경우 모든 file에 대해 write access가 필요하다.
Import할 경우 dump file에 대해 read access, log file과 sql file에 대해 write access가 필요하다.

 

1.3.4. Data Pump File Naming and size

 

(1) DUMPFILE
- file list는 , 로 분리한다.
- %U template --> two-character, fix-width, 01부터 증가하는 integer 를 가진다.
- DUMPFILE 이 지정되어 있지 않으면 expdat.dmp 가 default로 사용된다. Default는 autoextensible이다.

 

(2) FILESIZE
- FILESIZE 가 지정되어 있으면 각 file은 FILESIZE안으로 만들어지고 늘어날 수 없다. dump 공간이 더 필요하고 template %U가 지정되었다면, 새로운 파일이 생성된다. 그렇치 않으면 사용자는 new file을 add하라는 메세지를 받는다.

 

(3) PARALLEL
- %U가 지정되면 PARALLEL parameter의 개수만큼 초기에 file이 생성된다.
- 기존에 존재하는 file과 이름이 중복될 경우 overwrite하지 않고 에러를 발생시키고 job이 abort된다.
- 복수개의 dump file template가 제공되면 round-robin fashion으로 dump file을 생성하는 데 사용한다.

 

1.3.5. Data Pump Filtering

 

(1) Find-grained object selection
- 기존의 exp/imp는 index, trigger, grant, constraint를 포함하거나 제외하는 것이 있으나
data pump는 virtually any type of object를 포함하거나 제외할 수 있다.
- EXCLUDE 와 IMCLUDE는 mutually exclusive 하다.
- INCLUDE = object_type[:"name_expr"]
- EXCLUDE = object_type[:"name_expt"]
- 모든 view, 모든 package, EMP로 시작하는 Index만 제외한다.
EXCLUDE=view
EXCLUDE=package
EXCLUDE=INDEX:"LIKE 'EMP%' "

 

(2) Data Selection
- CONTENT = ALL(def) | METADATA_ONLY | DATA_ONLY
- QUERY = [Schema.][table_name:]"query_clause"
- CONTENT에 data_only가 사용되면 EXCLUDE 와 INCLUDE를 사용할 수 없다.QUERY=hr.employees:"WHERE department_id in (10,20) and salary < 1600 ORDER BY department_id"
    <--특정 table을 지정해서 해당 table로 한정. imp시에도 적용.

 

1.3.6. Data Pump Job Monitoring

 

- 데이터베이스 뷰에서 실행되는 Data Pump 작업에 관해서도 자세한 정보를 확인할 수 있다.
- DBA_DATAPUMP_JOBS – 작업에서 실행되는 작업자 프로세스(DEGREE 열)의 수를 확인 할 수 있다.
- DBA_DATAPUMP_SESSIONS –이전 뷰 및 V$SESSION과 조인하여 foreground 프로세스 세션의 SID확인 할 수 있다.

 

select sid, serial# from v$session s, dba_datapump_sessions d where s.saddr = d.saddr;

- V$SESSION_LONGOPS - 작업 완료에 걸리는 시간을 예측하는 또 다른 유용한 정보를 얻을 수 있다.

select sid, serial#, sofar, totalwork from v$session_longops where opname = 'CASES_EXPORT' and sofar != totalwork;

totalwork 열에는 총 작업량이 표시되는데, 이 중 현재까지 sofar 작업량을 완료했으므로 이를 통해 얼마나 더 시간이 걸릴지 예측할 수 있다.

 

1.3.7. Data Pump Export and Import

 

1) Parallel Full Export and Import

> expdp system/manager full=y parallel=4 dumpfile=DATADIR1:full1%U.dat, DATADIR2:full2%U.dat, DATADIR3:full3%U.dat, DATADIR4:full4%U.dat filesize=2G

 

<--4개의 work process를 가진 full export,
Pump file은 DATADIR1, DATADIR2, DATADIR3, DATADIR4 네 곳에 라운드로빈 방식으로 생성된다.
2G를 넘지 않으면서 최소4개 생성.
Job 과 master table 이름은 default로 SYSTEM_EXPORT_FULL_01 를 가진다.

 

>impdp system/manager directory= NET_STORGAE_1 parallel=4 dumpfile= full1%U.dat, full2%U.dat, full3%U.dat, full4%U.dat

 

<--expdp로 받은 dump file을 network를 통해 NET_STORAGE_1 이라는 directory object위치로 보내졌다. Default import는 dump set 전체를 import하는 것이므로 Full=y 는 필요 없다.
Job 과 master table 이름은 default로 SYSTEM_IMPORT_FULL_01 를 가진다.

 

2) Limited Schema Export (fine-grained)

incluse=function include=procedure include=pacakge include=type include=view:"like 'PRODUCT%'" > expdp system/manager schemas=hr,oe directory=USR_DATA dumpfile=schema_hr_oe.dat parfile=exp_par.txt <----------------------------

 

<--HR, OE schema에서 모든 func, prod, pkg, user-defined type, PRODUCT로 시작하는 view를 export한다.
Schema definition과 system priv graints는 export되지 않는다.

 

> impdp system/manager directory=USR_DATA dumpfile=schema_hr_oe.dat sqlfile=schema_hr_oe.dat

 

<--실제 import는 하지 않고 dmp file에서 DDL 문장만 뽑아낸다.

 

3) Network Mode Import (DB Link)

network_link=finance.hq.com <--db link remap_schema=payroll:finance

> impdp system/manager schemas=hr,sh,payroll
parfile=imp_par.txt <--------------------------------

<--Source DB에 dblink로 붙어서 hr, sh, payroll schema를 가져온 다음 imp 한다.
이때 payroll schema로 finance schema로 만들어 진다.
SYSTEM은 IMPORT_FULL_DATABASE role을 가지고 있고 Source DB에 대해서는 EXPORT_FULL_DATABASE role을 가지므로 Target DB에 해당 schema definition이 없으면 만들어진다.
flashback_time은 예전의 CONSISTENT와 동일하다.

 

4) Data-Only Unload

> expdp hr/hr parfile=exp_par.txt dumpfile=expdat.dmp content=data_only
include=table:"in ('DEPARTMENTS','DEPARTMENTS_HIST','EMPLOYEES','EMP_HIST')"
query="where DEPARTMENT_ID != 30 order by DEPARTMENT_ID"

 

1.3.8. Data Pump restarting

 

1) Attaching to Existing Job
> expdp system/manager attach=EXP_TS1

<--Job name(MT name) :dba_datapump_jobs 해당 스키마에 active export job 이 하나만 있을 경우 안 적어도 된다..

job: EXP_TS1
owner: SYSTEM
mode:
status:
Export> STOP_JOB
<--중지.
Attach session은 terminate 되고 실행되던 job은 controlled fashion으로 run down 된다.

해당 Job은 dump file과 SYSREM.EXP_TS1 table이 disturbed 되지 않는 한 startable 하다.

 

2) Restarting Stopped Job

> expdp system/manager attach=exp_ts1
<--같은 schema안에 여러 개의 outstanding job이 있으면 job name지정한다.

Export> parallel=4 Export> start_job Export> status =600 <--10분

<-- detailed per-work process가 10분 단위로 regular status message를 보여준다.
KILL_JOB로 job을 kill한다.

<--status, status=600(초)
stop_job,
start_job,
continue_client: attach한 session이 계속 받아서 expdp 실행한다.(logging mode로 전환)
exit_client: Attach를 빠져 나옴. expdp는 background로 실행한다.

 

1.4. Data pump 실습

 

1.4.1. 전체 데이터베이스 export 실습

 

SQL> conn /as sysdba
연결되었습니다.

SQL> create directory dump as 'C:₩oracle/backup'; ->directory를 생성한다.

 

디렉토리가 생성되었습니다.
SQL> grant read ,write on directory dump to public; -> directory에 권한을 부여한다.

 

권한이 부여되었습니다.

 

SQL> host

Microsoft Windows XP [Version 5.1.2600]

(C) Copyright 1985-2001 Microsoft Corp.
C:₩oracle>expdp system/oracle dumpfile=full.dmp directory=dump full=y job_name=Lucie

Export: Release 10.2.0.1.0 - Production on 화요일, 29 5월, 2007 17:17:41

Copyright (c) 2003, 2005, Oracle. All rights reserved.

접속 대상: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the OLAP and Data Mining options
"SYSTEM"."LUCIE" 시작 중: system/******** dumpfile=full.dmp directory=dump full=y job_name=Lucie
BLOCKS 메소드를 사용하여 예측 진행 중...
객체 유형 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA 처리 중
BLOCKS 메소드를 사용한 총 예측: 66.56 MB ->대략적인dmp파일 size를 예측할 수 있다.
객체 유형 DATABASE_EXPORT/TABLESPACE 처리 중
객체 유형 DATABASE_EXPORT/SYS_USER/USER 처리 중
객체 유형 DATABASE_EXPORT/SCHEMA/USER 처리 중
객체 유형 DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ 처리 중

. . "SCOTT"."DEPT" 48.00 MB 2097152행이 엑스포트됨
. . "SYSMAN"."MGMT_JOB_CRED_PARAMS" 11.70 KB 18행이 엑스포트됨
. . "SYSMAN"."MGMT_JOB_PROP_PARAMS" 8.820 KB 12행이 엑스포트됨
. . "SYSMAN"."MGMT_JOB_STEP_PARAMS" 127.3 KB 1128행이 엑스포트됨

 

Control + c ->중간에 끊어도 job이 끊기지 않고 명령모드로 들어간다.

 

Export>status 작업: LUCIE 작업: EXPORT 모드: FULL 상태: EXECUTING 처리된 바이트: 50,337,376 완료율: 84 -> 진행률을 알 수 있다. 현재 병렬도: 1 작업 오류 수: 0 덤프 파일: C:₩ORACLE₩BACKUP₩FULL.DMP 기록된 바이트: 55,226,368 작업자 1 상태: 상태: EXECUTING 객체 스키마: SYSMAN 객체 이름: MGMT_JOB_EXECUTION 객체 유형: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA 완료된 객체: 45 총 객체: 408 작업자 병렬도: 1 Export> stop_job -> job 을 정지시킨다. 이 작업을 정지하겠습니까([예]/아니오):

 

C:₩oracle>

C:₩oracle>sqlplus "/as sysdba"

 

SQL*Plus: Release 10.2.0.1.0 - Production on 화 5월 29 17:32:59 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

다음에 접속됨:

 

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the OLAP and Data Mining options

SQL>select owner_name,job_name,operation,job_mode,state from dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE
---------- ---------- ------------ ----------- ------------
SYSTEM LUCIE EXPORT FULL NOT RUNNING
                                 ->job 상태를 확인할 수 있다.

SQL> exit

 

C:₩oracle>expdp system/oracle attach=lucie ->job을 다시 attach한다.

Export: Release 10.2.0.1.0 - Production on 화요일, 29 5월, 2007 17:35:54

 

Copyright (c) 2003, 2005, Oracle. All rights reserved.

접속 대상: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the OLAP and Data Mining options

 

작업: LUCIE
소유자: SYSTEM
작업: EXPORT
생성자 권한: FALSE
GUID: 18405C1B820C4ABB9B30C4948E0D356F
시작 시간: 화요일, 29 5월, 2007 17:35:56
모드: FULL
인스턴스: ora10
최대 병렬도: 1
EXPORT 작업 매개변수:
매개변수 이름 매개변수 값:
  CLIENT_COMMAND    system/******** dumpfile=full.dmp directory=dump full=y job_name=Lucie
상태: IDLING
처리된 바이트: 51,646,000
완료율: 99
현재 병렬도: 1
작업 오류 수: 0
덤프 파일: C:₩oracle/backup₩full.dmp
기록된 바이트: 55,914,496

작업자 1 상태:
상태: UNDEFINED

 

SQL>select owner_name,job_name,operation,job_mode,state from dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE
---------- ---------- ------------ ----------- ------------
SYSTEM LUCIE EXPORT FULL IDLING
                            ->job 상태를 확인할 수 있다.

Export> start_job ->Job을 다시restar한다.

Export> status

작업: LUCIE
작업: EXPORT
모드: FULL
상태: COMPLETING
처리된 바이트: 51,646,001
완료율: 100
현재 병렬도: 1
작업 오류 수: 0
덤프 파일: C:₩oracle/backup₩full.dmp
기록된 바이트: 64,684,032

작업자 1 상태:
상태: WORK WAITING

C:₩oracle>

 

Logfile 확인

 

지정한 directory 위치에 “export” log file을 확인 한다. 파일의 끝부분을 보면 성공적으로 완료됨을 확인할 수 있다.

"SYSTEM"."LUCIE" 작업이 17:18:56에서 사용자 요청에 의해 정지됨 LUCIE 작업이 화요일, 29 5월, 2007 17:35 에서 다시 열림 ->작업을 정지했다 다시 시작한 것을 확인 할 수 있음 "SYSTEM"."LUCIE" 재시작 중: system/******** dumpfile=full.dmp directory=dump full=y job_name=Lucie 마스터 테이블 "SYSTEM"."LUCIE"이(가) 성공적으로 로드됨/로드 취소됨 ****************************************************************************** SYSTEM.LUCIE에 대해 설정된 덤프 파일: C:₩oracle/backup₩full.dmp "SYSTEM"."LUCIE" 작업이 17:37:12에서 성공적으로 완료됨

 

1.4.2. 특정 스키마 DDL 스크립트 생성 실습

 

SQL> conn /as sysdba 연결되었습니다. SQL> create directory dump as 'C:₩oracle/backup'; ->directory를 생성한다. 디렉토리가 생성되었습니다. SQL> grant read ,write on directory dump to public; -> directory에 권한을 부여한다. C:₩oracle>impdp system/oracle directory=dump dumpfile=full.dmp schemas=scott sqlfile=ddl_scott.sql <이 명령은 dump로 지정된 디렉터리에 ddl_scott.sql로 명명된 파일을 생성하며 엑스포트 덤프 파일 내의 객체 스크립트를 생성한다.> Import: Release 10.2.0.1.0 - Production on 화요일, 29 5월, 2007 19:12:13 Copyright (c) 2003, 2005, Oracle. All rights reserved. 접속 대상: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the OLAP and Data Mining options 마스터 테이블 "SYSTEM"."SYS_SQL_FILE_SCHEMA_01"이(가) 성공적으로 로드됨/로드 취소됨 "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" 시작 중: system/******** directory=dump dumpfile=full.dmp schemas= scott sqlfile=ddl_scott.sql 객체 유형 DATABASE_EXPORT/SCHEMA/USER 처리 중 객체 유형 DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT 처리 중 객체 유형 DATABASE_EXPORT/SCHEMA/ROLE_GRANT 처리 중 객체 유형 DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE 처리 중 객체 유형 DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA 처리 중 객체 유형 DATABASE_EXPORT/SCHEMA/TABLE/TABLE 처리 중 객체 유형 DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS 처리 중 "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" 작업이 19:12:22에서 성공적으로 완료됨

 

Logfile 확인

 

- dump로 지정된 C:₩oracle/backup에 ddl_scoot.sql파일이 생성된다.

 

-- CONNECT SYSTEM -- new object type path is: DATABASE_EXPORT/SCHEMA/USER CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"; -- new object type path is: DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT GRANT UNLIMITED TABLESPACE TO "SCOTT"; GRANT CREATE SESSION TO "SCOTT"; -- new object type path is: DATABASE_EXPORT/SCHEMA/ROLE_GRANT GRANT "RESOURCE" TO "SCOTT"; -- new object type path is: DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE ALTER USER "SCOTT" DEFAULT ROLE ALL; -- new object type path is: DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA -- CONNECT SCOTT BEGIN sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'ORA10', inst_scn=>'283762'); COMMIT; END; / -- new object type path is: DATABASE_EXPORT/SCHEMA/TABLE/TABLE -- CONNECT SYSTEM CREATE TABLE "SCOTT"."EMP" ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ; CREATE TABLE "SCOTT"."DEPT" ( "DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ;

 

1.4.3. 존재하는 table import

 

1) content=data_only 포함한 경우

 

C:₩oracle>Impdp system/oracle dumpfile=full.dmp directory= dump content=data_only job_name=data_import logfile=table_log tables=scott.dept Import: Release 10.2.0.1.0 - Production on 화요일, 29 5월, 2007 19:42:15 Copyright (c) 2003, 2005, Oracle. All rights reserved. 접속 대상: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the OLAP and Data Mining options 마스터 테이블 "SYSTEM"."DATA_IMPORT"이(가) 성공적으로 로드됨/로드 취소됨 "SYSTEM"."DATA_IMPORT" 시작 중: system/******** dumpfile=full.dmp directory= dump content=data_only job_name=data_import logfile=table_log tables=scott.dept 객체 유형 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA 처리 중 . . "SCOTT"."DEPT" 48.00 MB 2097152행이 임포트됨 "SYSTEM"."DATA_IMPORT" 작업이 19:42:52에서 성공적으로 완료됨

 

2) content=data_only 포함하지 않은 경우

 

C:₩oracle>Impdp system/oracle dumpfile=full.dmp directory= dump job_name=data_import logfile=table_log tables=scott.dept Import: Release 10.2.0.1.0 - Production on 화요일, 29 5월, 2007 19:41:02 Copyright (c) 2003, 2005, Oracle. All rights reserved. 접속 대상: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the OLAP and Data Mining options 마스터 테이블 "SYSTEM"."DATA_IMPORT"이(가) 성공적으로 로드됨/로드 취소됨 "SYSTEM"."DATA_IMPORT" 시작 중: system/******** dumpfile=full.dmp directory= dump job_name=data_impo rt logfile=table_log tables=scott.dept 객체 유형 DATABASE_EXPORT/SCHEMA/TABLE/TABLE 처리 중 ORA-39151: "SCOTT"."DEPT" 테이블이 존재합니다. 건너 뛰기 table_exists_action으로 인해 모든 종속 메타 데이터 및 데이터를 건너 뜁니다. 객체 유형 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA 처리 중 객체 유형 DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS 처리 중 "SYSTEM"."DATA_IMPORT" 작업이 1 오류와 함께 19:41:09에서 완료됨 * 임포트 프로세스의 기본 작업 방식은 테이블 및 연관된 모든 객체를 생성하고 테이블이 있는 상태에서 오류를 만들어 낸다.

 

* 임포트 프로세스의 기본 작업 방식은 테이블 및 연관된 모든 객체를 생성하고 테이블이 있는 상태에서 오류를 만들어 낸다.

 

 

다음 명령은 대화형 모드에서 적합합니다.
참고: 약어도 허용됨

 

 

 

다음 명령은 대화형 모드에서 적합합니다.
참고: 약어도 허용됨

 

 

 

출처 : GOODUS

제공 : DB포탈사이트 DBguide.net

orapwd를 password와 sys user의 password가 일치해야 접속이 가능합니다.

 

0. DB startup 상태

1. sqlnet.ora에 다음을 주석 처리
   % vi $ORACLE_HOME/network/admin/sqlnet.ora
       #SQLNET.AUTHENTICATION_SERVICES = (NONE)

 

2. 오라클 alter user 명령으로 설정
    SQL> connect / as sysdba
    SQL> alter user sys identified by change_on_install;

 

3. orapwd unix command 명령으로 설정
    % mv orapwDBTEST orapwDBTEST.old
    % orapwd file=orapwDBTEST password=change_on_install entries=5

 

4. initDBTEST.ora file에 다음을 설정
    % vi $ORACLE_HOME/dbs/initDBTEST.ora
        remote_login_passwordfile=exclusive

    # spfile을 사용할 경우
   SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

 

5. sqlnet.ora에 주석처리했던 내용을 풀어 줌.
  % vi $ORACLE_HOME/network/admin/sqlnet.ora
     SQLNET.AUTHENTICATION_SERVICES = (NONE)

 

6. DB Shutdown/startup

인덱스 정보 보기
select a.index_name,b.table_name,column_name,COLUMN_POSITION,UNIQUENESS,tablespace_name,table_owner
from ind a, USER_IND_COLUMNS b
where a.index_name=b.index_name
order by a.index_name,COLUMN_POSITION

Remove the following directories from your filesystem:
<ORACLE_HOME>/<hostname_sid>
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>

sys로 접속하셔서
SQL> drop user sysman cascade;
SQL> drop role MGMT_USER;
SQL> drop user MGMT_VIEW cascade;
SQL> drop public synonym MGMT_TARGET_BLACKOUTS;
SQL> drop public synonym SETEMVIEWUSERCONTEXT;

emca -config dbcontrol db -repos create

딕셔너리뷰에서
USER_SEGMENTS 에서 확인할수 있습니다.

SELECT SEGMENT_NAME, BYTES FROM USER_SEGMENTS where SEGMENT_TYPE=''TABLE''
에서 점유용량을 확인할수 있습니다.

//////////////////////////////////////////////////////////////////////////////////

위 delri님의 방법은 해당 Table에 할당되어 있는 크기를 측정하는 방법이며
만일 Table에 공간 할당은 되었으나 아직 (전혀)사용되지 않은 공간을 제외한 실
제 사용되고 있는
공간을 확인하실려면(즉 HWM 아래 공간)...

1. 해당 table을 analyze 해서

analyze table t_name compute statistics;

2. 해당 table의 blocks를 구한다.

select blocks from dba_tables where table_name =''T_NAME'';

==> blocks +1(segment header block) 이 해당 table이 사용중인 db block 갯수


위 계산에서 물론 사용된 후 delete에 의해 현재 완전히 비어 있는 block도 사용
고 있는 block으로 인식
하므로 완전히 비어 있는 block을 제거하기 위해서는
oracle8 이상인 경우 rowid는 아래처럼 구성되어 있기때문에

object fil block row
------ --- ------ ---
AAABPW AAF AAAAv1 AAA

1. select count(*)
from (select substr(rowid,1,15) from test group by substr(rowid,1,15) );
하면..HWM아래 할당은 되었지만 완전히 비어 있는 block을 빼고 조금이라도 사
용된 block의 갯수만 구함





예제)

1* analyze table costs compute statistics
SQL> /

Table analyzed.

SQL> select blocks from user_tables where table_name=''COSTS'';

BLOCKS
----------
2924 <---HWM 아래 block갯수


1 select count(*)
2 from
3* (select substr(rowid,1,15) from costs group by substr(rowid,1,15))
SQL> /

COUNT(*)
----------
2506 <---HWM 아래 block중 사용중인 block갯수

** 정리하면

Table의 공간(크기)이란 아래 3가지 관점에서 값이 다를수 있습니다.

1. 해당 Table에 할당된 크기
-Table에 할당되었지만 사용중 또는 미사용공간 전부 포함

2. HWM 아래 크기
- 할당된 공간중에서 full table scan시 검색하는 부분까지만의 크기
- 즉 HWM이후 공간은 아직 한번도 사용이 안된 new Block임.

3. HWM 아래중 완전히 비어 있는 block을 제외한 크기
- delete에 의해 block전체가 비어 있는 block을 제외한 크기

//////////////////////////////////////////////////////////////////////////////////

위의 HUMAN21님말대로 USER_SEGMENTS 의 BYTES는 실데이타 공간이 아니라 할당영
역을 나타냅니다.. 빈 블럭도 포함이 되는거죠..
만일 UNUSED 블럭을 제외한 실제 데이타 공간을 구하려면..아래와 같
이 구하시면 될것 같네요

select table_name, GREATEST(4, ceil(num_rows /
((round(((8192 - (ini_trans * 23)) *
((100 - pct_used) /100)) / avg_row_len)))) *
8192)/1024/1024
TableSize_Mbytes
from user_tables


위에서 8192 는 SHOW PARAMETER 의 DB_BLOCK_SIZE 입니다.
위의 문을 정확히 하기위해서는 ANALYZE TABLE은 꼭 해주셔야지 정확한 값이 구해지겠죠..


펌질한곳 : http://otn.oracle.co.kr/forum/forum_open_view.jsp?forum_seqno=20040907205724&forum_cate=ORCL

PCTFREE, PCTUSED

PCTFREE 및 PCTUSED는 DataBase 내부의 공간(Data Block)을 효과적으로 다루기 위해 사용되어 지는 저장 매개변수(Storage Parameter) 이다. 대부분 Data Block 내부의 free space를 다루기 위해서는 PCTFREE와  PCTUSED는 함께 설정 되어 지며 새로운 자료의 Insert나 Data Block 내부에 이미 존재하는 자료의 Update를 위한 두 가지 목적으로 주로 이용된다. PCTFREE의 기본값은 10, PCTUSED의 기본값은 40이다. 만약 CREATE TABLE, CREATE INDEX, CREATE CLUSTER등과 같은 DDL의 사용시 PCTFREE 및 PCTUSED를 적절히 구사 한다면 DataBase의 Performance를 높일수 있을 것이다. PCTFREE의 경우 Data Block에 이미 존재하고 있는 행(Row)에 Update등이 일어 날 경우를 대비하여 예약 시켜놓을 Data Block의 퍼센트를 지정하는 것이며 0~99사이의 정수가 사용 가능 하다. PCTUSED의 경우는 Data Block의  여유공간 퍼센트가 PCTFREE에 도달하는 경우 사용된 공간이 PCTUSED 아래로 떨어지기 전에는 새로운 데이터를 삽입(Insert) 할 수 없다는 것을 가리키는 매개 변수 이다. 예를 들면   PCTFREE와 PCTUSED 값의 합은 100 보다 적거나 같은 정수이어야 한다. 다음의 경우를 보자. 만약 임의의 테이블의 주소 컬럼이 가변길이 100 바이트로 구성 되었다고 가정 하자. 처음에 데이터가 Insert  되면서 100 바이트 중 10바이트를 사용하였지만 나중에 변동이 생겨 테이블의 데이터를 100바이트로 Update해야 한다고 생각을 해 보자. 이런 경우 처럼 나중에 테이블의 컬럼등이 Update될때 이전 보다 훨씬 사이즈가 늘어 날 경우를 대비하여 전체 Data Block에서 몇 퍼센트를 비워 놓을 건지를 지정하는 매개변수가 PCTFREE 인 것이다. 적은 PCTFREE의 경우 변경을 위한 작은 공간을 예약하는 것이며 Insert와 같은 명령 수행시 Data Block을 완전히 채울 수 있으며 큰 PCTFREE경우 나중에 변경을 위해 큰 공간을 예약하며 같은 양의 데이터가 Insert 된다면 적은 PCTFREE보다 많은 Data Block을 요구하게 될것이다.  Oracle 9i의 경우 데이터베이스 생성시 segment관리를 auto 옵션을 이용하여 자동으로 지정한다면 Data Block의 free space관리를 위해 bitmap을 이용 할 것이다. 즉 bitmap block에는 Data Block의 free space 가 얼마인지를 가르키고 있다. 다음의 예를 보자. 만약 PCTFREE가 20이고 PCTUSED가 40이라고 할 때 신규 행(Row)은 free space가 20% 될때 까지 입력 된다. 기존의 행에 수정을 하면 예약 해둔 빈 공간을 사용하게 되며 새로운 행은 사용된 공간이 40% 아래로 되어야만 Insert가 가능하게 된다. 40% 아래로 떨어져 새로운 행을 입력 한다면 80%까지만 입력되고 20%는 Update를 위해 예약을 해 두는 것이다. 만 약 테이블을 설계할 때 빈번한 Update가 발생 될 가능성이 크며 컬럼의 사이즈가 늘어날 확률이 높다면 PCTFREE 매개변수를 크게 하고 PCTUSED 매개변수는 낮게 설정 해야 할 것이다. PCTFREE는 20, PCTUSED는 40 정도가 적당 할 것이다. 또한 어떤 테이블은 읽기 전용이며 테이블의 사이즈가 적당히 크다면 PCTFREE는 낮은 수를 PCTUSED는 큰 수를 지정 하면 될 것 이다. ? 아래 예문은 오라클 9i의 새로운 특징인 auto segment space 관리의 예문이다. 결국 오라클 9i의 경우 automatic segment space management 와 manual segment space management의 두가지 방법을 제공함을 알 수 있다. SQL> connect / as sysdba 연결되었습니다. SQL> create tablespace auto   2  datafile 'C:\oracle\oradata\wink\auto01.dbf'   3  size 10m   4  segment space management auto; 테이블 영역이 생성되었습니다. ? TableSpace에 관한 정보를 제공해 주는 뷰를 통해 내용을 확인 하자. SQL> select TABLESPACE_NAME,   2         SEGMENT_SPACE_MANAGEMENT   3  from dba_tablespaces; TABLESPACE_NAME                SEGMEN ------------------------------ ------ SYSTEM                         MANUAL TEST                           MANUAL AUTO                           AUTO …. ? 아래 예문은 create table 명령이다. 지금까지 보았던 것과는 약간 다른 모습 이다. pctfree, pctused를 사용 했으며 , 저장 매개변수를 지정 하였다. 저장 매개 변수는 6.2절에 자세히 나와 있으니 참고 하기 바란다. SQL> connect scott/tiger SQL> create table myAddrBook (   2     id number(5) not null primary key,   3     name varchar2(20) not null,   4     address varchar2(100)   5  )   6  pctfree 10   7  pctused 50   8  tablespace users   9  storage ( 10     initial 50k 11     next 50k 12     maxextents 10 13     pctincrease 25); 테이블이 생성되었습니다.