Mardi 1 mai 2012
2
01
/05
/Mai
/2012 12:04
Nom et taille des objets dans le Tablespace SYSAUX.
Comment voir, lister le contenu du Tablespace SYSAUX.
Comment déplacer, migrer un objet du Tablespace SYSAUX.
Voir les objets du Tablespace SYSAUX.
On interroge la vue V$SYSAUX_OCCUPANTS pour lister les objets contenu dans le Tablespace SYSAUX.
SQL> SET LINESIZE 130
SQL> COL occupant_desc FORMAT A52
SQL> COL occupant_name FORMAT A21
SQL> COL schema_name FORMAT A10
SQL>
SQL> SELECT occupant_desc,
occupant_name,
schema_name,
space_usage_kbytes
FROM v$sysaux_occupants
ORDER BY space_usage_kbytes DESC;
OCCUPANT_DESC OCCUPANT_NAME SCHEMA_NAM SPACE_USAGE_KBYTES
---------------------------------------------------- --------------------- ---------- ------------------
Enterprise Manager Repository EM SYSMAN 49728
XDB XDB XDB 49216
Server Manageability - Optimizer Statistics History SM/OPTSTAT SYS 37504
Oracle Spatial SDO MDSYS 33216
Server Manageability - Automatic Workload Repository SM/AWR SYS 31360
Analytical Workspace Object Table AO SYS 21248
OLAP API History Tables XSOQHIST SYS 21248
OLAP Catalog XSAMD OLAPSYS 15936
Server Manageability - Advisor Framework SM/ADVISOR SYS 7232
Server Manageability - Other Components SM/OTHER SYS 6272
Workspace Manager WM WMSYS 6080
OCCUPANT_DESC OCCUPANT_NAME SCHEMA_NAM SPACE_USAGE_KBYTES
---------------------------------------------------- --------------------- ---------- ------------------
LogMiner LOGMNR SYSTEM 6080
Oracle Text TEXT CTXSYS 4736
Expression Filter System EXPRESSION_FILTER EXFSYS 3712
Enterprise Manager Monitoring User EM_MONITORING_USER DBSNMP 1600
Logical Standby LOGSTDBY SYSTEM 896
Oracle Streams STREAMS SYS 512
Oracle interMedia ORDSYS Components ORDIM ORDSYS 512
Unified Job Scheduler JOB_SCHEDULER SYS 448
Oracle Data Mining ODM DMSYS 256
Oracle Transparent Session Migration User TSM TSMSYS 256
Oracle Ultra Search Demo User ULTRASEARCH_DEMO_USER WK_TEST 0
OCCUPANT_DESC OCCUPANT_NAME SCHEMA_NAM SPACE_USAGE_KBYTES
---------------------------------------------------- --------------------- ---------- ------------------
Oracle interMedia ORDPLUGINS Components ORDIM/PLUGINS ORDPLUGINS 0
Statspack Repository STATSPACK PERFSTAT 0
Oracle Ultra Search ULTRASEARCH WKSYS 0
Oracle interMedia SI_INFORMTN_SCHEMA Components ORDIM/SQLMM SI_INFORMT 0
N_SCHEMA
26 ligne(s) sélectionnée(s).
SQL>
|
Migrer un objet hors du Tablespace SYSAUX.
Pour déplacer un objet du Tablespace SYSAUX vers un autre espace disque logique, on utilise le champ MOVE_PROCEDURE de la vue
V$SYSAUX_OCCUPANTS.
Ce champ contient la procédure à exécuter pour déplacer l'objet dans un autre Tablespace.
SQL> SET LINESIZE 130
SQL> COL occupant_name FORMAT A25
SQL> COL move_procedure FORMAT A36
SQL> COL move_procedure_desc FORMAT A58
SQL>
SQL> SELECT occupant_name,
move_procedure,
move_procedure_desc
FROM v$sysaux_occupants
ORDER BY occupant_desc DESC;
OCCUPANT_NAME MOVE_PROCEDURE MOVE_PROCEDURE_DESC
------------------------- ------------------------------------ ------------------------------------------
XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE Move Procedure for XDB
WM DBMS_WM.move_proc Move Procedure for Workspace Manager
JOB_SCHEDULER *** MOVE PROCEDURE NOT APPLICABLE ***
STATSPACK Use export/import (see export parameter fi
SM/OTHER *** MOVE PROCEDURE NOT APPLICABLE ***
SM/OPTSTAT *** MOVE PROCEDURE NOT APPLICABLE ***
SM/AWR *** MOVE PROCEDURE NOT APPLICABLE ***
SM/ADVISOR *** MOVE PROCEDURE NOT APPLICABLE ***
ULTRASEARCH_DEMO_USER MOVE_WK Move Procedure for Oracle Ultra Search
ULTRASEARCH MOVE_WK Move Procedure for Oracle Ultra Search
TSM *** MOVE PROCEDURE NOT APPLICABLE ***
OCCUPANT_NAME MOVE_PROCEDURE MOVE_PROCEDURE_DESC
------------------------- ------------------------------------ ------------------------------------------
TEXT DRI_MOVE_CTXSYS Move Procedure for Oracle Text
STREAMS *** MOVE PROCEDURE NOT APPLICABLE ***
SDO MDSYS.MOVE_SDO Move Procedure for Oracle Spatial
ORDIM/SQLMM *** MOVE PROCEDURE NOT APPLICABLE ***
ORDIM *** MOVE PROCEDURE NOT APPLICABLE ***
ORDIM/PLUGINS *** MOVE PROCEDURE NOT APPLICABLE ***
ODM MOVE_ODM Move Procedure for Oracle Data Mining
XSAMD DBMS_AMD.Move_OLAP_Catalog Move Procedure for OLAP Catalog
XSOQHIST DBMS_XSOQ.OlapiMoveProc Move Procedure for OLAP API History Tables
LOGMNR SYS.DBMS_LOGMNR_D.SET_TABLESPACE Move Procedure for LogMiner
LOGSTDBY SYS.DBMS_LOGSTDBY.SET_TABLESPACE Move Procedure for Logical Standby
OCCUPANT_NAME MOVE_PROCEDURE MOVE_PROCEDURE_DESC
------------------------- ------------------------------------ ------------------------------------------
EXPRESSION_FILTER *** MOVE PROCEDURE NOT APPLICABLE ***
EM emd_maintenance.move_em_tblspc Move Procedure for Enterprise Manager Repo
EM_MONITORING_USER *** MOVE PROCEDURE NOT APPLICABLE ***
AO DBMS_AW.MOVE_AWMETA Move Procedure for Analytical Workspace Ob
26 ligne(s) sélectionnée(s).
SQL>
|
Déplacer l'objet LOGMNR de SYSAUX vers le Tablespace DATA.
Ici nous déplaçons LOGMNR de SYSAUX vers un Tablespace DATA avec l'aide du Package SYS.DBMS_LOGMNR_D.SET_TABLESPACE, et on ensuite nous vérifions que l'objet est bien
migré vers le nouveau Tablespace en interrogeant la vue DBA_SEGMENTS.
SQL> EXECUTE SYS.DBMS_LOGMNR_D.SET_TABLESPACE('DATA');
Procédure PL/SQL terminée avec succès.
SQL>
SQL> COL segment_name FORMAT A35
SQL> SELECT segment_name,
tablespace_name
FROM dba_segments
WHERE tablespace_name='DATA';
SEGMENT_NAME TABLESPACE_NAME
----------------------------------- -------------------
LOGMNR_SESSION_EVOLVE$ DATA
LOGMNR_SESSION_EVOLVE$_PK DATA
LOGMNR_HEADER1$ DATA
LOGMNR_HEADER2$ DATA
LOGMNR_UID$ DATA
LOGMNR_UID$_PK DATA
LOGMNRC_DBNAME_UID_MAP DATA
LOGMNRC_DBNAME_UID_MAP_PK DATA
LOGMNR_DICTSTATE$ DATA
LOGMNR_DICTIONARY$ DATA
LOGMNR_OBJ$ DATA
SEGMENT_NAME TABLESPACE_NAME
----------------------------------- -------------------
LOGMNR_USER$ DATA
LOGMNRC_GTLO DATA
LOGMNRC_GTLO_PK DATA
LOGMNRC_GTCS DATA
LOGMNRC_GSII DATA
.............
|
| Liens en rapport avec l'article et qui sont susceptibles de vous intéresser |
| • Oracle Tablespace |
