当前位置:操作系统 > Unix/Linux >>

dbms_metadata.get_ddl学习

dbms_metadata.get_ddl学习
 
dbms_metadata一些用途测试学习
 
查看建立表空间语句
 
SQL> set head off
SQL> set pages 0
SQL> set long 9999999
SQL> select dbms_metadata.get_ddl('TABLESPACE','TEST') from dual;
 
  CREATE TABLESPACE "TEST" DATAFILE
  '/home/oracle/TEST/TEST.dbf' SIZE 52428800
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL TESTALLOCATE SEGMENT SPACE MANAGEMENT MANUAL
   ALTER DATABASE DATAFILE
  '/home/oracle/TEST/TEST.dbf' RESIZE 2097152000
 
查看建立用户语句
 
 
SQL> select dbms_metadata.get_ddl('USER','TEST') from dual;
 
   CREATE USER "TEST" IDENTIFIED BY VALUES '90CEB80324B4BC3D'
      DEFAULT TABLESPACE "TEST"
      TEMPORARY TABLESPACE "TEMP"    
 
查看一个用户的授权相关信息
 
SQL>     SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','TEST') from dual;
 
   GRANT "CONNECT" TO "TEST"
   GRANT "RESOURCE" TO "TEST"
 
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','TEST') from dual;
 
  GRANT CREATE ANY SYNONYM TO "TEST"
  GRANT CREATE SYNONYM TO "TEST"
  GRANT SELECT ANY TABLE TO "TEST"
  GRANT LOCK ANY TABLE TO "TEST"
  GRANT DROP ANY TABLE TO "TEST"
  GRANT ALTER ANY TABLE TO "TEST"
  GRANT CREATE ANY TABLE TO "TEST"
  GRANT UNLIMITED TABLESPACE TO "TEST"
  GRANT CREATE SESSION TO "TEST"
    
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','TEST') from dual;
 
  GRANT EXECUTE ON "SYS"."DBMS_REDEFINITION" TO "TEST"
  GRANT DEBUG ON "SYS"."DBMS_REDEFINITION" TO "TEST"
  GRANT EXECUTE ON "SYS"."DBMS_REPCAT_INTERNAL_PACKAGE" TO "TEST"
 
查看当前系统的所有db_link
 
SQL> SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) FROM all_db_links a;
 
  CREATE DATABASE LINK "TEST1.COM"
   CONNECT TO "REPADMIN" IDENTIFIED BY VALUES '05CC75BB7C740556460505AC8391AE8D3
93717DAB3FFDB28DB'
   USING 'TEST1'
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,