Finding object size in Oracle database

Ref:

http://www.runningoracle.com/product_info.php?cPath=2_44&products_id=153


Finding object size in Oracle database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in Mbytes. The scripts have been formatted to work very easily with TOAD SQL Editor. For example you can filter with tablespace_name, or owner, or size (for example more than 1GByte) 


SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name 
FROM DBA_SEGMENTS 
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION', 
'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY') 
--AND TABLESPACE_NAME LIKE 'COSTE%' 
--AND SEGMENT_NAME LIKE 'P2010201%' 
--AND partition_name LIKE 'P20100201%'
--AND segment_type = 'TABLE'
--AND OWNER = 'TARGET_POC' 
--AND ROUND(bytes/(1024*1024),2) > 1000 
ORDER BY bytes DESC;

You can group by tablespace, owner and segment type and see the total space occupied in MBytes
SELECT tablespace_name, owner, segment_type "Object Type",
       COUNT(owner) "Number of Objects",
       ROUND(SUM(bytes) / 1024 / 1024, 2) "Total Size in MB"
FROM   sys.dba_segments
WHERE  tablespace_name IN ('MPIS')
GROUP BY tablespace_name, owner, segment_type
ORDER BY tablespace_name, owner, segment_type;


Database size history
                                                
Summary 

Faster query!(all the files, data and temp)
SELECT SUM(MB_ALLOC)/1024 GB_DB_SIZE FROM 
(SELECT SUM(ROUND(bytes_used/(1024*1024),2) + ROUND(bytes_free/(1024*1024),2)) MB_ALLOC 
FROM V$temp_space_header, dba_temp_files 
WHERE V$temp_space_header.file_id (+) = dba_temp_files.file_id
UNION
SELECT SUM(BYTES)/(1024*1024) MB_ALLOC FROM dba_data_files);

Slower query
SELECT ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, ROUND(SUM(USED.BYTES) / 1024 / 1024 / 1024 ) - 
ROUND(FREE.P / 1024 / 1024 / 1024) || ' GB' "Used space"
, ROUND(FREE.P / 1024 / 1024 / 1024) || ' GB' "Free space"
FROM    (SELECT BYTES
FROM V$DATAFILE
UNION ALL
SELECT BYTES
FROM V$TEMPFILE
UNION ALL
SELECT BYTES
FROM V$LOG) USED
, (SELECT SUM(BYTES) AS P
FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;

To keep a history of how database is growing you can create a table that records for example every week the database size. The following procedure does not take into account the UNDO tablespace and TEMPORARY tablespace, only real data and indexes. 

Create the table for database size history
create table db_space_hist (
timestamp    date,
total_space  number(8),
used_space   number(8),
free_space   number(8),
        pct_inuse    number(5,2),
        num_db_files number(5)
);
Create the procedure db_space_history
CREATE OR REPLACE PROCEDURE db_space_history AS
BEGIN
   INSERT INTO db_space_hist 
SELECT SYSDATE, total_space,
        total_space-NVL(free_space,0) used_space,
        NVL(free_space,0) free_space,
        ((total_space - NVL(free_space,0)) / total_space)*100 pct_inuse,
        num_db_files
 FROM ( SELECT SUM(bytes)/1024/1024 free_space
        FROM   sys.DBA_FREE_SPACE WHERE tablespace_name NOT LIKE '%UNDO%') FREE,
      ( SELECT SUM(bytes)/1024/1024 total_space,
               COUNT(*) num_db_files
        FROM   sys.DBA_DATA_FILES WHERE tablespace_name NOT LIKE '%UNDO%') FULL;
   COMMIT;
END;
/
Create the job that runs once in a week
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'SYS.DB_SPACE_HISTORY;'
     ,next_date  => TO_DATE('22/02/2008 19:40:28','dd/mm/yyyy hh24:mi:ss')
     ,INTERVAL   => 'TRUNC(SYSDATE+7)'
     ,no_parse   => FALSE
    );
END;
Monitor how things going on periodically:
select * from db_space_hist order by timestamp desc;
Alternative:How the database size increased in GBytes per month for the last year.
SELECT TO_CHAR(creation_time, 'RRRR Month') "Month", 
round(SUM(bytes)/1024/1024/1024) "Growth in GBytes" 
FROM sys.v_$datafile 
WHERE creation_time > SYSDATE-365 
GROUP BY TO_CHAR(creation_time, 'RRRR Month');

Month          Growth in GBytes
-------------- ----------------
2008 December              1331
2008 November               779
2008 October                447
2009 April                  797
2009 August                 344
2009 February               505
2009 January                443
2009 July                   358
2009 June                   650
2009 March                  452
2009 May                   1787
2009 October                255
2009 September              158

As you can see from the last query the database increased its size for the month: 2009 October 255 GBytes

Script to extract total objects & size of all Schema’s:

select obj.owner "Owner", obj_cnt "Objects",
decode(seg_size, NULL, 0, seg_size) "size MB"
from ( select owner, count(*) obj_cnt from dba_objects group by owner) obj,
( select owner, ceil(sum(bytes)/1024/1024) seg_size from dba_segments group by owner) segment
where obj.owner = segment.owner(+)
order by 3 desc, 2 desc, 1;



SQL> set pages 999
SQL> col "size MB" format 999,999,999
SQL> col "Objects" format 999,999,999
SQL> select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1;

Database uptime

column hostname format a26
column “Instance Name” format a16
column “Started At” format a26
column “Database Uptime” format a52
SELECT
host_name as Hostname,
instance_name as “Instance Name”,
to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) as “Started_At”,
floor(sysdate – startup_time) || ‘ days(s) ‘ ||
trunc( 24*((sysdate-startup_time) -
trunc(sysdate-startup_time))) || ‘ hour(s) ‘ ||
mod(trunc(1440*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||’ minute(s) ‘ ||
mod(trunc(86400*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||’ seconds’ as “Database_Uptime”
FROM

sys.v_$instance;

Good Blog - like it

http://www.runningoracle.com/

Datagurd standby troubleshooting

Ref:
http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/
http://shivanandarao-oracle.com/2012/03/26/roll-forward-physical-standby-database-using-rman-incremental-backup/

Run Below scripts from SYS user from Both Primary & Standby databases.

Primary Script:-

spool dg_Primary_output.log
set feedback off
set trimspool on
set line 500
set pagesize 50
column name for a30
column display_value for a30
column ID format 99
column "SRLs" format 99
column active format 99
col type format a4
column ID format 99
column "SRLs" format 99
column active format 99
col type format a4
col PROTECTION_MODE for a20
col RECOVERY_MODE for a20
col db_mode for a15
SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name;
col name for a10
col DATABASE_ROLE for a10
SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE,switchover_status from v$database;
select thread#,max(sequence#) from v$archived_log group by thread#;
col severity for a15
col message for a70
col timestamp for a20
select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2;
select ds.dest_id id
, ad.status
, ds.database_mode db_mode
, ad.archiver type
, ds.recovery_mode
, ds.protection_mode
, ds.standby_logfile_count "SRLs"
, ds.standby_logfile_active active
, ds.archived_seq#
from v$archive_dest_status ds
, v$archive_dest ad
where ds.dest_id = ad.dest_id
and ad.status != 'INACTIVE'
order by
ds.dest_id;
column FILE_TYPE format a20
col name format a60
select    name
,    floor(space_limit / 1024 / 1024) "Size MB"
,    ceil(space_used  / 1024 / 1024) "Used MB"
from    v$recovery_file_dest
order by name;
spool off

This script will generate dg_Primary_output.log file where user is connected to SQLPlus from shell.

Standby Script:-

spool dg_standby_output.log
set feedback off
set trimspool on
set line 500
set pagesize 50
set linesize 200
column name for a30
column display_value for a30
col value for a10
col PROTECTION_MODE for a15
col DATABASE_Role for a15
SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name;
col name for a10
col DATABASE_ROLE for a10
SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE from v$database;
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
select process, status,thread#,sequence# from v$managed_standby;
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
col name for a30 
select * from v$dataguard_stats; 
select * from v$archive_gap; 
col name format a60 
select name,floor(space_limit / 1024 / 1024) "Size MB" ,ceil(space_used  / 1024 / 1024) "Used MB" from    v$recovery_file_dest order by name; 
spool off

column applied_time for a30
set linesize 140
select to_char(sysdate,'mm-dd-yyyy hh24:mi:ss') "Current Time" from dual;
SELECT DB_NAME,  APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP ,
(case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or 
            (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or 
            ((LOG_ARCHIVED-LOG_APPLIED) > 5)) 
      then 'Error! Log Gap is ' 
      else 'OK!' 
 end) Status
FROM
(
SELECT INSTANCE_NAME DB_NAME
FROM GV$INSTANCE
where INST_ID = 1
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=1
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1
)
UNION
SELECT DB_NAME,  APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP,
(case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or 
            (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or 
            ((LOG_ARCHIVED-LOG_APPLIED) > 5)) 
      then 'Error! Log Gap is ' 
      else 'OK!' 
 end) Status
from (
SELECT INSTANCE_NAME DB_NAME
FROM GV$INSTANCE
where INST_ID = 2
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=2
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2
)
/