Archive Generation is more in oracle

chceck tbs in begin backup mode:
================================

select a.tablespace_name
from sys.dba_data_files a, sys.v_$backup b
where b.status = 'ACTIVE'
and b.file# = a.file_id
group by a.tablespace_name;


Query to find the session that is generating more Archives
This Query is to find the session that is generating more Archives.
====================================================================

col program for a10
col username for a10
select to_char(sysdate,'hh24:mi'), username, program , a.sid, a.serial#, b.name, c.value
from v$session a, v$statname b, v$sesstat c
where b.STATISTIC# =c.STATISTIC#
and c.sid=a.sid and b.name like 'redo%'
order by value;





Check which session is generating more redo.
============================================
set pages 1000
set lines 140
SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;


SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;


SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec FROM v$session s, v$transaction t
WHERE s.taddr = t.addr ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;


The o/p of the query gives the sid , Number of undo blocks used and Number of undo records used that were happening during that time.
From the sid we can get the request id and the request name.



select sql_text from v$sqltext where hash_value=( select prev_hash_value from v$session where sid='&sid');

select sid||' - '||serial#||' - '||osuser||' - '||username||' - '||machine||' - '||status||' - '||logon_time
from v$session where sid=&123;

Tablespace used and free space

set linesize 400

set pagesize 20

set heading on

select c.tablespace_name as "TABLESPACE",

ROUND((b.total_space_available/1048576),2) AS "SIZE MB",

ROUND((b.total_space_available/1048576)-(a.free_space/1048576),2) as "USED MB",

ROUND((a.free_space/1048576),2) as "FREE MB",

ROUND(100 - (((a.free_space/1048576)/(b.total_space_available/1048576))*100),1)||' %'

as "% USED",ROUND((((a.free_space/1048576)/(b.total_space_available/1048576))*100),1)||' %' as "% FREE"

from (select sum(bytes) as free_space,tablespace_name

from dba_free_space -- Free space in permanent tablespace

group by tablespace_name UNION ALL

select sum(bytes_free) as free_space,tablespace_name

from v$temp_space_header -- Free space in temporary tablespaces

group by tablespace_name) a, -- Calculate the free space that each tablespace has

(select sum(user_bytes) as total_space_available,tablespace_name

from dba_data_files -- Total space in permanent tablespaces

group by tablespace_name

UNION ALL select sum(user_bytes) as total_space_available,tablespace_name

from dba_temp_files -- Total space in temporary tablespaces

group by tablespace_name) b, -- Calculate the total size of each TS

dba_tablespaces c

where a.tablespace_name (+) = b.tablespace_name and -- show TS that are full so have no entries in dba_free_space

b.tablespace_name = c.tablespace_name 

order by ROUND(100 - (((a.free_space/1048576)/(b.total_space_available/1048576))*100),1) DESC;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Check the datafile size using the tablespace name.

set linesize 150

column file_name format a50

column tablespace_name format a10

select file_name,bytes/1024/1024 ,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&tablespace_name';

For Temp size

set linesize 150

column file_name format a50

column tablespace_name format a10

select file_name,bytes/1024/1024 ,AUTOEXTENSIBLE from dba_temp_files where tablespace_name='&tablespace_name';

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

ASM Tablespace Utilization Script

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

SET LINESIZE 300

SET PAGESIZE 9999

SET VERIFY off

COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'

COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'

COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'

COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'

COLUMN state                  FORMAT a11           HEAD 'State'

COLUMN type                   FORMAT a6            HEAD 'Type'

COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (GB)'

COLUMN free_mb                FORMAT 999,999,999   HEAD 'Free Size (GB)'

COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (GB)'

COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

SELECT

    distinct name                            group_name

  , sector_size                              sector_size

  , block_size                               block_size

  , allocation_unit_size                     allocation_unit_size

  , state                                    state

  , type                                     type

  , round(total_mb/1024)                     "total_gb"

  , round(free_mb/1024)                      "free_gb"

  , round((total_mb - free_mb) / 1024)       "used_gb"

  , round((1- (free_mb / total_mb))*100, 2)  "pct_used"

from v$asm_diskgroup ORDER BY name

/

ASM Disk Space Usage Script (In Detail)

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

SET LINESIZE 150 

SET PAGESIZE 9999 

SET VERIFY off 

COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name' 

COLUMN disk_file_path FORMAT a17 HEAD 'Path' 

COLUMN disk_file_name FORMAT a20 HEAD 'File Name' 

COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group' 

COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)' 

COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)' 

COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used' 

break on report on disk_group_name skip 1 

compute sum label 'Disk Group' of total_mb used_mb on disk_group_name 

compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report 

SELECT 

 NVL(a.name, '[CANDIDATE]') disk_group_name 

 , b.path disk_file_path 

 , b.name disk_file_name 

 , b.failgroup disk_file_fail_group 

 , b.total_mb total_mb 

 , (b.total_mb - b.free_mb) used_mb 

 , ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used 

FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER' 

ORDER BY a.name 

/

Datafiles of a particular TableSpace

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

set pages 9999 lines 300

col tablespace_name for a30

col file_name for a80

select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB 

from dba_data_files where tablespace_name='&tablespace_name' order by 1,2;

ASM Disk Database Files Script

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

set pages 9999 lines 300

col full_alias_path for a70

col file_type for a15

select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path, 

        system_created, alias_directory, file_type

 from ( select b.name gname, a.parent_index pindex, a.name aname, 

               a.reference_index rindex , a.system_created, a.alias_directory,

               c.type file_type

        from v$asm_alias a, v$asm_diskgroup b, v$asm_file c

        where a.group_number = b.group_number

              and a.group_number = c.group_number(+)

              and a.file_number = c.file_number(+)

              and a.file_incarnation = c.incarnation(+)

      )

 start with (mod(pindex, power(2, 24))) = 0

             and rindex in 

                 ( select a.reference_index

                   from v$asm_alias a, v$asm_diskgroup b

                   where a.group_number = b.group_number

                         and (mod(a.parent_index, power(2, 24))) = 0

                         and a.name = '&DATABASE_NAME'

                 )

 connect by prior rindex = pindex;

TABLESPACE DDL

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

set pagesize 0

SET LONG 9999999

select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;

To resize a datafile (ASM)

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

alter database datafile '&datafile_name' resize 4096M;

If AUTOEXTEND ON

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

alter database datafile '&datafile_name' autoextend on maxsize 8G;

To add a new datafile in a tablespace (ASM)

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

BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile

select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';

ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;

If AUTOEXTEND ON

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

ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE 8G;

To Create a new tablespace  (ASM)

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

CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;

If AUTOEXTEND ON

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

CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;

Schemas in a tablespace

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

set pages 9999 lines 300

col "size MB" format 999,999,999

col "Objects" format 999,999,999

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

/

All schema object details in a tablespace

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

set pages 9999 lines 300

col tablespace_name format a15

col segment_name format a40

col segment_type format a20

col PARTITION_NAME format a20

col mb format 999,999,999

select    owner

,       tablespace_name

,       segment_name

,       segment_type

,       PARTITION_NAME

,    ceil(sum(bytes) / 1024 / 1024) "Size in MB"

from    dba_segments

where    tablespace_name like '&tablespace_name'

group    by segment_name

order   by ceil(sum(bytes) / 1024 / 1024) desc

/

SELECT SUBSTR(data_files.tablespace_name,1,20) "Tablespace Name",

       tot_create_blk /1024/1024 created_size,

       ROUND(max_free_blk / 1024/1024,2) max_free,

       ROUND(tot_free_blk / 1024/1024,2) tot_free,

       ROUND((tot_free_blk/tot_create_blk*100),3) pct_free

FROM   ( SELECT tablespace_name,

                SUM(bytes) tot_create_blk

         FROM sys.dba_data_files

         GROUP BY tablespace_name ) data_files,

       ( SELECT tablespace_name,

                MAX(bytes) max_free_blk,

                SUM(bytes) tot_free_blk

         FROM sys.dba_free_space

         GROUP BY tablespace_name ) free_space

       WHERE data_files.tablespace_name = free_space.tablespace_name

       and data_files.tablespace_name = '&tablespace'

ORDER BY data_files.tablespace_name;

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

This blog is for my Reference at my work and helping others for Reference :)

Hello everyone

This blog is purely for my Reference

Thank you for visiting my blog  I hope you will find my blog is useful for you and will welcome if you send your valuable comment on visited article.

Maintaining this blog which helping at my work and helping others for reference.

scripts and articles publish here may or may not tested..its individual risk.