Statistics Collection ( Gather Statistics )

Gathering Statistics

Auditing Information are Stored in SYSTEM Tablespace . While Statistics information are stored in SYSAUX Tablespace.

 ü  For this GATHER_STATS_JOB is enabled.

 ü  For GATHER_STATS_JOB to work properly, you must be sure that  the STATISTICS_LEVEL parameter is set to  at least TYPICAL.

Gathering Statistics for a Table

sql> select num_rows from dba_tables  where owner=’APPS' and table_name='PER_ALL_PEOPLE_F';

sql> exec dbms_stats.gather_Table_stats('HR','PER_ALL_PEOPLE_F');

sql> select num_rows from dba_tables  where owner=’APPS' and table_name='PER_ALL_PEOPLE_F;

Gathering Statistics for a Database

begin

dbms_stats.gather_database_stats(

options=> 'GATHER AUTO');

end;

Gathering Statistics for a Schema

begin

dbms_stats.gather_schema_stats(

ownname=> '"APPS"' ,

options=> 'GATHER AUTO');

dbms_stats.lock_schema_stats( ownname=> '"APPS"' );

end;

/

Several statistics option

lock optimizer statistics to gurantee that the statistics for certain objects are never overwritten.

1) exec dbms_stats.lock_schema_stats('HR');      - to lock schema statistics

2) exec dbms_stats.unlock_schema_stats('HR');  - to unlock schema statistics

3) exec dbms_stats.delete_schema_stats('HR')    - to delete schema statistics

4) exec dbms_stats.restore_table_stats('SCOTT','DEPT'..)        - to restore table statistics to a particular time

Tablespace and Datafile Management

Table space and Data file Management

Create SmallFile Tablespace

Create smallfile tablespace safal datafile ‘+data(datafile)’ size 10m autoextend on next 10m maxsize 50m logging extent management local segment space management auto;

Add Datafile to a Tablespace

Alter Tablespace safal add Datafile ‘+data(Datafile)’ size 10m autoextend on next 10m maxsize 50m;

Drop Datafile

Alter tablespace safal drop Datafile <Datafile_number>;

Drop Tablespace

Drop tablespace safal including contents and datafiles;

Change Default Permanent tablespace of a database

Select * from database_properties;

Alter database default tablespace safal;

Create undo tablespace with no retention noguarantee

Create smallfile undo tablespace safal Datafile ‘+data(Datafile)’ size 10m autoextend on next 10m maxsize 50m extent management local retention noguarantee;

Change undo tablespace to a retention guarantee

Alter tablespace safal retention guarantee;

Change default undo tablespace

Alter system set undo_tablespace=SAFAL;

Create Temporary Tablespace

Create smallfile temporary tablespace demo tempfile ‘+data(tempfile)’ size 10m autoextend on next 10m maxsize 50m extent management local uniform size 10k segment space management manual;

Change Default Temporary Tablespace

Alter database default temporary tablespace demo;