Collection of some useful oracle operations, tablespace-related

Typical use of tablespace creation:

 Create tablespace ts_name datafile 'd:/oracle/.../xxx. dbf' size 1024 m autoextend on next 500 m maxsize unlimited;

 

View the information of the existing tablespace:

Select upper (F. tablespace_name) "tablespace name", D. tot_grootte_mb "tablespace 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), '2017. 99 ') |' % '"usage Ratio (%)", F. total_bytes "Free Space (m)", F. max_bytes "maximum block (m)" from (select tablespace_name, round (sum (bytes)/(1024*1024), 2) total_bytes, round (max (bytes) // (1024*1024), 2) max_bytes from sys. dba_free_space group by tablespace_name) F, (select DD. tablespace_name, round (sum (DD. bytes)/(1024*1024), 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 F. tablespace_name;

 

There are two ways to delete an existing tablesapce and its associated datafile. This method is "arbitrary" and should be used with caution:

Drop tablespace tablespace_name including contents and datafiles; drop tablespace tablespace_name including contents cascade constraints;