获取对象定义的包为：dbms_metadata，其中的get_ddl函数是获取对象的函数 GET_DDL函数返回创建对象的原数据的DDL语句，参数说明 1、object_type ---需要返回原数据的DDL语句的对象类型 2、name --- 对象名称 3、schema ---对象所在的Schema，默认为当前用户所在所Schema 4、version ---对象原数据的版本 5、model ---原数据的类型默认为ORACLE 6、transform. - XSL-T transform. to be applied. 7、RETURNS: 对象的原数据默认以CLOB类型返回 其中，我们经常用到的是前三项。 dbms_metadata包中的get_ddl函数定义： FUNCTION 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; 注意： 1、如果使用sqlplus需要进行下列格式化，特别需要对long进行设置，否则无法显示完整的SQL 2、参数要使用大写，否则会查不到 set linesize 180 set pages 999 set long 1、查看数据库表的定义写法： select dbms_metadata.get_ddl('TABLE','TABLENAME','USERNAME') from dual; 2、查看索引的SQL select dbms_metadata.get_ddl('INDEX','INDEXNAME','USERNAME') from dual; 3、查看创建主键的SQL SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','CONSTRAINTNAME','USERNAME') FROM DUAL; 4、查看创建外键的SQL SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','REF_CONSTRAINTNAME','USERNAME') FROM DUAL; 5、查看创建视图的SQL SELECT DBMS_METADATA.GET_DDL('VIEW','VIEWNAME','USERNAME') FROM DUAL; 6、查看用户的SQL SELECT DBMS_METADATA.GET_DDL('USER','USERNAME') FROM DUAL; 7、查看角色的SQL SELECT DBMS_METADATA.GET_DDL('ROLE','ROLENAME') FROM DUAL; 8、查看表空间的SQL SELECT DBMS_METADATA.GET_DDL('TABLESPACE','TABLESPACENAME') FROM DUAL; 9、获取物化视图SQL select dbms_metadata.get_ddl('MATERIALIZED VIEW','MVNAME') FROM DUAL; 10、获取远程连接定义SQL SELECT dbms_metadata.get_ddl('DB_LINK','DBLINKNAME','USERNAME') stmt FROM dual 11、获取用户下的触发器SQL select DBMS_METADATA.GET_DDL('TRIGGER','TRIGGERNAME','USERNAME) FROM DUAL; 或 SELECT l.owner, l.db_link, l.username, dbms_metadata.get_ddl('DB_LINK', l.db_link, l.owner) stmt FROM dba_db_links l 12、获取用户下的序列 select DBMS_METADATA.GET_DDL('SEQUENCE','SEQUENCENAME') from DUAL; 13、获取用户下的函数 select DBMS_METADATA.GET_DDL('FUNCTION','FUNCTIONNAME','USERNAME') from DUAL 14、获取包的定义 select DBMS_METADATA.GET_DDL('PACKAGE','PACKAGENAME','USERNAME') from dual 15、获取存储过程 select DBMS_METADATA.GET_DDL('PROCEDURE','PROCEDURENAME','USERNAME') from dual 16、获取包体定义 select DBMS_METADATA.GET_DDL('PACKAGE BODY','PACKAGEBODYNAME','USERNAME') from dual 17、获取远程数据库对象的定义 SELECT DBMS_LOB.SUBSTR@dblinkname(DBMS_METADATA.GET_DDL@dblinkname('TABLE', 'TABLENAME', 'USERNAME')) FROM DUAL@dblinkname 18、获取多个对象的定义 SELECT DBMS_METADATA.GET_DDL(O.OBJECT_TYPE, O.object_name,O.OWNER) FROM DBA_OBJECTS O where O.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE','FUNCTION') and ONWER = 'ONWERNAME'; DDL生成对象创建语句
select j.SCHEMA_USER, 'declare ' || ' n_syn_points number; '|| ' begin '|| ' dbms_job.submit(n_syn_points,'|| ''''||j.what||''''||','|| ' sysdate,'|| ''''||j.interval||''''||');'|| ' commit; '|| ' end; ' as ss from dba_jobs j where j.LOG_USER not in ('SYS') and j.SCHEMA_USER='ZHAOPIN' ; select 'exec dbms_job.remove(' || job || ');' as ss,s.* from dba_jobs s where s.log_user = 'LCC' DDL生成创建Job或删除Job语句
---查询表空间 select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB", round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used" from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name order by ((a.bytes-b.bytes)/a.bytes) desc; ---生成添加数据文件SQL select 'ALTER TABLESPACE '||f.tablespace_name||' ADD DATAFILE ' ||''''||f.FILE_NAME ||''''||' SIZE 10G;' stras,f.* from dba_data_files f where f.TABLESPACE_NAME='SMARTDATA_HISTORY' ; DDL生成表空间添加文件SQL
select 'drop TYPE '||o.owner||'.'||o.object_name ||';'||chr(13)||chr(10) from dba_objects o where o.owner in ('HRONLINE', 'ETS', 'LHC', 'BACKUPUSER', 'CAREER_DMOLVIEW', 'ASSEREAD', 'NORMREAD', 'ASSE', 'ZHAOPIN', 'CAREER', 'PERFSTAT', 'INTERNCAREER', 'HNCAREER', 'HOLONLINE', 'ZTECAREER' ) and o.object_type='TYPE' order by o.created desc ; 01_DDL_DROP_TYPE
select 'drop view ' ||o.owner||'.'||o.object_name||';'||chr(13)||chr(10) from dba_objects o where o.owner in ('HRONLINE', 'ETS', 'LHC', 'BACKUPUSER', 'CAREER_DMOLVIEW', 'ASSEREAD', 'NORMREAD', 'ASSE', 'ZHAOPIN', 'CAREER', 'PERFSTAT', 'INTERNCAREER', 'HNCAREER', 'HOLONLINE', 'ZTECAREER' ) and o.object_type='VIEW' order by o.created desc ; 02_DDL_DROP_VIEW
select 'drop sequence '||o.owner||'.'||o.object_name ||';'||chr(13)||chr(10) from dba_objects o where o.owner in ('HRONLINE', 'ETS', 'LHC', 'BACKUPUSER', 'CAREER_DMOLVIEW', 'ASSEREAD', 'NORMREAD', 'ASSE', 'ZHAOPIN', 'CAREER', 'PERFSTAT', 'INTERNCAREER', 'HNCAREER', 'HOLONLINE', 'ZTECAREER' ) and o.object_type='SEQUENCE' order by o.created desc ; 03_DDL_DROP_SEQUENCE
select 'drop TRIGGER '||o.owner||'.'||o.object_name ||';'||chr(13)||chr(10) from dba_objects o where o.owner in ('HRONLINE', 'ETS', 'LHC', 'BACKUPUSER', 'CAREER_DMOLVIEW', 'ASSEREAD', 'NORMREAD', 'ASSE', 'ZHAOPIN', 'CAREER', 'PERFSTAT', 'INTERNCAREER', 'HNCAREER', 'HOLONLINE', 'ZTECAREER' ) and o.object_type='TRIGGER' order by o.created desc ; 04_DDL_DROP_TRIGGER
select 'drop MATERIALIZED VIEW '||o.owner||'.'||o.object_name ||';'||chr(13)||chr(10) from dba_objects o where o.owner in ('HRONLINE', 'ETS', 'LHC', 'BACKUPUSER', 'CAREER_DMOLVIEW', 'ASSEREAD', 'NORMREAD', 'ASSE', 'ZHAOPIN', 'CAREER', 'PERFSTAT', 'INTERNCAREER', 'HNCAREER', 'HOLONLINE', 'ZTECAREER' ) and o.object_type='MATERIALIZED VIEW' order by o.created desc ; 05_DDL_DROP_MATERIALIZED_VIEW
select 'drop FUNCTION '||o.owner||'.'||o.object_name ||';'||chr(13)||chr(10) from dba_objects o where o.owner in ('HRONLINE', 'ETS', 'LHC', 'BACKUPUSER', 'CAREER_DMOLVIEW', 'ASSEREAD', 'NORMREAD', 'ASSE', 'ZHAOPIN', 'CAREER', 'PERFSTAT', 'INTERNCAREER', 'HNCAREER', 'HOLONLINE', 'ZTECAREER' ) and o.object_type='FUNCTION' order by o.created desc ; 06_DDL_DROP_FUNCTION
select 'drop PROCEDURE '||o.owner||'.'||o.object_name ||';'||chr(13)||chr(10) from dba_objects o where o.owner in ('HRONLINE', 'ETS', 'LHC', 'BACKUPUSER', 'CAREER_DMOLVIEW', 'ASSEREAD', 'NORMREAD', 'ASSE', 'ZHAOPIN', 'CAREER', 'PERFSTAT', 'INTERNCAREER', 'HNCAREER', 'HOLONLINE', 'ZTECAREER' ) and o.object_type='PROCEDURE' order by o.created desc ; 07_DDL_DROP_PROCEDURE
select 'drop PACKAGE '||o.owner||'.'||o.object_name ||';'||chr(13)||chr(10) from dba_objects o where o.owner in ('HRONLINE', 'ETS', 'LHC', 'BACKUPUSER', 'CAREER_DMOLVIEW', 'ASSEREAD', 'NORMREAD', 'ASSE', 'ZHAOPIN', 'CAREER', 'PERFSTAT', 'INTERNCAREER', 'HNCAREER', 'HOLONLINE', 'ZTECAREER' ) and o.object_type='PACKAGE' order by o.created desc ; 08_DDL_DROP_PACKAGE
select 'drop PACKAGE BODY '||o.owner||'.'||o.object_name ||';'||chr(13)||chr(10) from dba_objects o where o.owner in ('HRONLINE', 'ETS', 'LHC', 'BACKUPUSER', 'CAREER_DMOLVIEW', 'ASSEREAD', 'NORMREAD', 'ASSE', 'ZHAOPIN', 'CAREER', 'PERFSTAT', 'INTERNCAREER', 'HNCAREER', 'HOLONLINE', 'ZTECAREER' ) and o.object_type='PACKAGE BODY' order by o.created desc ; 09_DDL_DROP_PACKAGE_BODY
select 'drop table '||o.owner||'.'||o.object_name ||' cascade constraints ;' from dba_objects o where o.owner in ('HRONLINE', 'ETS', 'LHC', 'BACKUPUSER', 'CAREER_DMOLVIEW', 'ASSEREAD', 'NORMREAD', 'ASSE', 'ZHAOPIN', 'CAREER', 'PERFSTAT', 'INTERNCAREER', 'HNCAREER', 'HOLONLINE', 'ZTECAREER' ) and o.object_type='TABLE' order by o.created desc ; 10_DDL_DROP_TABLE
select 'execute dbms_job.remove('||j.JOB||');' as removejob from dba_jobs j where instr(j.LOG_USER,'SYS')<=0 ;