DBMS_METADATA 패키지를 이용한 User, Tablespace 만들기(DDL문 추출)


# 해당 팁은 DB 마이그레이션 시 유용하게 쓸 수 있습니다. 유저 및 Tablespace의 생성문 추출을 도와줍니다.

 

set pages 10000

set long 99999

set heading off

set linesize 200

set feedback off

col ddl format a200

 

1. User 생성문 추출

SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,username ) from dba_users;

CREATE USER “DBSNMP” IDENTIFIED BY VALUES ‘S:6BFD54544426B96E4C4CB1C81DE86067752E0ABA27B060A9567AB78C21B3;FFF45BB2C0C327EC’

DEFAULT TABLESPACE “SYSAUX”

TEMPORARY TABLESPACE “TEMP”

PROFILE “MONITORING_PROFILE”

 

 

CREATE USER “SCOTT” IDENTIFIED BY VALUES ‘S:11881BD808FADF48F989F2EFF30E0E8274E82FBA61843388AA25DD951F67;F894844C34402B67’

DEFAULT TABLESPACE “USERS”

TEMPORARY TABLESPACE “TEMP”

 

 

CREATE USER “HR” IDENTIFIED BY VALUES ‘S:25D0899E13C6808D7DE80DE9C4D985A177DF8AAE78955FE47234CAE99C67;4C6D73C3E8B0F0DA’

DEFAULT TABLESPACE “USERS”

TEMPORARY TABLESPACE “TEMP”

 

 

CREATE USER “SYSMAN” IDENTIFIED BY VALUES ‘S:EFB80C2B3BB52B19E9F89632B415FFD6F0CDD3D3A06A3ACB6AB41DF8389F;2CA614501F09FCCC’

DEFAULT TABLESPACE “SYSAUX”

TEMPORARY TABLESPACE “TEMP”

 

….

 

2. User에 대한 권한 추출

SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,username ) from dba_users;
   GRANT “MGMT_USER” TO “MGMT_VIEW”
   GRANT “CONNECT” TO “SYS” WITH ADMIN OPTION
   GRANT “RESOURCE” TO “SYS” WITH ADMIN OPTION
   GRANT “DBA” TO “SYS” WITH ADMIN OPTION
   GRANT “SELECT_CATALOG_ROLE” TO “SYS” WITH ADMIN OPTION
   GRANT “EXECUTE_CATALOG_ROLE” TO “SYS” WITH ADMIN OPTION
   GRANT “DELETE_CATALOG_ROLE” TO “SYS” WITH ADMIN OPTION
   GRANT “EXP_FULL_DATABASE” TO “SYS” WITH ADMIN OPTION
   GRANT “IMP_FULL_DATABASE” TO “SYS” WITH ADMIN OPTION
….
3. Tablespace 생성문 추출
select dbms_metadata.get_ddl(‘TABLESPACE’, tablespace_name) from dba_tablespaces;
  CREATE TABLESPACE “SYSTEM” DATAFILE
  SIZE 524288000
  AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT MANUAL
   ALTER DATABASE DATAFILE
  ‘+DATA/orcl/datafile/system.256.823945567’ RESIZE 744488960
  CREATE TABLESPACE “SYSAUX” DATAFILE
  SIZE 419430400
  AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  ‘+DATA/orcl/datafile/sysaux.257.823945567’ RESIZE 576716800
….
Tablespace 같은 경우는 Storage 부분, Segment 정보에 대한 표시는 아래 구문을 실행해야 볼 수 있습니다.

 

STORAGE 부분 표시

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘STORAGE’, true);

 

SEGMENT 관련 부분 표시

 exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘SEGMENT_ATTRIBUTES’, true);

 

 

 

 


Comments

comments

haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다