Shulou(Shulou.com)06/01 Report--

1. Related sql sentences commonly used in oracle

1. Connect to the database

Su-oracle-c "sqlsplus user / password Note: sqlplus / as sysdba for first login

Note: close the database: note: shutdown can add the shutdown option, from the mildest to the most violent behavior options are (shutdown, shutdown transactional, shutdown immediate, shutdown abort)

Shutdown: close, wait for each user to exit the system is canceled, exit and shut down the database.

Shutdown transactional: transactional shutdown, waiting for each user to commit the transaction to roll back the current transaction, then oracle cancels the conversation and performs the shutdown after all users exit the system.

Shutdown immediate: close directly, cancel all user conversations (cause fallback), and execute the normal shutdown procedure.

After shutting down the database, you need to turn off listening, lsnrctl stop

two。 View current user

Show user

3. Create a tablespace

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 tablespace

ALTER TABLESPACE tablespace 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 tablespace name temporary tablespace temp

6. Assign permissions

Grant permissions to user

7. Reset password

Alter user user identified by password

8. View the data file in the table space of the current user

Select * from v$datafile

9. View the data file corresponding to the tablespace in which the table is located

Select t1.name from v$tablespace T1 where t1.ts=value vicidatafile T2

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'

twelve。 Know the tablespace and display all the tables included in the tablespace

Select * from all_tables where tablespace_name=' table space name'

13. Know the table name and show the tablespace to which the table belongs

Select tablespace_name,table_name from user_tables where table_name=' table name'

14. Rename tablespace

When the tablespace is ONLINE

ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name

15. Delete tablespace

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES

16. View tablespace

SELECT t.tablespace_name, round (SUM (bytes / (1024 * 1024)), 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 file

SELECT NAME FROM v$controlfile

18. View log files

SELECT MEMBER FROM v$logfile

19. View the date and method of database creation

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. Query user session

Select username,serial#,sid from v$session

Alter system kill session 'serial#,sid';-deletes related user sessions

twenty-two。 Query the number of connections in oracle

Select count (*) from v$session

23. Query the number of concurrent connections to oracle

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

24. View the version of oracle

Select banner from sys.v_$version

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.