Common commands in Oracle

Common commands in Oracle

1. Create a user
Create user Username identified by password
Note: The username and password should be in English.
For example, create user sms identified by sms;

2. Create a tablespace
Create tablespace name datafile 'Storage path' size
For example, create tablespace ts_sms datafile 'f: \ quanxianguanliruanjian \ oracle \ tablespace \ sms. dbf' size 100 m;

3. Assign the tablespace value to the created user.
Alter user default tablespace
For example, alter user sms default tablespace ts_sms;

4. grant permissions to users
Grant create session, create view, create table, unlimited tablespace to user
For example, grant create session, create view, create table, and unlimited tablespace to sms;
Or assign all DBA permissions to the user, so that the user has permissions such as sequence creation.
Grant dba to user; for example, grant dba to sms;

5. Switch to the new user logon
Conn user/Password
For example, conn sms/sms;
1--5 is the process from creating a user to importing SQL statements.

6. delete a user
Drop user Username
For example, drop user sms;

7. Change the user's password
Alter user Username identified by new password
For example, alter user test identified by test;

8. View All Users
Select * from dba_users; or select * from all_users; or select * from user_users;
Select * from user_users; only the current user can be viewed.

9. View permissions of the current user or DBA role
Select * from user_sys_privs; select * from dba_sys_privs;

10. view the tablespace capacity
SQL> selecttablespace_name "table space", bytes/1024/1024 "total capacity MB" fromdba_data_files;
The result is as follows:

11. view the usage of the tablespace.
SQL> selecta. tablespace_name as tablespace,. bytes/1024/1024 as total capacity MB, (. bytes-b.bytes)/1024/1024 "use capacity MB", B. bytes/1024/1024 "remaining capacity MB", round (. bytes-b.bytes)/. bytes) *) "percent used" from (select tablespace_name, sum (bytes) bytes fromdba_data_files group by tablespace_name) a, (select tablespace_name, sum (bytes) bytes, max (bytes) largest from dba_free_space group by tablespace_name) B where. tablespace_name = B. tablespace_nameorder by (. bytes-b.bytes)/. bytes) desc;

12. view the locked process:
Select 'alter system kill session ''' | sid | ',' | serial # | '''; 'From v $ session where sid in (select sid from v $ lock where block = 1 );
The result is as follows: if any process is locked, the system prompts "unselected rows"
'Altersystemkillsession ''' | SID | ',' | SERIAL # | ''';'
--------------------------------------------------------------------------------
Alter system kill session '2017 57 ';
Kill the locked process:
SQL> alter system kill session '2017 57 ';