• An Introduction to Linux Shell Scripting for DBAs
• Oracle 11g: Usage of hugepages on Linux

List all users/schema

         SET PAGESIZE 50
         SELECT DISTINCT USERNAME FROM DBA_USERS;

List ONLY your tables

         SET PAGESIZE 80
         COL TABLE__NAME FORMAT a30;
         COL TABLE_TYPE FORMAT a30;
         SELECT * FROM CAT;

List the Oracle Tablespaces and free space

         SELECT * FROM v$TABLESPACE;

List the free space for all Oracle Tablespaces within a Database

         SELECT * FROM DBD_FREE_SPACE WHERE TABLESPACE_NAME = 'IATOR';

List the space ALREADY USED for a specific Oracle Tablespace

         SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'myTABLESPACE';

List extents for segments

         SELECT TABLESPACE_NAME, COUNT(*), MAX(blocks), SUM(blocks) FROM dba_free_space GROUP BY TABLESPACE_name;

See all the tables you have access to:

         SET PAGESIZE 90
         COLUMN "OBJECT NAME" format a26
         COLUMN "OBJECT TYPE" format a20
         SELECT object_name    "OBJECT NAME",
                object_type    "OBJECT TYPE"
         FROM
                user_objects
         WHERE
                object_type = 'TABLE';

See your TABLESPACE

         SELECT TABLESPACE_NAME FROM ALL_TABLES WHERE TABLE_NAME = '<your_table>'

Consulta para calcular el espacio total, el ocupado y el espacio libre de un TABLESPACE :

SET pagesize 0
SET numf '9999999.99'
SELECT NVL(b.free,0.0),a.total,100 - trunc(NVL(b.free,0.0)/a.total * 1000) / 10 prc
FROM (
SELECT tablespace_name,SUM(bytes)/1024/1024 total
FROM dba_data_files GROUP BY tablespace_name) A
LEFT OUTER JOIN
( SELECT tablespace_name,SUM(bytes)/1024/1024 free
FROM dba_free_space GROUP BY tablespace_name) B
ON a.tablespace_name=b.tablespace_name WHERE a.tablespace_name='NOMBRE_TABLESPACE'