查詢oracle有哪些用戶的方法：1、執(zhí)行“select * from dba_users;”語句；2、執(zhí)行“select * from all_users;”語句；3、執(zhí)行“select * from user_users;”語句。
本教程操作環(huán)境：Windows7系統(tǒng)、Oracle 11g版、Dell G3電腦。
查詢Oracle中所有用戶信息
1、查看所有用戶：
select?*?from?dba_users;? select?*?from?all_users;? select?*?from?user_users;
2、查看用戶或角色系統(tǒng)權(quán)限(直接賦值給用戶或角色的系統(tǒng)權(quán)限)：
select?*?from?dba_sys_privs;? select?*?from?user_sys_privs;?(查看當(dāng)前用戶所擁有的權(quán)限)
3、查看角色(只能查看登陸用戶擁有的角色)所包含的權(quán)限
sql>select?*?from?role_sys_privs;
4、查看用戶對象權(quán)限：
select?*?from?dba_tab_privs;? select?*?from?all_tab_privs;? select?*?from?user_tab_privs;
5、查看所有角色：?
select?*?from?dba_roles;
6、查看用戶或角色所擁有的角色：
select?*?from?dba_role_privs;? select?*?from?user_role_privs;
7、查看哪些用戶有sysdba或sysoper系統(tǒng)權(quán)限(查詢時需要相應(yīng)權(quán)限)
select?*?from?V$PWFILE_USERS
8、SqlPlus中查看一個用戶所擁有權(quán)限
SQL>select?*?from?dba_sys_privs?where?grantee='username';?其中的username即用戶名要大寫才行。 比如：?SQL>select?*?from?dba_sys_privs?where?grantee='TOM';
9、Oracle刪除指定用戶所有表的方法
select?'Drop?table?'||table_name||';'?from?all_tables?where?owner='要刪除的用戶名(注意要大寫)';
10、刪除用戶
drop?user?user_name?cascade;?如：drop?user?SMCHANNEL?CASCADE
11、獲取當(dāng)前用戶下所有的表：
select table_name from user_tables;
12、刪除某用戶下所有的表數(shù)據(jù):
select?'truncate?table?'?||?table_name?from?user_tables;
13、禁止外鍵 ORACLE數(shù)據(jù)庫中的外鍵約束名都在表user_constraints中可以查到。
其中constraint_type=’R’表示是外鍵約束。
啟用外鍵約束的命令為：alter?table?table_name?enable?constraint?constraint_name? 禁用外鍵約束的命令為：alter?table?table_name?disable?constraint?constraint_name
然后再用SQL查出數(shù)據(jù)庫中所以外鍵的約束名：
select?'alter?table?'||table_name||'?enable?constraint?'||constraint_name||';'?from?user_constraints?where?constraint_type='R'select?'alter?table?'||table_name||'?disable?constraint?'||constraint_name||';'?from?user_constraints?where?constraint_type='R'
14、ORACLE禁用/啟用外鍵和觸發(fā)器 –啟用腳本
SET?SERVEROUTPUT?ON?SIZE?1000000 BEGIN for?c?in?(select?'ALTER?TABLE?'||TABLE_NAME||'?ENABLE?CONSTRAINT?'||constraint_name||'?'?as?v_sql?from?user_constraints where?CONSTRAINT_TYPE='R')?loop DBMS_OUTPUT.PUT_LINE(C.V_SQL); begin EXECUTE?IMMEDIATE?c.v_sql; exception?when?others?then dbms_output.put_line(sqlerrm); end; end?loop;? for?c?in?(select?'ALTER?TABLE?'||TNAME||'?ENABLE?ALL?TRIGGERS?'?AS?v_sql?from?tab?where?tabtype='TABLE')?loop dbms_output.put_line(c.v_sql); begin execute?immediate?c.v_sql; exception?when?others?then dbms_output.put_line(sqlerrm); end; end?loop; end; /? commit;
–禁用腳本
SET?SERVEROUTPUT?ON?SIZE?1000000 BEGIN for?c?in?(select?'ALTER?TABLE?'||TABLE_NAME||'?DISABLE?CONSTRAINT?'||constraint_name||'?'?as?v_sql?from?user_constraints where?CONSTRAINT_TYPE='R')?loop DBMS_OUTPUT.PUT_LINE(C.V_SQL); begin EXECUTE?IMMEDIATE?c.v_sql; exception?when?others?then dbms_output.put_line(sqlerrm); end; end?loop;? for?c?in?(select?'ALTER?TABLE?'||TNAME||'?DISABLE?ALL?TRIGGERS?'?AS?v_sql?from?tab?where?tabtype='TABLE')?loop dbms_output.put_line(c.v_sql); begin execute?immediate?c.v_sql; exception?when?others?then dbms_output.put_line(sqlerrm); end; end?loop; end; / commit;
推薦教程：《Oracle教程》
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載。
THE END
喜歡就支持一下吧
相關(guān)推薦
.png)
