Querying 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 that are directly assigned to a user or role):
SELECT * from Dba_sys_privs;
SELECT * from User_sys_privs; (View the permissions that the current user has)

3. View the permissions that are included in the role (can only view the roles owned by the logged-on user)
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. View the roles owned by the user or role:
SELECT * from Dba_role_privs;
SELECT * from User_role_privs;

7. See which users have SYSDBA or Sysoper system permissions (requires appropriate permissions when querying)
SELECT * FROM V$pwfile_users

View a user's permissions in 8.SqlPlus
Sql>select * from Dba_sys_privs where grantee= ' username ';
The username is the user name to capitalize.
Like what:
Sql>select * from Dba_sys_privs where grantee= ' TOM ';

9. How Oracle Deletes all tables for the specified user
Select ' Drop table ' | | table_name| | '; ' from All_tables
Where owner= ' user name to delete (note to capitalize) ';

10. Delete Users
Drop user user_name cascade;
such as: Drop user Smchannel CASCADE

11. Get all tables under current User: Select table_name from User_tables;

12. Delete all table data from a user: Select ' truncate table ' | | table_name from User_tables;

13, prohibit foreign key
The foreign KEY constraint name in the Oracle database can be found in table user_constraints. where constraint_type= ' R ' indicates a foreign KEY constraint. The command for the
Enable FOREIGN KEY constraint is: ALTER TABLE table_name enable constraint constraint_name 
The command to disable the FOREIGN KEY constraint is: ALTER TABLE table_name Disable constraint constraint_name
then use SQL to isolate the constraint name of a key other than 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 Disable/enable 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 and 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 and 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

Querying all user information in Oracle