« Fandangos o SoleáXML Tools – Herramientas XML »

Oracle 11g – Ejercicio: Consultar los tablespaces

Posted in Oracle, SQL, Scripting, Sistemas on Feb 9th, 2010

Consultar los tablespaces en Oracle 11g.

– Lanzo el SQLplus del host donde reside el servidor de Oracle 11g. vía ssh. Y me conecto con el usuario sys como sysdba

$ ssh usuario@servidor-oracle-o-ip sqlplus

… usuario: sys as sysdba

…

– Consultamos los tablespaces

select tablespace_name,extent_management from dba_tablespaces;

SQL> select tablespace_name,extent_management from dba_tablespaces;

TABLESPACE_NAME            EXTENT_MAN
—————————— ———-
SYSTEM                   LOCAL
SYSAUX                   LOCAL
UNDOTBS1               LOCAL
TEMP                   LOCAL
USERS                   LOCAL
EXAMPLE                LOCAL

6 filas seleccionadas.

SQL>

– Ficheros que forman parte de los Tablespaces

SQL>      SELECT rpad(a.TABLESPACE_NAME,10) tablespace,rpad(a.FILE_NAME,40) fichero,
to_char(a.BYTES/1024/1024,’999.99′) MB,
to_char(a.increment_by*b.value/1024/1024,’99.99′) nextmb,
to_char(a.MAXBYTES/1024/1024,’9999.99′) maxmb
FROM DBA_DATA_FILES a, v$parameter b
where b.name=’db_block_size’;
2    3    4    5    6
TABLESPACE FICHERO                    MB        NEXTMB MAXMB
———- —————————————- ——- —— ——–
EXAMPLE    E:\APP\ORACLE11G\ORADATA\BBDDORACLE11G\E 100.00    .63 ########
SYSTEM       E:\APP\ORACLE11G\ORADATA\BBDDORACLE11G\S 690.00 10.00 ########
SYSAUX       E:\APP\ORACLE11G\ORADATA\BBDDORACLE11G\S 570.44 10.00 ########
UNDOTBS1   E:\APP\ORACLE11G\ORADATA\BBDDORACLE11G\U   45.00   5.00 ########
USERS       E:\APP\ORACLE11G\ORADATA\BBDDORACLE11G\U    5.00   1.25 ########

SQL>

SELECT rpad(a.TABLESPACE_NAME,10) tablespace,rpad(a.FILE_NAME,40) fichero,
to_char(a.BYTES/1024/1024,'999.99') MB,
to_char(a.increment_by*b.value/1024/1024,'99.99') nextmb,
to_char(a.MAXBYTES/1024/1024,'9999.99') maxmb
FROM DBA_DATA_FILES a, v$parameter b
where b.name='db_block_size';

– Para ver los “ocupantes” del tablespace SYSAUX, y cuánto ocupa cada uno consultaremos V$

SQL> select rpad(occupant_name,30), space_usage_kbytes from v$sysaux_occupants;

RPAD(OCCUPANT_NAME,30)	       SPACE_USAGE_KBYTES
------------------------------ ------------------
LOGMNR					     7744
LOGSTDBY				     1024
SMON_SCN_TIME				      256
PL/SCOPE				      384
STREAMS 				     1024
XDB					    99584
AO					    42496
XSOQHIST				    42496
XSAMD					    15936
SM/AWR					    22016
SM/ADVISOR				     7616

RPAD(OCCUPANT_NAME,30)	       SPACE_USAGE_KBYTES
------------------------------ ------------------
SM/OPTSTAT				     5440
SM/OTHER				     5952
STATSPACK					0
SDO					    47424
WM					     7296
ORDIM					    11200
ORDIM/PLUGINS					0
ORDIM/SQLMM					0
EM					   129088
TEXT					     5568
ULTRASEARCH				     7616

RPAD(OCCUPANT_NAME,30)	       SPACE_USAGE_KBYTES
------------------------------ ------------------
ULTRASEARCH_DEMO_USER			    13824
EXPRESSION_FILTER			     3968
EM_MONITORING_USER			     1536
TSM					      256
SQL_MANAGEMENT_BASE			     1728
AUTO_TASK				      320
JOB_SCHEDULER				      384

29 filas seleccionadas.

SQL>