RMAN restore database from SNAPSHOT CONTROLFILE

CONFIGURE SNAPSHOT CONTROLFILE NAME

================================================
in my case this is the location ...
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/app/oracle/product/11.2/dbs/snapcf_orcl.f'; # default

====================================================

RMAN> connect target /

connected to target database (not started)

RMAN> startup force nomount pfile='/tmp/spfileorcl.ora';

Oracle instance started

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes
Variable Size 608176120 bytes
Database Buffers 213909504 bytes
Redo Buffers 14979072 bytes

RMAN> restore controlfile from '/opt/app/oracle/product/11.2/dbs/snapcf_orcl.f' ;

Starting restore at 24-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/opt/app/oracle/oradata/orcl/control01.ctl
output file name=/opt/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 24-JUL-11




RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1



RMAN> exit


Recovery Manager complete.
oracle@apt-amd-02:/opt/app/oracle/oradata/orcl> !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 24 05:27:12 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select group#, first_change#, status, archived from v$log;

GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
4 12374116 CURRENT NO
6 0 UNUSED YES
5 0 UNUSED YES

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@apt-amd-02:/opt/app/oracle/oradata/orcl> rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jul 24 05:28:15 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /

connected to target database: ORCL (DBID=1267852645, not open)

RMAN> restore database;

Starting restore at 24-JUL-11
Starting implicit crosscheck backup at 24-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 24-JUL-11

Starting implicit crosscheck copy at 24-JUL-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 24-JUL-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /opt/app/oracle/flash_recovery_area/ORCL/autobackup/2011_07_23/o1_mf_s_757265596_72otfxny_.bkp
File Name: /opt/app/oracle/flash_recovery_area/ORCL/autobackup/2011_07_23/o1_mf_n_757269308_72oy1z1v_.bkp
File Name: /opt/app/oracle/flash_recovery_area/ORCL/autobackup/2011_07_23/o1_mf_n_757280782_72p98jvx_.bkp
File Name: /opt/app/oracle/flash_recovery_area/ORCL/autobackup/2011_07_24/o1_mf_n_757315149_72qbthrt_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/orcl/anujtest.dbf
channel ORA_DISK_1: restoring datafile 00007 to /opt/app/oracle/oradata/orcl/tsapexf01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /opt/app/oracle/oradata/orcl/tsapexu01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /opt/app/oracle/oradata/orcl/test.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/app/oracle/oradata/orcl/rman.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/databasefiles_ORCL_0lmi5sv0_21_20110723
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/databasefiles_ORCL_0lmi5sv0_21_20110723 tag=TAG20110723T152936
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:55
Finished restore at 24-JUL-11

RMAN> recover database;

Starting recover at 24-JUL-11
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/24/2011 05:32:02
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 12374116

RMAN> alter database open resetlogs;

database opened

RMAN> exit


Recovery Manager complete.
oracle@apt-amd-02:/opt/app/oracle/oradata/orcl> !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 24 05:35:09 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select group#, first_change#, status, archived from v$log;

GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
4 12374117 CURRENT NO
5 0 UNUSED YES
6 0 UNUSED YES

SQL>

RMAN restore spfile

RMAN> connect target /

connected to target database (not started)

RMAN> >STARTUP FORCE NOMOUNT


RMAN> STARTUP FORCE NOMOUNT ;

Oracle instance started

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes
Variable Size 629147640 bytes
Database Buffers 205520896 bytes
Redo Buffers 2396160 bytes


RMAN> restore spfile to pfile '/tmp/spfileorcl.ora' from '/home/oracle/RmanBackup/spfile_ORCL_20_20110723.bak' ;

Starting restore at 23-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/RmanBackup/spfile_ORCL_20_20110723.bak
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 23-JUL-11

RMAN restore database

RMAN full restore/recover
RMAN recovery
RMAN Restore Database


RMAN> connect target /

connected to target database (not started)



start up the database with pfile

RMAN> startup force nomount pfile='/opt/app/oracle/product/11.2/dbs/initorcl.ora';

Oracle instance started

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes
Variable Size 629147640 bytes
Database Buffers 205520896 bytes
Redo Buffers 2396160 bytes
=================

Backup file ....


-rw-r----- 1 oracle oinstall 98304 2011-07-23 15:29 spfile_ORCL_20_20110723.bak
-rw-r----- 1 oracle oinstall 469909504 2011-07-23 15:33 databasefiles_ORCL_0lmi5sv0_21_20110723
-rw-r----- 1 oracle oinstall 1130496 2011-07-23 15:33 controlfile_ORCL_0mmi5t5q_22_20110723



restore controlfile from '/home/oracle/RmanBackup/controlfile_ORCL_0mmi5t5q_22_20110723';

----


RMAN> restore controlfile from '/home/oracle/RmanBackup/controlfile_ORCL_0mmi5t5q_22_20110723';

Starting restore at 23-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/app/oracle/oradata/orcl/control01.ctl
output file name=/opt/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 23-JUL-11



RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1


RMAN> list backup;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13 Full 80.00K DISK 00:00:00 23-JUL-11
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: SPFILE
Piece Name: /home/oracle/RmanBackup/spfile_ORCL_20_20110723.bak
SPFILE Included: Modification time: 23-JUL-11
SPFILE db_unique_name: ORCL

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14 Full 448.13M DISK 00:03:30 23-JUL-11
BP Key: 16 Status: AVAILABLE Compressed: YES Tag: TAG20110723T152936
Piece Name: /home/oracle/RmanBackup/databasefiles_ORCL_0lmi5sv0_21_20110723
List of Datafiles in backup set 14
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/system01.dbf
2 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/sysaux01.dbf
3 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/undotbs01.dbf
4 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/users01.dbf
5 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/example01.dbf
6 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/anujtest.dbf
7 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/tsapexf01.dbf
8 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/tsapexu01.dbf
9 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/test.dbf
10 Full 12374114 23-JUL-11 /opt/app/oracle/oradata/orcl/rman.dbf

RMAN> restore database;

Starting restore at 23-JUL-11
Starting implicit crosscheck backup at 23-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 23-JUL-11

Starting implicit crosscheck copy at 23-JUL-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 23-JUL-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /opt/app/oracle/flash_recovery_area/ORCL/autobackup/2011_07_23/o1_mf_s_757265596_72otfxny_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/orcl/anujtest.dbf
channel ORA_DISK_1: restoring datafile 00007 to /opt/app/oracle/oradata/orcl/tsapexf01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /opt/app/oracle/oradata/orcl/tsapexu01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /opt/app/oracle/oradata/orcl/test.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/app/oracle/oradata/orcl/rman.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/databasefiles_ORCL_0lmi5sv0_21_20110723
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/databasefiles_ORCL_0lmi5sv0_21_20110723 tag=TAG20110723T152936
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:55
Finished restore at 23-JUL-11



RMAN> recover database;

Starting recover at 23-JUL-11
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=509
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/23/2011 16:28:17
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 509 and starting SCN of 12374114


so we have to restore the database upto 12374114 -1 = 12374113




RMAN>

run {
set until scn 12374113;
recover database;
}



RMAN> run {
set until scn 12374113;
recover database;
}
2> 3> 4>
executing command: SET until clause

Starting recover at 23-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/23/2011 16:30:09
RMAN-06556: datafile 1 must be restored from backup older than SCN 12374113


then try again

12374113 -1 =12374112



run {
set until scn 12374112;
recover database;
}


RMAN> run {
set until scn 12374112;
recover database;
}2> 3> 4>

executing command: SET until clause

Starting recover at 23-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/23/2011 16:31:20
RMAN-06556: datafile 1 must be restored from backup older than SCN 12374112



no luck ... then I did resetlogs


RMAN> alter database open resetlogs;

database opened


RMAN> exit





sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 23 16:35:57 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN



SQL> select group#, first_change#, status, archived from v$log;

GROUP# FIRST_CHANGE# STATUS ARC
---------- ------------- ---------------- ---
4 12374115 CURRENT NO
5 0 UNUSED YES
6 0 UNUSED YES


=============================================================

umount2: Device or resource busy

mount point busy
device is busy

not able to unmount unix

apt-amd-02:~ # umount -f /opt
umount2: Device or resource busy
umount: /opt: device is busy.
(In some cases useful info about processes that use
the device is found by lsof(8) or fuser(1))
umount2: Device or resource busy

find the process through lsof

apt-amd-02:~ # lsof /opt <<<<<<<<<<<<-------
lsof: WARNING: can't stat() fuse.gvfs-fuse-daemon file system /home/anujs/.gvfs
Output information may be incomplete.
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
kdm 1828 root txt REG 253,1 156904 83982271 /opt/kde3/bin/kdm
kdm 2022 root txt REG 253,1 156904 83982271 /opt/kde3/bin/kdm
acroread 5322 root mem REG 253,1 11664616 117462946 /opt/kde3/share/icons/hicolor/icon-theme.cache
bash 6061 root cwd DIR 253,1 4096 94473498 /opt/app/oracle/product/10.2/db10g/agent10g
bash 24434 root cwd DIR 253,1 4096 33666700 /opt/app/oracle/oraInventory/logs
firefox 31691 anujs mem REG 253,1 11664616 117462946 /opt/kde3/share/icons/hicolor/icon-theme.cache


kill all the process


apt-amd-02:~ # kill -9 1828 2022 5322 6061 24434 31691

apt-amd-02:~ # umount -f /opt
apt-amd-02:~ # mount /opt
apt-amd-02:~ # df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 10G 8.7G 1.4G 87% /
devtmpfs 1003M 452K 1003M 1% /dev
tmpfs 1005M 16K 1005M 1% /dev/shm
/dev/sda1 69M 59M 5.9M 91% /boot
/dev/mapper/system-home
42G 24G 17G 59% /home
/dev/mapper/system-opt
20G 14G 7.0G 66% /opt

Oracle delete ADDM advisory

delete ADDM report advice
ADDM report delete


spool delete.sql
select 'exec dbms_advisor.delete_task(''' || TASK_NAME || ''' )' ||';' from dba_advisor_tasks;

spool off

will delete all the advisory

sql>@delete


apt-rdbms-01.:APTDB\sys> l
1 select 'exec dbms_advisor.delete_task(''' || TASK_NAME || ''' )' ||';' from dba_advisor_tasks
2* rows selected.

Oracle script maintain with comment

sql script comment
sql script maintenance
good example from http://blog.tanelpoder.com/?s=snapper to maintain the . sql script


put a line like this in the sql script

-- Purpose: Shows table, column, index, stats for a table

and you will get result like this

oracle@apt-lnxtst-01:~> grep -i Purpose: *.sql | awk -F: '{ printf("%20s %-50s\n", $1, $3) }'
roll1.sql to get the average writez of a rollback segment extent
table_stat2.sql Shows table, column, index, stats for a table

Oracle Table statistics report

Oracle Table statistics report

Table stat report



from web ...




set echo off feed off
set serveroutput on size 1000000

accept ownname prompt 'Owner : '
accept tabname prompt 'Table : '

set lines 80

define v_desc = '&ownname..&tabname.'
desc &v_desc
set lines 200

declare
v_query varchar2(4000) ;
v_owner varchar2(30) := upper('&&ownname');
v_table varchar2(30) := upper('&&tabname');
v_max_colname number ;
v_max_ndv number ;
v_max_nulls number ;
v_max_bkts number ;
v_max_smpl number ;
v_max_endnum number ;
v_max_endval number ;
v_ct number ;
prev_col varchar2(30) ;


cursor col_stats is
select a.column_name, nvl(a.last_analyzed,to_date('01/01/1900','mm/dd/yyyy')) last_analyzed,
decode(a.nullable,'N','NOT NULL',' ') nullable,
a.num_distinct, a.density, a.num_nulls,
a.num_buckets, a.avg_col_len, a.sample_size
from all_tab_columns a
where a.owner = v_owner
and a.table_name = v_table ;

cursor hist_stats is
select b.column_name, b.endpoint_number, b.endpoint_value, b.endpoint_actual_value
from all_tab_histograms b
where b.owner = v_owner
and b.table_name = v_table
and (exists (select 1 from all_tab_columns
where num_buckets > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
or
exists (select 1 from all_tab_histograms
where endpoint_number > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
)
order by b.column_name, b.endpoint_number;

procedure print_table ( p_query in varchar2, p_date_fmt in varchar2 default 'dd-MON-yyyy hh24:mi:ss' ) is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_cs varchar2(255);
l_date_fmt varchar2(255);

-- Small inline procedure to restore the session's state.
-- We may have modified the cursor sharing and nls date format
-- session variables. This just restores them.
procedure restore
is
begin
if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
then
execute immediate
'alter session set cursor_sharing=exact';
end if;
if ( p_date_fmt is not null )
then
execute immediate
'alter session set nls_date_format=''' || l_date_fmt || '''';
end if;
dbms_sql.close_cursor(l_theCursor);
end restore;
begin
-- I like to see the dates print out with times, by default. The
-- format mask I use includes that. In order to be "friendly"
-- we save the current session's date format and then use
-- the one with the date and time. Passing in NULL will cause
-- this routine just to use the current date format.
if ( p_date_fmt is not null )
then
select sys_context( 'userenv', 'nls_date_format' )
into l_date_fmt
from dual;
execute immediate
'alter session set nls_date_format=''' || p_date_fmt || '''';
end if;

-- To be bind variable friendly on ad-hoc queries, we
-- look to see if cursor sharing is already set to FORCE or
-- similar. If not, set it to force so when we parse literals
-- are replaced with binds.
if ( dbms_utility.get_parameter_value
( 'cursor_sharing', l_status, l_cs ) = 1 )
then
if ( upper(l_cs) not in ('FORCE','SIMILAR'))
then
execute immediate
'alter session set cursor_sharing=force';
end if;
end if;


-- Parse and describe the query sent to us. We need
-- to know the number of columns and their names.
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );

-- Define all columns to be cast to varchar2s. We
-- are just printing them out.
for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;

-- Execute the query, so we can fetch.
l_status := dbms_sql.execute(l_theCursor);

-- Loop and print out each column on a separate line.
-- Bear in mind that dbms_output prints only 255 characters/line
-- so we'll see only the first 200 characters by my design...
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
substr( l_columnValue, 1, 200 ) );
end loop;
dbms_output.put_line( '-----------------' );
end loop;

-- Now, restore the session state, no matter what.
restore;
exception
when others then
restore;
raise;
end;


begin
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Table Statistics');
dbms_output.put_line('==========================================================================================');


v_query := 'select table_name, last_analyzed, trim(degree) degree, partitioned,
num_rows, chain_cnt, blocks, empty_blocks, avg_space,
avg_row_len, monitoring, sample_size
from all_tables
where owner = ''' || UPPER(v_owner) || ''' and table_name = ''' || UPPER(v_table) || '''';
print_table (v_query);

v_ct := 0 ;

select count(1)
into v_ct
from all_tab_partitions
where table_owner = v_owner
and table_name = v_table;

if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Partition Information');
dbms_output.put_line('==========================================================================================');


v_query := 'select partition_name, last_analyzed, high_value,
num_rows, chain_cnt, blocks, empty_blocks,
avg_space, avg_row_len, compress_for
from all_tab_partitions
where table_owner = ''' || UPPER(v_owner) || '''
and table_name = ''' || UPPER(v_table) || '''';

print_table (v_query);
end if ;

select max(length(column_name)) + 1, max(length(num_distinct)) + 3,
max(length(num_nulls)) + 1, max(length(num_buckets)) + 1,
max(length(sample_size)) + 1
into v_max_colname, v_max_ndv, v_max_nulls, v_max_bkts, v_max_smpl
from all_tab_columns
where owner = v_owner
and table_name = v_table ;

if v_max_nulls < 8 then
v_max_nulls := 8 ;
end if ;

if v_max_bkts < 10 then
v_max_bkts := 10 ;
end if ;

if v_max_smpl < 7 then
v_max_smpl := 7;
end if;

dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Column Statistics');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' ' || rpad('Name',v_max_colname) || ' Analyzed Null? ' ||
rpad(' NDV',v_max_ndv) || ' ' || rpad(' Density',10) ||
rpad('# Nulls',v_max_nulls) || ' ' || rpad('# Buckets',v_max_bkts) || ' ' ||
rpad('Sample',v_max_smpl) || ' Avg Col Len');
dbms_output.put_line('==========================================================================================');


for v_rec in col_stats loop
dbms_output.put_line(rpad(v_rec.column_name,v_max_colname) || ' ' ||
to_char(v_rec.last_analyzed,'mm/dd/yyyy') || ' ' ||
v_rec.nullable || ' ' ||
rpad(v_rec.num_distinct,v_max_ndv) ||
to_char(v_rec.density,'9.999999') || ' ' ||
rpad(v_rec.num_nulls,v_max_nulls) || ' ' ||
rpad(v_rec.num_buckets,v_max_bkts) || ' ' ||
rpad(v_rec.sample_size,v_max_smpl) || ' ' ||
v_rec.avg_col_len );

end loop ;

select max(length(column_name)) + 1, max(length(endpoint_number)) + 1,
max(length(endpoint_value)) + 1
into v_max_colname, v_max_endnum, v_max_endval
from all_tab_histograms
where owner = v_owner
and table_name = v_table ;

if v_max_endnum < 12 then
v_max_endnum := 12 ;
end if ;

if v_max_endval < 16 then
v_max_endval := 16 ;
end if ;

select count(1)
into v_ct
from all_tab_histograms b
where b.owner = v_owner
and b.table_name = v_table
and (exists (select 1 from all_tab_columns
where num_buckets > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
or
exists (select 1 from all_tab_histograms
where endpoint_number > 1
and owner = b.owner
and table_name = b.table_name
and column_name = b.column_name)
);

/* Histogram data commented out

if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Histogram Statistics');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' ' || rpad('Name',v_max_colname) || ' ' ||
rpad('Endpoint #',v_max_endnum) || ' ' ||
rpad('Endpoint Value',v_max_endval) || ' Endpoint Actual Value');

v_ct := 0 ;
for v_rec in hist_stats loop
if v_ct = 0 then
v_ct := 1 ;
prev_col := v_rec.column_name ;
elsif prev_col <> v_rec.column_name then
dbms_output.put_line('------------------------------------------------------------------------------------------');
prev_col := v_rec.column_name ;
end if ;
dbms_output.put_line(rpad(v_rec.column_name, v_max_colname) || ' ' ||
rpad(v_rec.endpoint_number,v_max_endnum) || ' ' ||
rpad(v_rec.endpoint_value,v_max_endval) || ' ' ||
substr(v_rec.endpoint_actual_value,1,20) ) ;
end loop ;
end if ;
*/

v_ct := 0;

select count(1)
into v_ct
from all_indexes a
where a.table_owner = v_owner
and a.table_name = v_table;

if v_ct > 0 then
dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Index Information');
dbms_output.put_line('==========================================================================================');

v_query := 'select a.index_name, substr(a.index_type, 1, 4) index_type,
a.last_analyzed, a.degree, a.partitioned, a.blevel,
a.leaf_blocks, a.distinct_keys,
a.avg_leaf_blocks_per_key, a.avg_data_blocks_per_key,
a.clustering_factor, b.blocks blocks_in_table, b.num_rows rows_in_table
from all_indexes a, all_tables b
where (a.table_name = b.table_name and a.table_owner = b.owner)
and a.table_owner = ''' || UPPER(v_owner) || '''
and a.table_name = ''' || UPPER(v_table) || '''' ;

print_table (v_query);

dbms_output.put_line('==========================================================================================');
dbms_output.put_line(' Index Columns Information');
dbms_output.put_line('==========================================================================================');
dbms_output.put_line('Index Name Pos# Order Column Name Expression');
dbms_output.put_line('==========================================================================================');
end if;

end ;
/

set verify off feed off numwidth 15 lines 500 heading off
column column_name format a30 heading 'Column Name'
column index_name heading 'Index Name' format a30
column column_position format 999999999 heading 'Position'
column descend format a5 heading 'Order'
column column_expression format a40 heading 'Expression'

break on index_name skip 1

select b.index_name, b.column_position, b.descend, b.column_name, e.column_expression
from all_ind_columns b, all_ind_expressions e
where b.table_owner = upper('&ownname')
and b.table_name = UPPER('&tabname')
and b.index_name = e.index_name(+)
order by b.index_name, b.column_position, b.column_name
/

undefine ownname
undefine tabname

set feed on numwidth 12 lines 120

clear columns
clear breaks
set lines 155
set head on