Home>Article>Database> How to check which users are in Oracle

How to check which users are in Oracle

青灯夜游
青灯夜游 Original
2022-02-22 16:49:35 39419browse

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.

How to check which users are in Oracle

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn