Overblog Suivre ce blog
Editer l'article Administration Créer mon blog
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 .............  

Haut de Page www.dba-ora.fr

 

 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Oracle Tablespace

Partager cet article

Repost 0
Published by Daniel Roesch - dans TABLESPACE
commenter cet article

commentaires