Oracle Common related SQL statements

First, Oracle common related SQL statements

1. Connect to the database
Su-oracle-c "Sqlsplus User/Password Note: First time login with Sqlplus/as SYSDBA

Note: Close the database: Note: Shutdown can be turned off option, from the mildest to the most brutal behavior options (shutdown, shutdown transactional, shutdown immediate, shutdown abort)
Shutdown: Shut down, wait for each user to exit the system war be canceled after exiting to close the database.
Shutdown transactional: Transactional shutdown, waiting for each user to submit a war fallback to the current transaction, and Oracle cancels the conversation and shuts down after all users have exited the system.
Shutdown immediate: Directly close, cancel all user conversations (prompting fallback), and perform a normal shutdown program.
After you close the database, you need to turn off monitoring, lsnrctl stop

2. View Current User

Show user;

3. Create a table space
Create tablespace table name datafile '/db/oracle11g/oradata/table name/table name 01.dbf ' size 500m autoextend on next 500m
MaxSize 31g Logging Online permanent extent management local;

4. Add Table Space
ALTER tablespace table space name ADD datafile '/db/oracle11g/oradata/table name/table name 02.dbf ' SIZE 500m autoextend on NEXT 500m
MAXSIZE 31g;

5. Create a user
Create user username identified by password default Tablespace table space name temporary tablespace temp;

6. Assigning Permissions

Grant permissions to the user;

7. Reset Password
Alter user identified by password;

8. View the data file for the current user's tablespace

SELECT * FROM V$datafile

9. View the data file corresponding to the table space where the table resides

Select T1.name from V$tablespace t1,v$datafile T2 where T1.ts=value

10. View the table space name of the current user

SELECT * from V$tablespace;

11. See what role a user has?

SELECT * from Dba_role_privs where grantee= ' SYS '

12. Know the tablespace, showing all tables included in the table space;
SELECT * from all_tables where tablespace_name= ' table space name '

13. Know the table name and show the table space that the table belongs to
Select Tablespace_name,table_name from user_tables where table_name= ' table name '

14. Renaming table Spaces
In the case of a table space of online
ALTER tablespace tablespace_name RENAME to New_tablespace_name;

15. Delete Table spaces

DROP tablespace tablespace_name including CONTENTS and datafiles

16. View Table Space

SELECT T.tablespace_name, round (SUM (Bytes/(1024x768)), 0) ts_size
From Dba_tablespaces T, Dba_data_files D
WHERE T.tablespace_name = D.tablespace_name
GROUP by T.tablespace_name;

17. View Control Files

SELECT NAME from V$controlfile;

18. View Log files

SELECT MEMBER from V$logfile;

19. View the date and manner in which the database was created

SELECT created, Log_mode, log_mode from V$database;

20. View the number of hosts currently connected to the database:

Col Machine for A20
Set Linesize 150
Select distinct machine,username from V$session order by Username,machine;

21. Querying User Sessions

Select Username,serial#,sid from V$session;
Alter system kill session ' Serial#,sid '; --Delete related user sessions

22. Query the number of connections to Oracle

Select COUNT (*) from v$session

23. Querying the number of concurrent connections for Oracle

Select COUNT (*) from v$session where status= ' ACTIVE ';

24. View versions of Oracle

Select banner from Sys.v_$version;

This article is from the "lake and Laughter" blog, please make sure to keep this source http://hashlinux.blog.51cto.com/9647696/1793427

Oracle Common related SQL statements