Managing Segments

1] When was my table last modified or accessed ?

1.1 Last modified ?

1.1.1 ORA_ROWSCN behaviour : the most recent change to the row

In 10g there is a new pseudocolumn of any table that is not fixed or external. It represents the SCN of the most recent change to a given row, that is, the latest COMMIT operation for the row.

• ORA_ROWSCN is updated

SQL> select a,b,c,ora_rowscn from auc ;

         A B               C                                 ORA_ROWSCN

---------- --------------- --------------- ----------------------------

        11 test 1          SL_NO_COL_B                    9801961903153

        12 test 2          SL_NO_COL_B                    9801961903153

        13 test 2          SL_NO_COL_B                    9801961903153

        14 test 2          SL_NO_COL_B                    9801963336927

SQL> update sce.auc t set B='test 2 2' where a=14 ;

1 row updated.

SQL> commit ;

Commit complete.

col ORA_ROWSCN for 99999999999999

SQL> select a,b,c,ora_rowscn from auc ;

         A B               C                                 ORA_ROWSCN

---------- --------------- --------------- ----------------------------

        11 test 1          SL_NO_COL_B                    9801961903153

        12 test 2          SL_NO_COL_B                    9801961903153

        13 test 2          SL_NO_COL_B                    9801961903153

        14 test 2 2        SL_NO_COL_B                    9802380647979

• ORA_ROWSCN is updated at the block level when one row is modified

SQL> update sce.auc t set B='test 2 3' where a=11 ;

1 row updated.

SQL> commit ;

Commit complete.

SQL> select a,b,c,ora_rowscn,dbms_rowid.rowid_block_number(rowid) blockno from auc ;

         A B               C                    ORA_ROWSCN    BLOCKNO

---------- --------------- --------------- --------------- ----------

        11 test 2 3        SL_NO_COL_B       9802481246103     536629

        12 test 2          SL_NO_COL_B       9802481246103     536629

        13 test 2          SL_NO_COL_B       9802481246103     536629

        14 test 2 2        SL_NO_COL_B       9802380647979     536631

• To track modification at the row level, enable ROWDEPENDENCIES

Taken from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:517105100346104196

create table dept (deptno, dname, loc, data, constraint dept_pk primary key(deptno))

 ROWDEPENDENCIES

 as select deptno, dname, loc, rpad('*',3500,'*') from scott.dept;

1.1.2 Query ORA_ROWSCN

When was the last time my table was modified ?

SQL> select max(scn_to_timestamp(ora_rowscn)) from owner.ad_groupes ;

MAX(SCN_TO_TIMESTAMP(ORA_ROWSCN))

---------------------------------------------------------------------------

11-OCT-11 02.30.27.000000000 AM

How many and when rows were modified ?

select ora_rowscn , count(1) number_of_row from owner.ad_groupes group by ora_rowscn order by 1 desc ;

select distinct sequence# , ora_rowscn , first_time , next_time , number_of_row

from

( select ora_rowscn , count(1) number_of_row from owner.ad_groupes a group by ora_rowscn ) a , v$archived_log b

where ora_rowscn between FIRST_CHANGE# and NEXT_CHANGE#

order by 1 desc ;

Restriction to this method, example : Generate SQL

select 'select max(scn_to_timestamp(ora_rowscn)) from '||owner||'.'||table_name||' ; ' from dba_tables where owner = '<APP>' order by 1

select max(scn_to_timestamp(ora_rowscn)) from <APP>.AGENT ;

select max(scn_to_timestamp(ora_rowscn)) from <APP>.ARCHISTA ;

ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

ORA-08181: specified number is not a valid system change number

Still from Tom :

could well be that the SCN is just too old (we only keep a mapping for a short period of time)

Since, we can’t go very long back in time, from http://jonathanlewis.wordpress.com/2010/09/03/last-modified/ , we can have an idea of the date before the last modification happened :

select 'select '''||owner||'.'||table_name||''' Tab , min(first_time) last_modified_date_was_before from v$log_history where first_change# >= ( select max(ora_rowscn) from '||owner||'.'||table_name||' ) ; '

from dba_tables where owner = '<APP>' order by 1

select '<APP>.AGENT' Tab , min(first_time) last_modified_date_was_before from v$log_history where first_change# >= ( select max(ora_rowscn) from <APP>.AGENT ) ;

TAB          last_modified_date_was_before

<APP>.AGENT  03/04/2013 18:00:03

1.2 Last accessed ?

The SYS.COL_USAGE$ Table is of great interest because it stores SELECT access, mostly to help the DBMS_STATS package to gather Histograms on relevant columns.

Here, we query as follow :

-- last SELECT access on tables , columns

SELECT   dba_tab_columns.owner, dba_tab_columns.table_name,

         dba_tab_columns.column_name, --SYS.col_usage$.*

              a.timestamp

    FROM SYS.col_usage$ a, dba_objects, dba_tab_columns

   WHERE obj# = dba_objects.object_id

     AND dba_tab_columns.owner = dba_objects.owner

     AND dba_tab_columns.table_name = dba_objects.object_name

     AND intcol# = dba_tab_columns.column_id

     AND dba_objects.owner = 'APP01'

--     AND object_name = 'T'

ORDER BY timestamp desc -- intcol#

OWNER	TABLE_NAME	COLUMN_NAME	TIMESTAMP
APP01	FORMULES	TYPE	15/11/2012 07:25
APP01	FORMULES	SYSTEME	15/11/2012 07:25
APP01	ETABLISSEMENTMODULEINTERNET	CODEETAB	15/11/2012 07:25
APP01	ECRITUREBUDGETAIRE	ID_BUDGET	02/05/2011 17:07
APP01	GEDNIVEAU	IDNIVEAU	02/05/2011 17:07
APP01	GEDNIVEAU	PCNOM	02/05/2011 17:07
APP01	GEDPLANDECLASSEMENT	PCNOM	02/05/2011 17:07
APP01	BUDGETS	ID_BUDGET	02/05/2011 17:07
APP01	GEDDOSSIER	IDNIVEAU	02/05/2011 17:07

-- last SELECT access on tables

SELECT   b.owner, b.object_name,

         -- dba_tab_columns.column_name, SYS.col_usage$.*

              max(a.timestamp)

    FROM SYS.col_usage$ a, dba_objects b

   WHERE obj# = b.object_id AND b.owner = 'APP01'

--     AND object_name = 'T'

group by b.owner,b.object_name,a.timestamp

ORDER BY a.timestamp desc -- intcol#

OWNER	OBJECT_NAME	MAX(A.TIMESTAMP)
APP01	ETABLISSEMENTMODULEINTERNET	18/11/2012 06:05
APP01	FORMULES	18/11/2012 06:05
APP01	AGENTS	08/08/2012 15:58
APP01	BUDGETS	08/08/2012 15:58
APP01	CATEGORIEETAB	08/08/2012 15:58
APP01	COMPTE	08/08/2012 15:58

2] « Flash-Thompson » a Table

2.1 Get a table « Flashed-back » right after a Drop [ using the 10G+ BIN$ ]

Use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover objects from the recycle bin. You can specify either the name of the table in the recycle bin or the original table name.

·  A table and all of its dependent objects (indexes, LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together.

It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.

·  The recycle bin does not preserve referential constraints on a table (though other constraints will be preserved if possible). If a table had referential constraints before it was dropped (that is, placed in the recycle bin), then re-create any referential constraints after you retrieve the table from the recycle bin with Flashback Drop.

select a.table_name,index_name from dba_tables a , dba_indexes b

where a.owner = 'APP03' and a.table_name = b.table_name and a.owner = b.owner

select original_name, droptime

from dba_recyclebin where owner='APP03' and type='TABLE' order by 1

select 'FLASHBACK TABLE "'||object_name||'" TO BEFORE DROP; '

from dba_recyclebin where owner='APP03' and type='TABLE' -- and droptime < '2009-08-24:13:02:00' ;

select 'drop TABLE '||owner||'.'||original_name||'; '

from dba_recyclebin where owner='XD_LARA' and type='TABLE' and droptime < '2009-08-24:13:02:00'

order by original_name ;

select 'FLASHBACK TABLE "'||object_name||'" TO BEFORE DROP; '

from dba_recyclebin where owner='XD_LARA' and type='TABLE' and droptime < '2009-08-24:13:02:00' ;

FLASHBACK TABLE EDI_BOL_FREIGHT_CHARGES TO BEFORE DROP;

Flashback complete.

As seen above, check Index and re-create Foreign Keys.

2.2 Get a Table Back to the Past [ using UNDO ]

select count(*) from APP02.IMPRIMANTE as of timestamp to_timestamp('04-01-2010 23:00:00','DD-MM-YYYY hh24:mi:ss') ; -- 6

select * from APP02.IMPRIMANTE

alter table APP02.IMPRIMANTE enable row movement ;

FLASHBACK TABLE APP02.IMPRIMANTE TO TIMESTAMP to_timestamp('04-01-2010 23:00:00','DD-MM-YYYY hh24:mi:ss') ;

alter table APP02.IMPRIMANTE disable row movement ;

3] Remove all existing objects in a schema

This method guarantees that GRANTS given to the Schema owner to Others Schema Objects are Kept [ from my excellent colleague DD ] :

--

-- Creation: 01/2010, by DD

-- modified by AUC 22/01/10, non interactif mode , purge , db_link added

-- modified by AUC 10/02/10, commentaire sur les affichages d'execution & database link non traites

-- But : suppression de tous les objets d'un schema

-- modif by DD 27/04/11, traitement des collections de types

-- Usage : @vide_schema nom_du_schema O|N

-- Arg 1 : schema to empty

-- Arg 2 : Y/N  Y to execute the code

--              N to see the objects to be removed

set serveroutput on

-- Prompt Usage :  @vide_schema nom_du_schema Y|N

Prompt

set verify off

set feedback off

-- saisie des variables

define schema=&1

define exe=&2

----------------------------------------------------------------

-- declarations

----------------------------------------------------------------

declare

-- declaration des curseurs utiles

--

-- Materialized Views

cursor c_mv( b_owner in varchar2)

  is

    SELECT object_type, object_name, owner

    FROM   dba_objects

    where  owner = b_owner and object_type = 'MATERIALIZED VIEW';

-- Collections

cursor c_coll( b_owner in varchar2)

is

SELECT TYPE_NAME , OWNER

FROM DBA_COLL_TYPES

         WHERE OWNER=b_owner

         AND ELEM_TYPE_NAME IS NOT NULL;

-- Simple Types

cursor c_type( b_owner in varchar2)

is

SELECT OBJECT_NAME, OWNER

FROM DBA_OBJECTS

WHERE OBJECT_TYPE ='TYPE'

AND OWNER=b_owner

AND OBJECT_NAME NOT IN (

         SELECT TYPE_NAME

                FROM DBA_COLL_TYPES

                WHERE OWNER=b_owner

                AND ELEM_TYPE_NAME IS NOT NULL

                );

-- trigger schemas and database

-- ( table triggers are dropped with the table ...)

cursor c_trig( b_owner in varchar2)

  is

    SELECT trigger_name, owner

        FROM DBA_TRIGGERS

        WHERE  base_object_type <> 'TABLE'

        AND  base_object_type <> 'VIEW'

        AND OWNER = b_owner;

-- Other objects

cursor c_obj( b_owner in varchar2)

  is

    SELECT object_type, object_name, owner, created

    FROM   dba_objects

    where  owner = b_owner and object_type not in (

    'INDEX','CCONSUMER GROUP','EVALUATION CONTEXT','INDEX PARTITION',

    'JAVA DATA','JOB','JOB CLASS','LOB','LOB PARTITION','PROGRAM',

    'QUEUE','RESOURCE PLAN','RULE','RULE SET','SCHEDULE','TABLE PARTITION',

    'UNDEFINED','WINDOW','WINDOW GROUP','XML SCHEMA', 'PACKAGE BODY', 'TRIGGER',

    'MATERIALIZED VIEW', 'DATABASE LINK', 'TYPE', 'JAVA CLASS' )

    and    ( object_type <> 'TABLE' or object_name not like 'BIN$%')

    order by 1,4 desc ;

-- to count the objects

cursor c_compte_obj (b_owner in varchar2)

is

select object_type , count(object_name) nb_objets

from dba_objects

where owner=b_owner and ( object_name not like 'BIN$%')

group by object_type;

-- variables

l_execute varchar2(2000);

nb integer;

nom_schema varchar(30) := '&schema';

err_msg varchar(100);

exe char(1) := '&exe';

--------------------------------------------------------------

-- Start

--------------------------------------------------------------

begin

dbms_output.enable(100000);

DBMS_OUTPUT.put_line('** Start Empty schema &schema '||TO_char(sysdate,'DD/MM/YYYY HH24:MI:SS') );

select count(object_name) into nb

    from dba_objects

    where owner= upper('&schema') and object_type != 'DATABASE LINK' ;

if (nb=0) then

  DBMS_OUTPUT.put_line('Schéma ALREADY Empty !');

  ELSE

  BEGIN

  DBMS_OUTPUT.put_line('there are '||nb ||' object(s) to remove');

  -- MVs first to avoid error on the drop of the associated table

  DBMS_OUTPUT.put_line('Drop VMs');

  BEGIN

  for r_mv in c_mv( b_owner => upper(nom_schema)) loop

     l_execute:= 'drop materialized view "'||r_mv.owner||'"."'||r_mv.object_name||'"';

--     DBMS_OUTPUT.put_line(l_execute || '...');

     if exe = 'O' OR exe='o' then EXECUTE IMMEDIATE l_execute; end if;

  end loop;

  EXCEPTION

     WHEN OTHERS THEN

       err_msg := SUBSTR(SQLERRM(SQLCODE),1,100);

       dbms_output.put_line('Erreur : '|| err_msg);

  END;

  -- Collections

  DBMS_OUTPUT.put_line('Drop Collections');

  BEGIN

    for r_coll in c_coll( b_owner => upper(nom_schema)) loop

       l_execute:= 'drop type "'||r_coll.owner||'"."'||r_coll.TYPE_NAME || '" force';

  --     DBMS_OUTPUT.put_line(l_execute || '...');

       if exe = 'O' OR exe='o'  then EXECUTE IMMEDIATE l_execute; end if;

    end loop;

    EXCEPTION

       WHEN OTHERS THEN

         err_msg := SUBSTR(SQLERRM(SQLCODE),1,100);

         dbms_output.put_line('Erreur : '|| err_msg);

  END;

  -- Simple TYPEs

  DBMS_OUTPUT.put_line('Drop Simple TYPEs');

    BEGIN

      for r_type in c_type( b_owner => upper(nom_schema)) loop

         l_execute:= 'drop type "'||r_type.owner||'"."'||r_type.object_name || '" force';

      --  DBMS_OUTPUT.put_line(l_execute || '...');

         if exe = 'O' OR exe='o'  then EXECUTE IMMEDIATE l_execute; end if;

      end loop;

      EXCEPTION

         WHEN OTHERS THEN

           err_msg := SUBSTR(SQLERRM(SQLCODE),1,100);

           dbms_output.put_line('Erreur : '|| err_msg);

    END;

  -- Other objects with a Cascade Constraints on Table

  DBMS_OUTPUT.put_line('Drop Tables and other objects');

  BEGIN

  for r_obj in c_obj( b_owner => upper(nom_schema)) loop

     l_execute:= 'drop '||r_obj.object_type||' "'||r_obj.owner||'"."'||r_obj.object_name||'"';

     if r_obj.object_type = 'TABLE'

     then

       l_execute:= l_execute || ' CASCADE CONSTRAINTS';

     end if;

-- debug

--  DBMS_OUTPUT.put_line(l_execute || '...');

    if exe = 'O' OR exe='o'  then EXECUTE IMMEDIATE l_execute; end if;

  end loop;

  EXCEPTION

     WHEN OTHERS THEN

       err_msg := SUBSTR(SQLERRM(SQLCODE),1,100);

       dbms_output.put_line('Error on drop objects: ' || err_msg);

  END;

   -- triggers DATABASE and SCHEMA

  DBMS_OUTPUT.put_line('Drop Trigger triggers DATABASE and SCHEMA');

  BEGIN

  for r_trig in c_trig( b_owner => upper(nom_schema)) loop

     l_execute:= 'drop trigger "'||r_trig.owner||'"."'||r_trig.trigger_name||'"';

     DBMS_OUTPUT.put_line(l_execute || '...');

     if exe = 'O'  OR exe='o' then EXECUTE IMMEDIATE l_execute; end if;

  end loop;

  EXCEPTION

     WHEN OTHERS THEN

       err_msg := SUBSTR(SQLERRM(SQLCODE),1,100);

       dbms_output.put_line('Error on trigger DB: ' || err_msg);

  END;

  -- bin user purge

  DBMS_OUTPUT.put_line('bin user purge');

 for i in       ( select distinct owner , tablespace_name from dba_segments

                where owner = '&schema' and segment_type = 'TABLE' )

 loop

   begin

     if exe = 'O' OR exe='o'

         then execute immediate 'purge tablespace '||i.tablespace_name||' user '||i.owner||' ';

     end if;

   end;

 end loop;

  -- check show the remaining objects if they exist

  select count(object_name) into nb

    from dba_objects

    where owner= upper(nom_schema) and object_type != 'DATABASE LINK' ;

  if (nb=0) then

        DBMS_OUTPUT.put_line('Empty Schema !');

      else

        DBMS_OUTPUT.put_line('PROCEDURE unsuccessful  !!!!! There still :');

        for r_compte_obj in c_compte_obj( b_owner => upper(nom_schema)) loop

          DBMS_OUTPUT.put_line(r_compte_obj.nb_objets || ' ' || r_compte_obj.object_type || '(s)');

        end loop;

      end if;

 END; -- bloc du else

END IF;

end;

/

Exit

Execute

$ sqlplus system @/ORA_UTILS/oracle/dba/vide_schema.sql APPLI Y

4] Others / Tables

4.1 Shrink Table

Select Segment to be shrunk then go.

Identify total segment space allocation and real space used, and get estimated space gain.

select table_name , num_rows , blocks*8/1024 MB_Size from dba_tables where owner = 'APP' and num_rows is not null order by 2 desc

select segment_name , sum(bytes/1024/1024) from dba_segments where owner = 'APP' and segment_name in ('T_LOG') group by segment_name order by 2 desc ;

exec DBMS_STATS.GATHER_TABLE_STATS ( 'APP','T_LOG',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED COLUMNS SIZE AUTO',2,'ALL',TRUE) ;

select table_name, num_rows, round(tt/1024/1024,2) Used_Sz_MB, round(tr/1024/1024,2) Real_Sz_MB, lg Free_MB,ceil(trunc(100/tt*tr,2)-100) "% over_allocation",round((tr-decode(tr,40960,tr,tt))/1024/1024,2) "Gain_MB"

from (

SELECT   t.table_name, t.num_rows, t.num_rows * t.avg_row_len as tt, t.blocks * 8192 as tr,

         TRUNC (  (t.blocks * 8192 - t.num_rows * t.avg_row_len)/1024/1024,2 ) as lg

   FROM dba_tables t WHERE t.num_rows > 0 AND owner = 'APP' and table_name in ('T_LOG')

ORDER BY t.blocks * 8192 - t.num_rows * t.avg_row_len DESC

)

select count(1) from APP.T_LOG -- 188

alter table APP.T_LOG enable row movement ;

alter table APP.T_LOG shrink space cascade ;

-- The total time as needed to shrink a segment is proportional to the size of the segment to be shrinked.

alter table APP.T_LOG disable row movement ;

TABLE_NAME	NUM_ROWS	USED_SZ_MB	REAL_SZ_MB	FREE_MB	% over_allocation	Gain_MB
T_LOG	188	0,02	6 208,00	6 207,98	38049701	6 207,98

=>

TABLE_NAME	NUM_ROWS	USED_SZ_MB	REAL_SZ_MB	FREE_MB	% over_allocation	Gain_MB
T_LOG	188	0,02	0,02	0	38049701	0,01

4.2 Logging Mode

Put in Logging mode Tables ( exclude MV , selected account and tables )

select 'alter table '||a.owner||'.'||table_name||' logging ; ' from dba_tables a

where logging = 'NO' and TEMPORARY = 'N' and not exists ( select '1' from dba_mviews b where a.owner = b.owner and a.table_name = b.container_name ) 

and table_name not like 'C$%' and table_name not like 'TMP%'

and not ( a.owner = 'APP' and table_name like '%TMP%' ) and table_name like '%$' )

and a.owner not in ('APEX_030200','APEX_040000','FLOWS_030000','APPQOSSYS','CTXSYS','DBSNMP','EXFSYS','FLOWS_FILES','MDSYS','OLAPSYS',

'ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','SCOTT','SYS','SYSMAN','SYSTEM','WMSYS','XDB','PERFSTAT') and a.owner not like 'IBO%'

order by 1

select owner ,count(1) nb_obj_nologging from dba_tables a

where logging = 'NO' and TEMPORARY = 'N' and not exists ( select '1' from dba_mviews b where a.owner = b.owner and a.table_name = b.container_name ) 

and table_name not like 'C$%' and table_name not like 'TMP%'

and not ( a.owner = 'APP' and table_name like '%TMP%' ) and table_name like '%$' )

and a.owner not in ('APEX_030200','APEX_040000','FLOWS_030000','APPQOSSYS','CTXSYS','DBSNMP','EXFSYS','FLOWS_FILES','MDSYS','OLAPSYS',

'ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','SCOTT','SYS','SYSMAN','SYSTEM','WMSYS','XDB','PERFSTAT') and a.owner not like 'IBO%'

group by owner

order by 2 desc

Get Volume from LOB objects

-- calcul des volumes PJ

select sum(bytes/1024/1024) SZ_MB from dba_segments where segment_name = 'APP_PJ' and owner = 'APP' -- 18 MB

select sum(a.bytes/1024/1024) SZ_MB from dba_segments a , dba_lobs b where a.segment_name = b.segment_name and b.owner = 'APP' and b.table_name = 'APP_PJ' -- 36069 MB

5] External Table

Create an External Table to access data in a flat file named empxt.csv ; located in a Directory.

CONNECT / AS SYSDBA;

GRANT READ , WRITE ON DIRECTORY APP01_DP_DIR TO APP01 ;

CONNECT APP01/APP01

-- create the external table

drop table admin_ext_employees ;

CREATE TABLE admin_ext_employees

(employee_id NUMBER(4),

first_name VARCHAR2(20),

last_name VARCHAR2(25),

job_id VARCHAR2(10),

manager_id NUMBER(4),

hire_date DATE,

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

department_id NUMBER(4),

email VARCHAR2(25)

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY APP01_DP_DIR

ACCESS PARAMETERS

(

records delimited by newline

badfile APP01_DP_DIR:'empxt%a_%p.bad'

logfile APP01_DP_DIR:'empxt%a_%p.log'

fields terminated by ';'

missing field values are null

( employee_id, first_name, last_name, job_id, manager_id,

hire_date char date_format date mask "dd-mon-yyyy",

salary, commission_pct, department_id, email

)

)

LOCATION ('empxt.csv')

)

PARALLEL

REJECT LIMIT UNLIMITED;

SELECT * FROM admin_ext_employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME      JOB_ID   MANAGER_ID HIRE_DATE      SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL

360 Jane           Janus    ST_CLERK          121 17/05/2001       3000              0            50 jjanus

361 Mark           Jasper   SA_REP            145 17/05/2001       8000             .1            80 mjasper

362 Brenda         Starr    AD_ASST           200 17/05/2001       5500              0            10 bstarr

363 Alex           Alda     AC_MGR            145 17/05/2001       9000            .15            80 aalda