Oracle note 2. Common DBA command line

1. Unlock the user

Alter user Scott account unlock;

2. log out, disconnect, and switch the current user connection

Quit

Conn Scott/Tiger

3. user permission Query

A. View All Users:

Select * From dba_users;

Select * From all_users;

Select * From user_users;

B. view system permissions of users or roles (system permissions assigned to users or roles ):

Select * From dba_sys_privs;

Select * From user_sys_privs;

C. view the permissions contained in a role (only the roles owned by login users can be viewed)

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 a user or role:

Select * From dba_role_privs; s

Elect * From user_role_privs;

G. Check which users have sysdba or sysoper system permissions (the corresponding permissions are required during query)

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 10 m on users;

B. Change the password

Alter User Username identified by pass;

Change the password from password to pass. You can also change the password after logging in.

C. delete a user

Drop User Username;

Drop User Username cascade;

5. Three standard roles (role): connect, resource, and DBA.

I. Connect connection and login Permissions

Ii. resource can be used to create tables, sequences, procedure, trigger, index, and cluster ).

Iii. DBA Administrator

6. User Permissions

A. Authorize the user

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. delete a role

Drop role login;

8. Export role Resources

A. First, go to the CMD console.

B. Enter a directory, such as CD C:/userdir.

C. Enter the command, exp

D. Enter the user name and password, that is, the role data to be exported.

E. Press enter as the default value below.

F. After the export is complete, the exported file expdat. DMP is displayed.

9. Import role data

A. First, go to the DOS console.

B. Enter the directory for data export, for example, CD C:/userdir.

C. Enter the command, IMP

D. Enter the user name and password to import the data to the user.

E. Press enter. Enter.

F. Enter the user name and password. Which user is the data exported from?

10. Common sqlplus commands

A. Set linesize 120 allows you to set the sqlplus display buffer size;

B. Set pagesize 20. You can set the size of each page.

C. Edit can edit the current SQL statement. Save the statement and enter/press enter to run it.

D. Spool C:/temp. SQL; And spool off can save all the displayed content between them;

E. Start [filepath] can execute SQL statements in batches;

F and desc tablename can be used to view the specified table structure.

G, show user can view the current user

H. Set serveroutput on run dbms_output output information

I. Show ERROR view compilation error information

11. Common system tables and table dictionaries

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;