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

DB_BLOCK_SIZE
- 11g - http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams050.htm#REFRN10031
- 12c - http://docs.oracle.com/database/121/REFRN/GUID-6DD6D804-D03D-4C57-A764-826EE0F3836B.htm#REFRN10031