Hi reader,

with PL/SQL Developer you have an oracle development tool which is

• low priced
• rich featured 
• easy to use.

The newest update covers UNICODE support.

http://www.allroundautomations.com/update/update001.html

HTH
Karl

Hi reader,

If you copy a schema from a database to another you could check the integrity of the operation via the DBA_OBJECTS View. A good idea but the data dictionary view is incomplete - not all object types are supported.

SELECT DISTINCT Do.Object_Type
  FROM Dba_Objects Do
 ORDER BY Do.Object_Type

;

SQL> SELECT DISTINCT Do.Object_Type
  2    FROM Dba_Objects Do
  3   ORDER BY Do.Object_Type

  4  ;

OBJECT_TYPE
——————-
CHAIN
CLUSTER
CONSUMER GROUP
CONTEXT
DATABASE LINK
DIRECTORY
EVALUATION CONTEXT
FUNCTION
INDEX
INDEX PARTITION
INDEXTYPE
JOB
JOB CLASS
LIBRARY
LOB
LOB PARTITION
MATERIALIZED VIEW
OPERATOR
PACKAGE
PACKAGE BODY

PROCEDURE
PROGRAM
QUEUE
RESOURCE PLAN
RULE
RULE SET
SCHEDULE
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
TRIGGER
TYPE
TYPE BODY
UNDEFINED
VIEW
WINDOW
WINDOW GROUP
XML SCHEMA

39 rows selected

SQL>

In a schema of the same database i queried all object types of the DBA_OBJECTS view i found object types not listed : 

SELECT Uml.Log_Table
  FROM User_Mview_Logs Uml
;

SELECT Urc.NAME,
              Urc.Refgroup
  FROM User_Refresh_Children Urc
;

SELECT Ur.Refgroup,
       Ur.Rname
  FROM User_Refresh Ur

;

 

SQL> SELECT Uml.Log_Table
  2    FROM User_Mview_Logs Uml

;

LOG_TABLE
——————————
MLOG$_COMPANY

SQL>

SQL> SELECT Ur.Refgroup,
  2         Ur.Rname
  3    FROM User_Refresh Ur

;

  REFGROUP RNAME
———- ——————————
       141 GMRV_FACTS_RGR_IFX
SQL>

SQL> SELECT Urc.NAME,
  2                 Urc.Refgroup
  3    FROM User_Refresh_Children Urc

;

NAME                             REFGROUP
—————————— ———-
GMRV_FACTS_MAX_PARENT_IFX             141
GMRV_FACTS_COUNTPART_IFX              141
GMRV_FACTS_MAX_VAL_IFX                141

SQL>  

As you can see clearly Refresh groups and Materialized View Logs are not supported and documented via the (DBA|ALL|USER)_OBJECTS Views.

HTH KARL

Hi reader,

with oracle 10.2 you have something like a segment fragment which could release space not needed anymore. First you enable row movement to enable reorganization. Then you start to shrink. With the dba_segments view you can query the segment properties of your table or index.

SELECT Us.Segment_Name,
       Us.Segment_Type,
       Us.Bytes,
       Us.Blocks
  FROM User_Segments Us
 WHERE Us.Segment_Name = ‘EVENTLOG’

;

SQL>                                      
SQL> SELECT Us.Segment_Name,
  2         Us.Segment_Type,
  3         Us.Bytes,
  4         Us.Blocks
  5    FROM User_Segments Us
  6   WHERE Us.Segment_Name = ‘EVENTLOG’
  7  ;

SEGMENT_NAME                                SEGMENT_TYPE            BYTES     BLOCKS
——————————————- —————— ———- ———-
EVENTLOG                                    TABLE                84934656      10368

SQL>

 

Like a diskdefragmenter must be able to move disk blocks of a file the table defragmentation only works effective if rows are allowed to be moved from one oracle block to another. This means the rowid of a table row changes.

ALTER TABLE EVENTLOG ENABLE ROW MOVEMENT

;
ALTER TABLE EVENTLOG SHRINK SPACE

;

SQL> ALTER TABLE EVENTLOG ENABLE ROW MOVEMENT;
Table altered
SQL> ALTER TABLE EVENTLOG SHRINK SPACE;
Table altered
SQL>

In this example the number blocks could be reduced from 10368 to 128! Also this means that the high water mark which is the end position of the Full Table Scan is reseted too causing reduced resource usage for Full Table Scans.

SQL>                                                                                
SQL> SELECT Us.Segment_Name,                                                        
  2         Us.Segment_Type,                                                        
  3         Us.Bytes,                                                               
  4         Us.Blocks                                                               
  5    FROM User_Segments Us                                                        
  6   WHERE Us.Segment_Name = ‘EVENTLOG’
  7  ;                                           
                                                                                    
SEGMENT_NAME                                SEGMENT_TYPE            BYTES     BLOCKS
——————————————- —————— ———- ———-
EVENTLOG                                    TABLE                 1048576        128
                                                                                    
SQL>                                                                                

How do you detect candidates for shrinking? simply look into Segment Advisor Recommendations of the database Homepage of the DbConsole or do this by SQL using the Dba_Advisor_Findings view :

SELECT Daf.Task_Id,
       Daf.Finding_Id,
       Daf.Task_Name,
       Daf.TYPE,
       Daf.Object_Id,
       Daf.Message
  FROM Dba_Advisor_Findings Daf
 WHERE Daf.Task_Name LIKE ‘SYS_AUTO_SPCADV%’
   AND Message LIKE ‘%EVENTLOG%’

;

SQL>
SQL> SELECT Daf.Task_Id,
  2         Daf.Finding_Id,
  3         Daf.Task_Name,
  4         Daf.TYPE,
  5         Daf.Object_Id,
  6         Daf.Message
  7    FROM Dba_Advisor_Findings Daf
  8   WHERE Daf.Task_Name LIKE ‘SYS_AUTO_SPCADV%’
  9     AND Message LIKE ‘%EVENTLOG%’
 10  ;

   TASK_ID FINDING_ID TASK_NAME                      TYPE         OBJECT_ID MESSAGE
———- ———- —————————— ———– ———- ——————————————————————————–
     14837         10 SYS_AUTO_SPCADV_250201882006   INFORMATION         28 Zeilenverschiebung der Tabelle EVENTLOG aktivieren und Verkleinerung vorneh
     14870         11 SYS_AUTO_SPCADV_25041982006    INFORMATION         28 Zeilenverschiebung der Tabelle EVENTLOG aktivieren und Verkleinerung vorneh
     15127          7 SYS_AUTO_SPCADV_410202182006   INFORMATION         23 Zeilenverschiebung der Tabelle EVENTLOG aktivieren und Verkleinerung vorneh
     15228          1 SYS_AUTO_SPCADV_921202282006   INFORMATION          1 Zeilenverschiebung der Tabelle EVENTLOG aktivieren und Verkleinerung vorneh

SQL>

HTH Karl

Links

• Segment Shrink