Common Oracle query statements

1.-view the usage of Oracle tablespace:

Select D. tablespace_name "tablespace name", D. Status "status ",
(A. Bytes/1024/1024) as "Total MB ",
(A. bytes-Decode (F. bytes, null, 0, F. bytes)/1024/1024) as "How many m are used ",
(Decode (F. bytes, null, 0, F. bytes)/1024/1024) as "How many m are left ",
(A. bytes-Decode (F. bytes, null, 0, F. bytes)/1024/1024)/(A. Bytes/1024/1024) as "utilization ",
Decode (sign (. bytes-Decode (F. bytes, null, 0, F. bytes)/1024/1024)/(. bytes/1024/1024)-0.9), 1, 'remaining less than 10%, please consider expanding tablespaces ', 'normal') as "tip"
From SYS. dba_tablespaces D, SYS. sm $ ts_avail A, SYS. sm $ ts_free F
Where D. tablespace_name = A. tablespace_name
And F. tablespace_name (+) = D. tablespace_name;

 

2. view the number of Oracle connections

Select count (*) from V $ session -- number of connections

Select count (*) from V $ session where status = 'active' -- number of concurrent connections

Alter system set processes = value scope = spfile; -- restart the database to modify the connection

 

3. Resize a tablespace

-- Add data files to the tablespace

Alter tablespace users add datafile 'C: \ oracle \ ora81 \ oradata \ SID \ user002.dbf' size 100 m;
-- Increase the size of the original tablespace Data File
Alter database datafile 'C: \ oracle \ ora81 \ oradata \ SID \ users. dbf' resize 1000 m;

 

4. query the table space.

Select tablespace_name, file_id, Bytes/1024/1024, file_name

From dba_data_files order by file_id;