Query all user information in oracle
1. View all users:
Select * from dba_users
Select * from all_users
Select * from user_users
two。 View user or role system permissions (system permissions assigned directly to a user or role):
Select * from dba_sys_privs
Select * from user_sys_privs; (view the permissions of the current user)
3. View the permissions contained in roles (you can only view roles owned by logged-in users)
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. Check which users have sysdba or sysoper system permissions (appropriate permissions are required for query)
Select * from V$PWFILE_USERS
View the permissions of a user in 8.SqlPlus
SQL > select * from dba_sys_privs where grantee='username'
The username, that is, the user name, must be capitalized.
For example:
SQL > select * from dba_sys_privs where grantee='TOM'
9. Oracle method to delete all tables of a specified user
Select 'Drop table' | | table_name | |'; 'from all_tables
User name to be deleted by where owner=' (uppercase)'
10. Delete a user
Drop user user_name cascade
Such as: drop user SMCHANNEL CASCADE
11. Get all the tables under the current user: select table_name from user_tables
12. Delete all table data under a user: select 'truncate table' | | table_name from user_tables
13. Prohibit foreign keys
The foreign key constraint names in the ORACLE database can be found in the table user_constraints. Where constraint_type='R' represents a foreign key constraint.
The command to enable foreign key constraints is: alter table table_name enable constraint constraint_name
The command to disable foreign key constraints is: 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 scripting
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 scripts
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
