List common Oracle maintenance statements

The following articles mainly introduce common statements for Oracle maintenance. This article describes more than 20 related statements. Of course, these statements are often used in the actual use of Oracle, if you are an Oracle enthusiast. The following article will be meaningful to you.

1. view the table space name and size.

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;

2. view the name and size of the tablespace physical file.

Select tablespace_name, file_id, file_name, round (bytes/(1024*1024), 0) total_space from dba_data_files order by tablespace_name;

3. Check the rollback segment name and size.

Select segment_name, tablespace_name, r. status, (initial_ext Ent/1024) InitialExtent, (next_extent/1024) NextExtent, max_extents, v. curext CurExtent From dba_rollback_segs r, v $ rollstat v Where r. segment_id = v. usn (+) order by segment_name;

4. View Control Files

Select name from v $ controlfile;

5. view log files

Select member from v $ logfile;

6. View table space usage

Select sum (bytes)/(1024*1024) as free_space, tablespace_name from dba_free_space group by tablespace_name; select. TABLESPACE_NAME,. bytes total, B. bytes used, C. bytes free, (B. BYTES * 100)/. BYTES "% USED", (C. BYTES * 100)/. BYTES "% FREE" from sys. SM $ TS_AVAIL A, SYS. SM $ TS_USED B, SYS. SM $ TS_FREE c where. TABLESPACE_NAME = B. TABLESPACE_NAME and. TABLESPACE_NAME = C. TABLESPACE_NAME;

7. view database objects

Select owner, object_type, status, count (*) count # from all_objects group by owner, object_type, status;

8. In common Oracle maintenance statements, you need to view the database version.

Select version FROM Product_component_version Where SUBSTR (PRODUCT, 1, 6) = 'oracle ';

9. view the database creation date and archiving method

Select Created, Log_Mode, Log_Mode From V $ Database;

10. view all objects currently

SQL> select * from tab;

11. Create an empty table with the same structure as Table.

SQL> create table B as select * from a where 1 = 2; SQL> create table B (b1, b2, b3) as select a1, a2, a3 from a where 1 = 2;

12. Check the database size and space usage

SQL> col tablespace format a20 SQL> select B. file_id

File ID, B. tablespace_name

Tablespace, B. file_name

Physical file name, B. bytes

Total number of bytes, (B. bytes-sum (nvl (a. bytes, 0 )))

Used, sum (nvl (a. bytes, 0 ))

Remaining, sum (nvl (a. bytes, 0)/(B. bytes) * 100

Percentage left from dba_free_space a, dba_data_files B

Where a. file_id = B. file_id group by B. tablespace_name, B. file_name, B. file_id, B. bytes

Order by B. tablespace_name/dba_free_space -- table space remaining

Dba_data_files -- data file space usage

13. view existing rollback segments and their statuses

SQL> col segment format a30 SQL> SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, SEGMENT_ID, FILE_ID, STATUS FROM DBA_ROLLBACK_SEGS;

14. view the data file placement path

SQL> col file_name format a50 SQL> select tablespace_name, file_id, bytes/1024/1024, file_name from dba_data_files order by file_id;

15. display the current connected user

SQL> show user

16. use SQL * Plus as a calculator

SQL> select 100*20 from dual;

17. connection string

SQL> select column 1 | Column 2 from table 1; SQL> select concat (column 1, column 2) from table 1;

18. query the current date

SQL> select to_char (sysdate, 'yyyy-mm-dd, hh24: mi: ss') from dual;

19. Data replication between users

 
 

  
  
SQL> copy from user1 to user2 create table2 using select * from table1; 
 
 

20. order by cannot be used in a view, but it can be replaced by group by for sorting purpose.

 
 

  
  
SQL> create view a as select b1,b2 from b group by b1,b2; 
 
 

21. Create a user through authorization

 
 

  
  
SQL> grant connect,resource to test identified by test; SQL> conn test/test 
 
 

The above content is an introduction to common Oracle maintenance statements. I hope you will get some benefits.