Shulou(Shulou.com)06/01 Report--

Query all user information in Oracle, delete users, tables, prohibit foreign keys, etc. 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 assigned directly to users or roles): select * from dba_sys_privs; select * from user_sys_privs; (view the permissions owned by the current user) 3. View the permissions sql > select * from role_sys_privs;4 contained in roles (you can only view roles owned by login users). 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) check the permissions of a user in select * from V$PWFILE_USERS8.SqlPlus SQL > select * from dba_sys_privs where grantee='username'; where the username means that the user name should be uppercase. For example: SQL > select * from dba_sys_privs where grantee='TOM';9, Oracle method select 'Drop table' | | table_name | |';'; 'username to be deleted by from all_tables where owner=' (note uppercase)'; 10. Delete user drop user user_name cascade; such as drop user SMCHANNEL CASCADE11, and get all 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, foreign key constraint names in the prohibited foreign key 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 and 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_constraintswhere constraint_type='R'14, ORACLE disable / enable foreign keys and triggers-enable script SET SERVEROUTPUT ON SIZE 1000000BEGINfor c in (select' ALTER TABLE'| | TABLE_NAME | | 'ENABLE CONSTRAINT' | | constraint_name | |'as v_sql from user_constraintswhere CONSTRAINT_TYPE='R') loopDBMS_OUTPUT.PUT_LINE (C.V_SQL); beginEXECUTE IMMEDIATE c.vSecretsql; 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.vSecretsql; exception when others then dbms_output.put_line (sqlerrm); end;end loop;end;/ commit -- disable script SET SERVEROUTPUT ON SIZE 1000000BEGINfor c in (select 'ALTER TABLE' | | TABLE_NAME | | 'DISABLE CONSTRAINT' | | constraint_name | |'as v_sql from user_constraintswhere CONSTRAINT_TYPE='R') loopDBMS_OUTPUT.PUT_LINE (C.V_SQL); begin EXECUTE IMMEDIATE c.vSecretsql; 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.vaccounsqlash exception when others then dbms_output.put_line (sqlerrm); end;end loop;end;/commit

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.