Tabla de Contenidos

Oracle

Notas de Oracle

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'

Consulta para conocer sesiones activas 

SELECT COUNT(SES.SID)
FROM  v\$session SES, V\$SQLAREA SQL, V\$SESSION_WAIT WA, V\$PROCESS P
WHERE SES.STATUS='\''ACTIVE'\''
AND SES.SQL_ID=SQL.SQL_ID
AND SES.SID=WA.SID
AND SES.paddr=p.addr
AND SES.USERNAME NOT IN ('\''SYS'\'','\''SYSMAN'\'','\''MDSYS'\'')

Otorgar acceso de lectura a todas las tablas a determinado usuario 

GRANT SELECT ANY TABLE TO EXT_USER_REPORTING WITH ADMIN OPTION;

Volcar un último archive log 

SQL> ALTER system archive log CURRENT;

Referencias oficiales