{"id":803,"date":"2015-12-20T09:33:54","date_gmt":"2015-12-20T09:33:54","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=803"},"modified":"2018-08-06T07:39:10","modified_gmt":"2018-08-05T22:39:10","slug":"dbmsmetadata-%ed%8c%a8%ed%82%a4%ec%a7%80%eb%a5%bc-%ec%9d%b4%ec%9a%a9%ed%95%9c-user-tablespace-%eb%a7%8c%eb%93%a4%ea%b8%b0ddl%eb%ac%b8-%ec%b6%94%ec%b6%9c","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=803","title":{"rendered":"DBMS_METADATA \ud328\ud0a4\uc9c0\ub97c \uc774\uc6a9\ud55c User, Tablespace \ub9cc\ub4e4\uae30(DDL\ubb38 \ucd94\ucd9c)"},"content":{"rendered":"

# \ud574\ub2f9 \ud301\uc740 DB \ub9c8\uc774\uadf8\ub808\uc774\uc158 \uc2dc \uc720\uc6a9\ud558\uac8c \uc4f8 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uc720\uc800 \ubc0f Tablespace\uc758 \uc0dd\uc131\ubb38 \ucd94\ucd9c\uc744 \ub3c4\uc640\uc90d\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

set pages 10000<\/p>\n

set long 99999<\/p>\n

set heading off<\/p>\n

set linesize 200<\/p>\n

set feedback off<\/p>\n

col ddl format a200<\/p>\n

 <\/p>\n

1. User \uc0dd\uc131\ubb38 \ucd94\ucd9c<\/p>\n

SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,username ) from dba_users;<\/p>\n

CREATE USER “DBSNMP” IDENTIFIED BY VALUES ‘S:6BFD54544426B96E4C4CB1C81DE86067752E0ABA27B060A9567AB78C21B3;FFF45BB2C0C327EC’<\/p>\n

DEFAULT TABLESPACE “SYSAUX”<\/p>\n

TEMPORARY TABLESPACE “TEMP”<\/p>\n

PROFILE “MONITORING_PROFILE”<\/p>\n

 <\/p>\n

 <\/p>\n

CREATE USER “SCOTT” IDENTIFIED BY VALUES ‘S:11881BD808FADF48F989F2EFF30E0E8274E82FBA61843388AA25DD951F67;F894844C34402B67’<\/p>\n

DEFAULT TABLESPACE “USERS”<\/p>\n

TEMPORARY TABLESPACE “TEMP”<\/p>\n

 <\/p>\n

 <\/p>\n

CREATE USER “HR” IDENTIFIED BY VALUES ‘S:25D0899E13C6808D7DE80DE9C4D985A177DF8AAE78955FE47234CAE99C67;4C6D73C3E8B0F0DA’<\/p>\n

DEFAULT TABLESPACE “USERS”<\/p>\n

TEMPORARY TABLESPACE “TEMP”<\/p>\n

 <\/p>\n

 <\/p>\n

CREATE USER “SYSMAN” IDENTIFIED BY VALUES ‘S:EFB80C2B3BB52B19E9F89632B415FFD6F0CDD3D3A06A3ACB6AB41DF8389F;2CA614501F09FCCC’<\/p>\n

DEFAULT TABLESPACE “SYSAUX”<\/p>\n

TEMPORARY TABLESPACE “TEMP”<\/p>\n

 <\/p>\n

….<\/p>\n

 <\/p>\n

2. User\uc5d0 \ub300\ud55c \uad8c\ud55c \ucd94\ucd9c<\/p>\n

SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,username ) from dba_users;<\/div>\n
<\/div>\n
\n
\u00a0 \u00a0GRANT “MGMT_USER” TO “MGMT_VIEW”<\/div>\n
\u00a0 \u00a0GRANT “CONNECT” TO “SYS” WITH ADMIN OPTION<\/div>\n
\u00a0 \u00a0GRANT “RESOURCE” TO “SYS” WITH ADMIN OPTION<\/div>\n
\u00a0 \u00a0GRANT “DBA” TO “SYS” WITH ADMIN OPTION<\/div>\n
\u00a0 \u00a0GRANT “SELECT_CATALOG_ROLE” TO “SYS” WITH ADMIN OPTION<\/div>\n
\u00a0 \u00a0GRANT “EXECUTE_CATALOG_ROLE” TO “SYS” WITH ADMIN OPTION<\/div>\n
\u00a0 \u00a0GRANT “DELETE_CATALOG_ROLE” TO “SYS” WITH ADMIN OPTION<\/div>\n
\u00a0 \u00a0GRANT “EXP_FULL_DATABASE” TO “SYS” WITH ADMIN OPTION<\/div>\n
\u00a0 \u00a0GRANT “IMP_FULL_DATABASE” TO “SYS” WITH ADMIN OPTION<\/div>\n<\/div>\n
<\/div>\n
….<\/div>\n
<\/div>\n
3. Tablespace \uc0dd\uc131\ubb38 \ucd94\ucd9c<\/div>\n
select dbms_metadata.get_ddl(‘TABLESPACE’, tablespace_name) from dba_tablespaces;<\/div>\n
<\/div>\n
\n
\u00a0 CREATE TABLESPACE “SYSTEM” DATAFILE<\/div>\n
\u00a0 SIZE 524288000<\/div>\n
\u00a0 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M<\/div>\n
\u00a0 LOGGING ONLINE PERMANENT BLOCKSIZE 8192<\/div>\n
\u00a0 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT<\/div>\n
\u00a0NOCOMPRESS \u00a0SEGMENT SPACE MANAGEMENT MANUAL<\/div>\n
\u00a0 \u00a0ALTER DATABASE DATAFILE<\/div>\n
\u00a0 ‘+DATA\/orcl\/datafile\/system.256.823945567’ RESIZE 744488960<\/div>\n
<\/div>\n
<\/div>\n
\u00a0 CREATE TABLESPACE “SYSAUX” DATAFILE<\/div>\n
\u00a0 SIZE 419430400<\/div>\n
\u00a0 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M<\/div>\n
\u00a0 LOGGING ONLINE PERMANENT BLOCKSIZE 8192<\/div>\n
\u00a0 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT<\/div>\n
\u00a0NOCOMPRESS \u00a0SEGMENT SPACE MANAGEMENT AUTO<\/div>\n
\u00a0 \u00a0ALTER DATABASE DATAFILE<\/div>\n
\u00a0 ‘+DATA\/orcl\/datafile\/sysaux.257.823945567’ RESIZE 576716800<\/div>\n<\/div>\n
….<\/div>\n
<\/div>\n
<\/div>\n
Tablespace \uac19\uc740 \uacbd\uc6b0\ub294 Storage \ubd80\ubd84, Segment \uc815\ubcf4\uc5d0 \ub300\ud55c \ud45c\uc2dc\ub294 \uc544\ub798 \uad6c\ubb38\uc744 \uc2e4\ud589\ud574\uc57c \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/div>\n
\n

\u00a0<\/span><\/p>\n

STORAGE <\/span>\ubd80\ubd84<\/span> \ud45c\uc2dc<\/span><\/p>\n

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘STORAGE’, true);<\/span><\/p>\n

 <\/p>\n

SEGMENT <\/span>\uad00\ub828<\/span> \ubd80\ubd84<\/span> \ud45c\uc2dc<\/span><\/p>\n

\u00a0exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘SEGMENT_ATTRIBUTES’, true);<\/span><\/p>\n

\u00a0<\/span><\/p>\n

\u00a0<\/span><\/p>\n

\n<\/div>\n

 <\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

# \ud574\ub2f9 \ud301\uc740 DB \ub9c8\uc774\uadf8\ub808\uc774\uc158 \uc2dc \uc720\uc6a9\ud558\uac8c \uc4f8 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uc720\uc800 \ubc0f Tablespace\uc758 \uc0dd\uc131\ubb38 \ucd94\ucd9c\uc744 \ub3c4\uc640\uc90d\ub2c8\ub2e4.   set pages 10000 set long 99999 set heading off set linesize 200 set feedback off col ddl format a200   1. User \uc0dd\uc131\ubb38 \ucd94\ucd9c SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,username ) from dba_users; CREATE USER “DBSNMP” IDENTIFIED BY VALUES ‘S:6BFD54544426B96E4C4CB1C81DE86067752E0ABA27B060A9567AB78C21B3;FFF45BB2C0C327EC’ DEFAULT TABLESPACE […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[10],"tags":[],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/803"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=803"}],"version-history":[{"count":2,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/803\/revisions"}],"predecessor-version":[{"id":4089,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/803\/revisions\/4089"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=803"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=803"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=803"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}