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>