Connect Database and Execute with user DBA privilege<\/p>\n\n\n\n
Put username in uppercase<\/strong><\/p>\n\n\n\n References<\/strong><\/p>\n\n\n\n NOTE:207859.1<\/a> – Object types for DBMS_METADATA Connect Database and Execute with user DBA privilege Put username in uppercase References NOTE:207859.1 – Object types for DBMS_METADATANOTE:1460969.1 – How To Retrieve The Full Object Definition (DDL) From The DatabaseNOTE:1163383.1 – The dbms_metadata.get_granted_ddl Does Not Extract all System Privileges<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"aside","meta":{"footnotes":""},"categories":[8],"tags":[95,403,402,401,400],"class_list":["post-1214","post","type-post","status-publish","format-aside","hentry","category-oracle","tag-dbms_metadata-get_ddl","tag-dbms_metadata-get_granted_ddl","tag-note1163383-1","tag-note1460969-1","tag-note207859-1","post_format-post-format-aside"],"_links":{"self":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1214","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=1214"}],"version-history":[{"count":2,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1214\/revisions"}],"predecessor-version":[{"id":1216,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/posts\/1214\/revisions\/1216"}],"wp:attachment":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=1214"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=1214"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=1214"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}set longchunksize 20000 pagesize 0 feedback off verify off trimspool on\ncolumn Extracted_DDL format a1000\n\nEXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);\nEXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);\n\nundefine User_in_Uppercase;\n\nset linesize 1000\nset long 2000000000\nselect (case\nwhen ((select count(*)\nfrom dba_users\nwhere username = '&&User_in_Uppercase' and profile <> 'DEFAULT') > 0)\nthen chr(10)||' -- Note: Profile'||(select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl from dba_users u where u.username = '&User_in_Uppercase')\nelse to_clob (chr(10)||' -- Note: Default profile, no need to create!')\nend ) from dual\nUNION ALL\nselect (case\nwhen ((select count(*)\nfrom dba_users\nwhere username = '&User_in_Uppercase') > 0)\nthen ' -- Note: Create user statement'||dbms_metadata.get_ddl ('USER', '&User_in_Uppercase')\nelse to_clob (chr(10)||' -- Note: User not found!')\nend ) Extracted_DDL from dual\nUNION ALL\nselect (case\nwhen ((select count(*)\nfrom dba_ts_quotas\nwhere username = '&User_in_Uppercase') > 0)\nthen ' -- Note: TBS quota'||dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&User_in_Uppercase')\nelse to_clob (chr(10)||' -- Note: No TS Quotas found!')\nend ) from dual\nUNION ALL\nselect (case\nwhen ((select count(*)\nfrom dba_role_privs\nwhere grantee = '&User_in_Uppercase') > 0)\nthen ' -- Note: Roles'||dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&User_in_Uppercase')\nelse to_clob (chr(10)||' -- Note: No granted Roles found!')\nend ) from dual\nUNION ALL\nselect (case\nwhen ((select count(*)\nfrom V$PWFILE_USERS\nwhere username = '&User_in_Uppercase' and SYSDBA='TRUE') > 0)\nthen ' -- Note: sysdba'||chr(10)||to_clob (' GRANT SYSDBA TO '||'\"'||'&User_in_Uppercase'||'\"'||';')\nelse to_clob (chr(10)||' -- Note: No sysdba administrative Privilege found!')\nend ) from dual\nUNION ALL\nselect (case\nwhen ((select count(*)\nfrom dba_sys_privs\nwhere grantee = '&User_in_Uppercase') > 0)\nthen ' -- Note: System Privileges'||dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&User_in_Uppercase')\nelse to_clob (chr(10)||' -- Note: No System Privileges found!')\nend ) from dual\nUNION ALL\nselect (case\nwhen ((select count(*)\nfrom dba_tab_privs\nwhere grantee = '&User_in_Uppercase') > 0)\nthen ' -- Note: Object Privileges'||dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&User_in_Uppercase')\nelse to_clob (chr(10)||' -- Note: No Object Privileges found!')\nend ) from dual\n\/<\/pre><\/div>\n\n\n\n
NOTE:1460969.1<\/a> – How To Retrieve The Full Object Definition (DDL) From The Database
NOTE:1163383.1<\/a> – The dbms_metadata.get_granted_ddl Does Not Extract all System Privileges<\/p>\n","protected":false},"excerpt":{"rendered":"