如何在Oracle中使用DBMS

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

DBMS_METADATA.GET_DDL

Oracle数据库有DBMS_METADATA包，它提供了一种从数据库字典中检索元数据的方法。

Get DDL ( Create Script ) DBMS_METADATA.GET_DDL

DBMS_METADATA.GET_DDL 的语法如下。

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

DBMS_METADATA – User Create Script

可以按如下方式获取任何用户（Schema）的 DDL（创建脚本）

select dbms_metadata.get_ddl('USER', du.username) AS DDL_SCRIPT
from dba_users du
where du.username = 'TYPE_USER_NAME'
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', dtq.username) AS DDL_SCRIPT
from dba_ts_quotas dtq
where dtq.username = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', dsp.grantee) AS DDL_SCRIPT
from dba_sys_privs dsp
where dsp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', dtp.grantee) AS DDL_SCRIPT
from dba_tab_privs dtp
where dtp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'TYPE_USER_NAME'
and drp.default_role = 'YES'
and rownum = 1;

GET_DDL

For example: You can get DDL of SCOTT user as follows.

SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('USER', du.username) AS DDL_SCRIPT
from dba_users du
where du.username = 'SCOTT'
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', dtq.username) AS DDL_SCRIPT
from dba_ts_quotas dtq
where dtq.username = 'SCOTT'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'SCOTT'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', dsp.grantee) AS DDL_SCRIPT
from dba_sys_privs dsp
where dsp.grantee = 'SCOTT'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', dtp.grantee) AS DDL_SCRIPT
from dba_tab_privs dtp
where dtp.grantee = 'SCOTT'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'SCOTT'
and drp.default_role = 'YES'
and rownum = 1;

CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:BE2891F45D57E8E7897914A39E21F4B3D7F9F23D72E8E0E65DF1F51C2262;435E3EB39C74D939' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"

   GRANT "CONNECT" TO "SCOTT"
   GRANT "RESOURCE" TO "SCOTT"
   GRANT "DBA" TO "SCOTT"

   GRANT CREATE TABLE TO "SCOTT"
   GRANT UNLIMITED TABLESPACE TO "SCOTT"

   GRANT SELECT ON "SYS"."DBA_USERS" TO "SCOTT"
   GRANT INSERT ON "SYS"."TEST402" TO "SCOTT"
   GRANT UPDATE ON "SYS"."TEST402" TO "SCOTT"
   GRANT SELECT ON "SYS"."TEST402" TO "SCOTT"
   GRANT DELETE ON "SYS"."TEST402" TO "SCOTT"

   ALTER USER "SCOTT" DEFAULT ROLE ALL;

SQL>

Get DDL of a Table ( Table Create Script )

可以按如下方式获取表的创建脚本

select dbms_metadata.get_ddl( 'TABLE', 'TABLE_NAME','SCHEMA_NAME' ) from dual;

For example; You can get SCOTT.EMP table create script as follows.

select dbms_metadata.get_ddl('TABLE', 'EMP','SCOTT') from dual;

SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('TABLE', 'EMP','SCOTT') from dual;

CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARALLEL

SQL>

还可以按如下方式获取所有表或任何用户模型下表的创建脚本。

SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner)
FROM dba_tables
WHERE owner = UPPER('&1');

Index Create Script

You can get DDL ( Create Script ) of any index as follows.

select dbms_metadata.get_ddl( 'INDEX', 'INDEX_NAME','SCHEMA_NAME' ) from dual;

For example; You can get HR.EMP_DEPARTMENT_IX index create script as follows.

SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('INDEX','EMP_DEPARTMENT_IX','HR') from dual;

CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES" ("DEPARTMENT_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS";

SQL>

You can get all indexes get ddl ( create script ) as follows.

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE ='NORMAL';

Tablespace Create Script

You can generate all tablespaces get ddl ( create script ) as follows.

set head off echo off
select 'select dbms_metadata.get_ddl(''TABLESPACE'','''
|| tablespace_name || ''') from dual;' from dba_tablespaces;

You can get the Users tablespace get ddl ( create script ) as follows.

SQL> select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;

CREATE TABLESPACE "USERS" DATAFILE
  '/u01/app/oracle/oradata/orcl/users01.dbf' SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  '/u01/app/oracle/oradata/orcl/users01.dbf' RESIZE 85196800;

DBMS_METADATA.GET_DDL VIEW, FUNCTION,PACKAGE

You can get all views get ddl ( create script ) as follows.

SELECT DBMS_METADATA.GET_DDL('VIEW',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE = 'VIEW' AND OWNER='USER_NAME';

You can get all functions, procedures get ddl ( create script ) as follows.

SELECT DBMS_METADATA.GET_DDL('PROCEDURE',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE = 'PROCEDURE' AND OWNER='USER_NAME';
SELECT DBMS_METADATA.GET_DDL('FUNCTION',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U WHERE U.OBJECT_TYPE = 'FUNCTION'AND OWNER='USER_NAME';

You can get any package’s get ddl ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','OBJECT_NAME','SCHEMA_NAME') FROM DUAL;

You can get any package’s body get ddl ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','OBJECT_NAME','SCHEMA_NAME') FROM DUAL;

You can get any constraint’s get ddl ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','OBJECT_NAME','SCHEMA_NAME') from dual;

You can get any user’s system grant ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCHEMA_NAME') from dual;

You can get any user’s role grant ( create script ) as follows.

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCHEMA_NAME') from dual;

上述sql脚本，可根据实际需求进行调整，还有部分其他用法此处未完全罗列，使用者可利用搜索引擎自行检索。

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

标签: oracle

返回列表

上一篇：Centos系统常见配置（详细）总结

下一篇：固定ip

“如何在Oracle中使用DBMS” 的相关文章

Oracle/MySQL/PostgreSQL的MVCC实现及redo/undo/binlog相关概念1年前 (2023-02-02)

静默安装oracle 12c时报错INS-351781年前 (2023-02-02)

oracle在线增加redo日志组成员1年前 (2023-02-07)

SQL Developer的下载、安装和连接Oracle数据库1年前 (2023-02-08)

Oracle数据库入门教程（作者原创）_oracle数据库1年前 (2023-02-10)

Oracle官方文档导读1年前 (2023-03-10)

LINUX下安装Oracle监听报错Error: null 1年前 (2023-03-11)

【大数据实时数据同步】超级详细的生产环境OGG(GoldenGate)12.2实时异构同步Oracle数据部署方案(上)1年前 (2023-03-24)

Oracle数据库中没有scott账户的方法1年前 (2023-03-24)

Oracle数据库 insert 插入数据显示问号乱码的解决办法1年前 (2023-03-24)