Querying and deleting user information in Oracle

Tag:oracle    Delete table     Delete user     user query    

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 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 sql>select * from role_sys_privs;4 the role (only the roles owned by the logged-on user). 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. To view the roles owned by a 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_users8. Sqlplus View a user has permissions sql>select * from dba_sys_privs where grantee= ' username ';  The username is the user name to capitalize.For example:  sql>select * from dba_sys_privs where grantee= ' TOM '; 9, Oracle removes all tables from specified users select  ' drop table  ' | | table_name| | '; '  from all_tables where owner= ' User name to delete (note to capitalize) '; 10, delete user drop user user_name  cascade; : Drop user smchannel cascade11, get all tables under 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 key  oracle the foreign KEY constraint name in the database can be found in the table user_constraints. where constraint_type= ' R ' indicates a foreign KEY constraint. The command to enable the foreign key constraint is:alter table table_name enable constraint constraint_name  The command to disable a FOREIGN KEY constraint is: Alter table table_name disable constraint constraint_ Name and then use SQL to isolate the constraint name of the key outside 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 scripting 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.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  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.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

This article is from the "Technology Sharing garden" blog, please be sure to keep this source http://zhangtianshan.blog.51cto.com/1500414/1774967

Querying and deleting user information in Oracle