Oracle Common SQL statements

View SQL statements for Oracle status

Select status from V$instance;

View SQL statements for Oracle control files

Select name from V$controlfile;

View SQL statements for Oracle data files

Select name from V$datafile;

View Oracle Online log files

SELECT * from V$logfile;

View Tablespace usage

Select UPPER (f.tablespace_name) "Table space name",
D.TOT_GROOTTE_MB "Table space size (M)",
D.tot_grootte_mb-f.total_bytes "used Space (M)",
To_char (ROUND (d.tot_grootte_mb-f.total_bytes)/d.tot_grootte_mb * 100,
2),
' 990.99 ') "Use ratio",
F.total_bytes "free Space (M)",
F.max_bytes "Max Block (M)"
From (SELECT Tablespace_name,
ROUND (SUM (BYTES)/(1024x768 * 1024x768), 2) Total_bytes,
ROUND (MAX (BYTES)/(1024x768), 2) max_bytes
From SYS. Dba_free_space
GROUP by Tablespace_name) F,
(SELECT DD.) Tablespace_name,
ROUND (SUM (DD). BYTES)/(1024x768 * 1024x768), 2) TOT_GROOTTE_MB
From SYS. Dba_data_files DD
GROUP by DD. Tablespace_name) D
WHERE D.tablespace_name = F.tablespace_name
ORDER by 4 DESC;

View the data file for a tablespace

Select File_name,tablespace_name from Dba_data_files
where tablespace_name= ' SYSTEM ';

Add a tablespace file

Alter tablespace USERS add datafile ' D:\orcle\product\10.2.0\oradata\orcl\USERS02. DBF ' size 10M autoextend on maxsize 20G;

Resize the data file(not recommended)

ALTER DATABASE DataFile ' D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01. DBF ' Resize 7600M;

Usage hit ratio for data buffers

Data buffer usage hit =1-(physical reads/(DB block gets + consistent gets).

Select round (1-((SELECT value from V$sysstat WHERE name= ' physical reads ')/
(SELECT value from V$sysstat WHERE name= ' consistent gets ') +
(SELECT value from V$sysstat WHERE name= ' db block gets '))
), 2) *100| | ' % ' from dual;

Use the following statement to view the hit ratio of a data buffer:

SELECT name, value from V$sysstat WHERE name in (' db block gets ', ' consistent gets ', ' physical reads ');

The result of the query buffer cache should be more than 90% hit rate, otherwise you need to increase the size of the data buffer.

Whether the query table space is auto-extended:

Select Tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name = ' SYSTEM ';

Oracle Common SQL statements