------数据迁移常用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 生在删除用户的SQLDDL 生在删除用户的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: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生成数据迁移的表空间创建语句
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并在目标库执行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并在目标库执行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_USERDDL_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: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;
不论授予何种权限，每条授权(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命令:授权-收回权限-角色