[Prog] SQL Useful Queries

Hello readers,

I’ll share with you useful queries that I use continuously on SQL Developer about tablespaces and partitions:

Tablespaces’s List

select * from user_tablespaces;

Available space:

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

List the tablespaces in descendent order based on the free space

select * from dba_free_space order by bytes desc;

Total size of the Tablespaces, formatted in GB

SELECT tablespace_name, round(sum(BYTES/1024/1024/1024),0) "Total GB" FROM dba_data_files b WHERE tablespace_name NOT LIKE 'TEMP%' GROUP BY b.tablespace_name;

List of tables that are partitioned

select table_name from user_tables where partitioned='YES';

Select only from one partition

select * from [insert_table_name] partition([insert_partition_name]);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s