DBA常用SQL之DDL生成语句

获取对象定义的包为：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
90000

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
;

11_DDL_REMOVE_JOB

------数据迁移常用SQL

SELECT 'DROP USER '||u.username ||' CASCADE;' AS dropstrs FROM DBA_USERS U
where u.username in
(
 'ETS',
 'LHC'
 )
 and u.account_status='OPEN'
;

DDL 生在删除用户的SQL

select
TABLESPACE_NAME,FILE_NAME,
case when rn=1 then
  ctb
  else
  replace(replace(replace(ctb,'create tablespace','alter tablespace '),'datafile','add datafile'),'extent management local','')
end ctb,
rn
from
(
select
TABLESPACE_NAME,FILE_NAME,ctb,
row_number() over(partition by TABLESPACE_NAME order by file_name) rn
from
    (
        select s.TABLESPACE_NAME,d.FILE_NAME,
         'create tablespace ' || s.TABLESPACE_NAME || '  datafile '||''''||
               replace(d.FILE_NAME,'+DATA/hronline/datafile/','/data/oradata/holtestdb/') ||''''||
               ' size 50M autoextend on  next 50M maxsize 20000M extent management local; ' AS ctb
         from dba_data_files d,dba_tablespaces s
          where d.TABLESPACE_NAME=s.TABLESPACE_NAME
          and s.TABLESPACE_NAME
          in
          (
            select distinct t.TABLESPACE_NAME from dba_tables t
            where t.OWNER in
            (
            'LHC',
            'BACKUPUSER',
            'TMSUSER'
            )
            and t.TABLESPACE_NAME is not null
          ) 

    )
)
;

-----------------------------------------
select
TABLESPACE_NAME,FILE_NAME,
case when rn=1 then
  ctb
  else
  replace(replace(replace(ctb,'create tablespace','alter tablespace '),'datafile','add datafile'),'extent management local','')
end ctb,
rn
from
(
select
TABLESPACE_NAME,FILE_NAME,ctb,
row_number() over(partition by TABLESPACE_NAME order by file_name) rn
from
    (
        select s.TABLESPACE_NAME,d.FILE_NAME,
         'create tablespace ' || s.TABLESPACE_NAME || '  datafile '||''''||
               replace(d.FILE_NAME,'D:\ORACLE\ORADATA\XASMART\','/u01/oradata/smart/') ||''''||
               ' size 100M autoextend on  next 100M maxsize 20000M extent management local; ' AS ctb
         from dba_data_files d,dba_tablespaces s
          where d.TABLESPACE_NAME=s.TABLESPACE_NAME
          and instr(s.TABLESPACE_NAME,'SYS')+
          instr(s.TABLESPACE_NAME,'TIVOLIORTS')+
          instr(s.TABLESPACE_NAME,'XDB')+
          instr(s.TABLESPACE_NAME,'TOOLS')+
          instr(s.TABLESPACE_NAME,'UNDOTBS1')+
          instr(s.TABLESPACE_NAME,'USERS')<=0

    )
)
;

DDL生成数据迁移的表空间创建语句

SELECT u.username,DBMS_METADATA.GET_DDL('USER',u.username) FROM DBA_USERS U
where u.username in
 ('HRONLINE'
 )

DDL_生成创建用户的SQL

 SELECT 'grant '||p.privilege || ' on '||p.owner||'.'||p.table_name||' to '||p.grantee||';' grant_table
 FROM DBA_TAB_PRIVS p
 WHERE GRANTEE IN
 (
     'SMART',
     'BACKUPUSER'
 )

 UNION ALL
 SELECT 'grant '||p.privilege || ' on '||p.owner||'.'||p.table_name||' to '||p.grantee||';' grant_table
 FROM DBA_TAB_PRIVS p
 WHERE GRANTEE IN
      (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS
         WHERE GRANTEE in
               (
                 'SMART',
                 'BACKUPUSER'
               )
      );

DDL在源库生成对象授权SQL并在目标库执行

SELECT
case when p.admin_option='NO' THEN
'grant '||p.privilege|| ' to '||p.grantee||';'
  else
'grant '||p.privilege|| ' to '||p.grantee||' with admin option'||';'
end as grants, 

'u' as U_S_PRIVS
FROM DBA_SYS_PRIVS p
WHERE p.GRANTEE in
(
  'HRONLINE'
)
UNION ALL
SELECT
case when s.admin_option='NO' THEN
'grant '||s.privilege|| ' to '||s.grantee||';'
else
'grant '||s.privilege|| ' to '||s.grantee||' with admin option'||';'
end as grants, 

'r' as U_S_PRIVS
FROM DBA_SYS_PRIVS s
WHERE s.GRANTEE IN
(SELECT r.GRANTED_ROLE FROM DBA_ROLE_PRIVS  r
  WHERE r.GRANTEE in
    (
      'HRONLINE'
 )

)
union all

SELECT  'GRANT CONNECT TO '||p.grantee||';' as grants, 'connect' as U_S_PRIVS
FROM DBA_SYS_PRIVS p
WHERE p.GRANTEE in
(
  'HRONLINE'

)
union all
SELECT  'GRANT RESOURCE TO '||p.grantee||';' as grants, 'resource' as U_S_PRIVS
FROM DBA_SYS_PRIVS p
WHERE p.GRANTEE in
(
  'HRONLINE'
)

DDL在源库生成用户授权的SQL并在目标库执行

select ' grant select on '||o.owner||'.'||o.object_name ||' to hronline ;'
  from dba_objects o
 where o.owner in
 ('HRCLOUD'
 )
 and o.object_type='TABLE'
 order by o.created desc
 ;

DDL_GRANT_TABLE_TO_USER

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;

select * from dba_data_files f
where f.TABLESPACE_NAME='SMARTDATA_HISTORY'
;

SELECT A.tablespace_name tablespace,
        D.mb_total,
        SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
        D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
 FROM v$sort_segment A,
        (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
           FROM v$tablespace B, v$tempfile C
          WHERE B.ts# = C.ts#
          GROUP BY B.name, C.block_size) D
 WHERE A.tablespace_name = D.name
 GROUP by A.tablespace_name, D.mb_total;

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'
;

select count(t.file_name), t.tablespace_name from DBA_DATA_FILES t
group by t.tablespace_name
;

select 'alter database datafile '||''''|| t.FILE_NAME||''''|| ' autoextend on next 100M maxsize 20000M;'from SYS.DBA_DATA_FILES t;
select 'ALTER TABLESPACE '||f.tablespace_name||' ADD DATAFILE ' ||''''||f.FILE_NAME ||''''||' SIZE 10G;' stras ,f.MAXBYTES/1024/1024/1024 as maxsize,f.*
from dba_data_files f
--where f.TABLESPACE_NAME='PSINDEX'
where f.TABLESPACE_NAME  in('PSINDEX','GPAPP','HRLARGE','PSIMAGE2','HRSLARGE','EOECLRG')
;

select 'alter database datafile '||''''||t.FILE_NAME||''''||' autoextend off;' ,t.MAXBYTES/1024/1024/1024 as maxsize from SYS.DBA_DATA_FILES t
where t.TABLESPACE_NAME in('PSINDEX','GPAPP','HRLARGE','PSIMAGE2','HRSLARGE','EOECLRG')

select 'alter database datafile ' ||''''||f.FILE_NAME ||''''||' RESIZE 20G;' stras ,f.MAXBYTES/1024/1024/1024 as maxsize,f.*
from dba_data_files f
--where f.TABLESPACE_NAME='PSINDEX'
where f.TABLESPACE_NAME  in('PSINDEX','GPAPP','HRLARGE','PSIMAGE2','HRSLARGE','EOECLRG')
;

根据表空间添加或修改数据文件

create user kmetlmd IDENTIFIED by kmetlmd2016;
GRANT CREATE USER,DROP USER,ALTER USER,
CREATE ANY VIEW, DROP ANY VIEW,
EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION
TO kmetlmd;

创建用户与授权

create tablespace cdmods  datafile 'F:\APP\ORADATA\KMMEDICAL\cdmods01.dbf' size 200M autoextend on  next 100M maxsize 10000M extent management local;
create tablespace cdmdatamarket  datafile 'F:\APP\ORADATA\KMMEDICAL\cdmdatamarket.dbf' size 200M autoextend on  next 100M maxsize 10000M extent management local; 

create user cdmods IDENTIFIED by kmcdmods1220 DEFAULT TABLESPACE cdmods;
create user cdmdatamarket IDENTIFIED by cdmdatamarket1220 DEFAULT TABLESPACE cdmdatamarket;

GRANT
CREATE TABLE,
CREATE SEQUENCE,
CREATE ROLE,
CREATE PUBLIC SYNONYM,
DROP PUBLIC SYNONYM,
CREATE SYNONYM,
CREATE VIEW,
DEBUG CONNECT SESSION,
CREATE DATABASE LINK,
CREATE CLUSTER,
EXP_FULL_DATABASE,
IMP_FULL_DATABASE,
ALTER SESSION,
CREATE SESSION,
CONNECT,
RESOURCE
TO cdmods;

GRANT
CREATE TABLE,
CREATE SEQUENCE,
CREATE ROLE,
CREATE PUBLIC SYNONYM,
DROP PUBLIC SYNONYM,
CREATE SYNONYM,
CREATE VIEW,
DEBUG CONNECT SESSION,
CREATE DATABASE LINK,
CREATE CLUSTER,
EXP_FULL_DATABASE,
IMP_FULL_DATABASE,
ALTER SESSION,
CREATE SESSION,
CONNECT,
RESOURCE
TO cdmdatamarket;

创建表与授权

---授权对象权限或角色
grant execute on km.func_decrypt to encryptfun;
GRANT DBA TO KM

---取消权限
REVOKE ALL ON KM.FUNC_DECRYPT FROM ENCRYPTFUN;
REVOKE DBA from KM;

SELECT * FROM USER_TAB_PRIVS_MADE WHERE GRANTEE='ENCRYPTFUN'; 

-----以下是查询对象权限
SELECT *
FROM DBA_TAB_PRIVS p
WHERE GRANTEE IN
(
 'ENCRYPTFUN'
)
;

----以下是系统角色或系统权限

SELECT *
FROM DBA_TAB_PRIVS p
WHERE GRANTEE IN
  (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS
     WHERE GRANTEE in
           (

             'ENCRYPTFUN'
           )
  );

取消权限

http://www.cnblogs.com/mq0036/archive/2012/12/31/2840637.html

查询用户拥有哪里权限：
SQL> select * from dba_role_privs;
 SQL> select * from dba_sys_privs;
 SQL> select * from role_sys_privs;
查自己拥有哪些系统权限
 SQL> select * from session_privs; 

查询用户及系统权限