How to check which users are in Oracle

How to query the users in Oracle: 1. Execute the "select * from dba_users;" statement; 2. Execute the "select * from all_users;" statement; 3. Execute the "select * from user_users;" statement.

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

Query all user information in Oracle

1. View all users:

select * from dba_users; 
select * from all_users; 
select * from user_users;

2. View user or role system permissions (system permissions directly assigned to users or roles):

select * from dba_sys_privs; 
select * from user_sys_privs; (查看当前用户所拥有的权限)

3. View roles (only roles owned by logged-in users can be viewed) Contained permissions

sql>select * from role_sys_privs;

4. View user object permissions:

select * from dba_tab_privs; 
select * from all_tab_privs; 
select * from user_tab_privs;

5. View all roles:

select * from dba_roles;

6. Check the roles owned by users or roles:

select * from dba_role_privs; 
select * from user_role_privs;

7. Check which users have sysdba or sysoper system permissions (corresponding permissions are required when querying)

select * from V$PWFILE_USERS

8. Check the permissions owned by a user in SqlPlus

SQL>select * from dba_sys_privs where grantee='username'; 其中的username即用户名要大写才行。
比如： SQL>select * from dba_sys_privs where grantee='TOM';

9. How to delete all tables of a specified user in Oracle

select 'Drop table '||table_name||';' from all_tables where owner='要删除的用户名(注意要大写)';

10. Delete user

drop user user_name cascade; 如：drop user SMCHANNEL CASCADE

11. Get all tables under the current user:

select table_name from user_tables;

12. Delete all table data under a certain user :

select 'truncate table ' || table_name from user_tables;

13. Foreign key constraints are prohibited. The foreign key constraint names in the ORACLE database can be found in the table user_constraints.

constraint_type='R' indicates a foreign key constraint.

启用外键约束的命令为：alter table table_name enable constraint constraint_name 
禁用外键约束的命令为：alter table table_name disable constraint constraint_name

Then use SQL to find out the constraint names of all foreign keys in the database:

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 disables/enables foreign keys and triggers - -Enable script

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;

--Disable script

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;

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of How to check which users are in Oracle. For more information, please follow other related articles on the PHP Chinese website!