Oracle Note II, Common DBA command line

1, to unlock the user

Alter user Scott account unlock;

2, log off, disconnect, switch the current user connection

Quit

Conn Scott/tiger

3, User rights query

A. View all Users:

SELECT * from Dba_users;

SELECT * from All_users;

SELECT * from User_users;

B. 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;

C. View the permissions contained in the role (only those roles owned by the logged-on user)

SELECT * from Role_sys_privs;

D. View User Object permissions:

SELECT * from Dba_tab_privs;

SELECT * from All_tab_privs;

SELECT * from User_tab_privs;

E. View all roles:

SELECT * from Dba_roles;

F. View the roles owned by the user or role:

SELECT * from Dba_role_privs; S

elect * from User_role_privs;

G. See which users have SYSDBA or Sysoper system permissions (requires appropriate permissions when querying)

SELECT * FROM V$pwfile_users

4. User Management

A. Create A User

Create user username identified by password;

Create user username identified by password default tablespace users quota 10M on users;

B, change the password

Alter user username identified by pass;

The password is changed from password to pass, the same login after the input password can also change the password

C, delete users

Drop user username;

Drop user username cascade;

5, three standard roles (role): CONNECT, Resource, and DBA.

I, connect connection, login permission

II. Resource can create tables, sequences, procedures (procedure), triggers (trigger), indexes, and clusters (cluster).

III. DBA Administrator

6. User Rights

A, to the user authorization

Grant Connect, resource to username;

Grant create session, CREATE table, create view to username;

B. Revoke permissions

Revoke connect from username;

7. Role Management

A. Create A role

Create role LOGIN;

B. Remove Roles

Drop role LOGIN;

8. Export Role Resources

A, first enter the console cmd

B, enter a directory, such as: CD C:/userdir

C, input command, exp

D, prompt for user name, password, that is, the data to export the role

E, the following direct default, press ENTER ENTER key can

F, after the export is complete, you can see the exported file Expdat.dmp

9. Import role Data

A. First enter the DOS console

B, enter the exported data directory, such as: CD C:/userdir

C, Input command, IMP

D, enter user name, password, import this data to the user

E, followed by the return, enter key can be

F, prompt to enter the user name, password, this is the data from which user to export, is that user's

10. Sqlplus Common Commands

A, set Linesize 120 can set the Sqlplus display buffer size;

B, set pageSize 20 to set the display size per page

C, edit the current SQL statement can be edited, after saving input/return run

D, spool c:/temp.sql; and spool off can be saved between all the displayed contents;

E, start [filepath] can execute SQL statements in bulk;

F, desc tableName can view the specified table structure

G, show user can view current users

H, set serveroutput on run dbms_output output information

I, show error view compilation errors information

11, User common system table, table dictionary

Select table_name from User_tables;

Select View_name from User_views;

Select Constraint_name, Constraint_type, table_name from user_constraints;

Select table_name from dictionary;

Select Index_name from User_indexes;

Oracle Note II, Common DBA command line