# 해당 팁은 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);
