Oracle

Hugepages en Linux

An Introduction to Linux Shell Scripting for DBAs

Oracle 11g: Usage of hugepages on Linux

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'\'')

Volcar un último archive log

SQL> ALTER system archive log CURRENT;