SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL; 查看创建用户索引的SQLITPUB个人空间n-~cpr P"Pe2Z9A 查看所需表的索引 G#@R(FI j1y_0SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME from user_indexes WHERE table_name='EMP';ITPUB个人空间+n t n;D2d~!R]Xq0P 查看当前...
1)获得表、索引、视图、存储过程、函数的DDL select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual; select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual; select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual; select dbms_...
select dbms_metadata.get_ddl('SEQUENCE','SEQ_ID') from dual; 2.查看表的定义 select dbms_metadata.get_ddl('TABLE','TABLENAME','USERNAME') from dual; 3.查看用户的约束定义 select dbms_metadata.get_ddl('CONSTRAINT','CONSTRAINTNAME','USERNAME') from dual; 4.查看外键定义 select dbms_metadata...
Oracle 在9i以后,可以利用DBMS_METADATA.GET_DDL包得到数据库的对象的ddl脚本。如下(SQLPLUS中执行): 1. 获取单个的建表、视图和建索引的语法set pagesize 0set long 90000set feedback offset echo offspool DEPT.sqlselect dbms_metadata.get_ddl('TABLE','TAB_NAME','SCOTT') from dual; select dbms_...
(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); ---去除 storage 等多余参数 SELECT DBMS_METADATA.GET_DDL('TABLE','TCC_NE_FRAME') FROM DUAL; SELECT DBMS_METADATA.GET_DDL('TABLE','TCC_NE_SNAP') FROM DUAL; 2.得到一个用户下的所有表, 索引, 存储过程的 ddl SET SERVEROUTPUT ON SET ...
CREATETABLEpublic."BIG_t"("BIG_a"int, "BIG_b" text); 不同参数的大小写敏感。 对象类型大小写不敏感:table/TABLE均可。 table SELECTdbms_metadata.get_ddl('table','BIG_t','public'); 返回结果如下: get_ddl---CREATETABLEIFNOTEXISTSpublic."BIG_t" (+"BIG_a"integer,+"BIG_b" textCOLLATE...
SQL> select dbms_metadata.get_ddl(‘TABLE’,‘TABLENAME’,‘USERNAME’) from dual; ERROR: ORA-19206: Invalid value for query or REF CURSOR parameter ORA-06512: at “SYS.DBMS_XMLGEN”, line 83 ORA-06512: at “SYS.DBMS_METADATA”, line 345 ...
SQL> select dbms_metadata.get_ddl('REF_CONSTRAINT','FK_DEPTNO') from dual; ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE 对象表空间 select dbms_metadata.get_ddl('TABLESPACE','TABLESPACE_NAME') from dual ...
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" ...
该语法使用 `dbms_metadata.get_ddl` 函数来获取指定表和索引的 DDL 定义,其中 `dbms_metadata.get_ddl` 函数的三个参数分别是对象类型、对象名和用户名。在上面的例子中,第一个参数是 `TABLE`,表示获取表的 DDL ... Oracle储存过程包加解密实现 解密过程与之类似,先用`DBMS_METADATA.GET_DDL`获取加密后...