DBA常用SQL之DDL生成语句-2

时间:2022-04-11 04:35:40

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

DBA常用SQL之DDL生成语句-2DBA常用SQL之DDL生成语句-2
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
DDL 生在删除用户的SQL DBA常用SQL之DDL生成语句-2DBA常用SQL之DDL生成语句-2
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:ORACLEORADATAXASMART‘,/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生成数据迁移的表空间创建语句
DDL生成数据迁移的表空间创建语句 DBA常用SQL之DDL生成语句-2DBA常用SQL之DDL生成语句-2
SELECT u.username,DBMS_METADATA.GET_DDL(USER,u.username) FROM DBA_USERS U
where u.username in
 (HRONLINE
 )
DDL_生成创建用户的SQL DBA常用SQL之DDL生成语句-2DBA常用SQL之DDL生成语句-2
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并在目标库执行
DDL在源库生成对象授权SQL并在目标库执行 DBA常用SQL之DDL生成语句-2DBA常用SQL之DDL生成语句-2
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并在目标库执行
DDL在源库生成用户授权的SQL并在目标库执行 DBA常用SQL之DDL生成语句-2DBA常用SQL之DDL生成语句-2
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
DDL_GRANT_TABLE_TO_USER DBA常用SQL之DDL生成语句-2DBA常用SQL之DDL生成语句-2
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)
;

根据表空间添加或修改数据文件
根据表空间添加或修改数据文件 DBA常用SQL之DDL生成语句-2DBA常用SQL之DDL生成语句-2
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;
创建用户与授权 DBA常用SQL之DDL生成语句-2DBA常用SQL之DDL生成语句-2
create tablespace cdmods  datafile F:APPORADATAKMMEDICALcdmods01.dbf size 200M autoextend on  next 100M maxsize 10000M extent management local; 
create tablespace cdmdatamarket  datafile F:APPORADATAKMMEDICALcdmdatamarket.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;

创建表与授权
创建表与授权
查询用户拥有哪里权限: 
SQL> select * from dba_role_privs;
 SQL> select * from dba_sys_privs;
 SQL> select * from role_sys_privs; 
查自己拥有哪些系统权限
 SQL> select * from session_privs; 
DBA常用SQL之DDL生成语句-2DBA常用SQL之DDL生成语句-2
不论授予何种权限,每条授权(grant)语句总是由三部分组成:

1) 接受者部分是准备获得权限的一个或多个用户的列表。

2)关键字权限部分由grant后跟一种或多种权限组成。如果在同一条grant语句中有多个权限,权限之间用逗号分隔。

3)  表名部分由关键字o n起头并列出准备在其上授权的表。

看下面的详细介绍,主要是介绍如何把添加、删除、修改、查询四种权限授予用户,如下:

一、insert

insert权限允许在其他用户的表中建立行。语句grant insert on sample_a to public;允许所有用户在sample_a中建立新的行。Oracle允许在单条grant语句中授多个权限,SQL语句grant insert,select on sample_a to public;等价于两个语句:grant select on sample_a to public;语句和grant insert on sample_a to ublic;语句。

二、update

update权限允许其他用户修改非自己表中的数据。语句grant update on sample_a teplownd;允许用户teplownd修改表sample_a中的信息。

三、select

select权限允许用户查看其他用户表中的内容。语句grant select on sample_3 to public;将允许所有用户浏览表sample_3中的内容,而语句grant select on sample_3 to opsrosenberge,opsrosenberge,opsabbeyms;则只允许两个用户查看表sample_3中的内容。注意,当多个用户接受授权时,用户名之间要用逗号分隔。

提示以public为授权对象时,所有数据库用户都获得指定的权限。如果用户的数据库有15000个用户,则单独授权就需要15000次(每个用户一次),而授权给public,一次即可。

四、delete

删除权限允许其他用户删除指定表的信息行。此权限非同小可,因此我们建议小心使用。下面是一个实际例子:如果一个用户连接到产品数据库,而他还以为他连接的是测试数据库。他发布了一条命令delete from people_master;并且Oracle做出了反应12003 rows delet.ed在退出SQL * Plus后,下一个程序访问people_master查看Rick Bower的记录,会被告知记录不存在。

命令grant delete,update,select on sample_a to public;对所有数据库用户给出指明的权限,而命令grant select,update,insert,delete on sample_a to teplownd,greerw;只允许用户teplownd和greerw对表sample_a做命令中所列出的动作

 

 

 

授权语句

--select * from dba_users; 查询数据库中的所有用户

--alter user TEST_SELECT account lock; 锁住用户 
--alter user TEST_SELECT account unlock; 给用户解锁

--create user xujin identified by   xujin; 建立用户

--grant create tablespace to xujin; 授权 
--grant select on tabel1 to xujin; 授权查询 
--grant update on table1 to xujin; 
--grant execute on procedure1 to xujin 授权存储过程 
--grant update on table1 to xujin with grant option; 授权更新权限转移给xujin用户,许进用户可以继续授权;

 
--收回权限

--revoke select on table1 from xujin1; 收回查询select表的权限; 
--revoke all on table1 from xujin;


/*grant connect to xujin; 
revoke connect from xujin 
grant select on xezf.cfg_alarm to xujin; 
revoke select on xezf.cfg_alarm from xujin;*/ 
--select table_name,privilege from dba_tab_privs where grantee=xujin 查询一个用户拥有的对象权限 
--select * from dba_sys_privs where grantee=xujin 查询一个用户拥有的系统权限 
--select * from session_privs --当钱会话有效的系统权限

 

--角色

--create role xujin1;--建立xujin1角色 
--grant insert on xezf.cfg_alarm to xujin1; 将插入表的信息 
--revoke insert on xezf.cfg_alarm from xujin1; 收回xujin1角色的权限 
--grant xujin1 to xujin ; 将角色的权限授权给xujin; 
-- create role xujin2; 
--grant xujin1 to xujin2; 将角色xujin1授权给xujin2; 
--alter user xujin default xujin1,xujin2; 修改用户默认角色 
-- DROP ROLE xujin1;删除角色1; 
--select * from role_sys_privs where role=xujin1; 
--查看许进1角色下有什么系统权限; 
--select granted_role,admin_option from role_role_privs where role=xujin2; 
--查看xujin1角色下面有什么角色权限 
--select * from role_sys_privs where role=xujin2; 
--select table_name,privilege from role_tab_privs where role=xujin1; 
--select * from dba_role_privs where grantee=xujin --查看用户下面有多少个角色;
Oracle命令:授权-收回权限-角色