Tablespace scripts

To Check 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;



undo space utlization

select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
from dba_data_files a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.contents = 'UNDO'
group by b.tablespace_name) a,
(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
from DBA_UNDO_EXTENTS c
where status <> 'EXPIRED'
group by c.tablespace_name) b
where a.tablespace_name = b.tablespace_name;


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

FRa size  db
______

set lines 100
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
/


NAME                                                            Size MB    Used MB
------------------------------------------------------------ ---------- ----------
+FLASH01                                                         102400     102337

ALTER SYSTEM SET db_recovery_file_dest_size = 105g SCOPE = BOTH;


alter system set db_recovery_file_dest=

alter system set DB_RECOVERY_FILE_DEST='+FLASH01' SID='*';

ALTER SYSTEM SET db_recovery_file_dest_size = 53g; SCOPE = BOTH;

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

Select tablespace_name,file_id,file_name,ceil(bytes/1024/1024/1024) "Size in GB",autoextensible,status,ceil(maxbytes/1024/1024/1024) "MAx in GB" from dba_data_files where tablespace_name='SYSTEM';
                                                                       


to get schema size


set linesize 150
set pagesize 5000
col owner for a15
col segment_name for a30
col segment_type for a20
col TABLESPACE_NAME for a30
clear breaks
clear computes
compute sum of SIZE_IN_GB on report
break on report
select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner;

               

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

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';

alter tablespace obi_dat add datafile '/obiprd01/orafs025/oradata/obiprd01/obi_dat50.dbf' size 2048M;
alter tablespace obi_dat add datafile '/obiprd01/orafs025/oradata/obiprd01/obi_dat50.dbf' size 2048M;


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';


alter tablespace temp add tempfile 'F:\ORA10\DATABASE\QI3T\TEMP06.DBF' size 4096M;



Column tablespace_name heading 'Tablespace Name' format a10
COLUMN created_size HEADING 'Create (MB)'    FORMAT 9999,999.99;
COLUMN max_free     HEADING 'Max Free (MB)'  FORMAT 9999,999.99;
COLUMN tot_free     HEADING 'Tot Free (MB)'  FORMAT 9999,999.99;
COLUMN pct_free     HEADING 'Free %'         FORMAT 999.999;








December 08, 2012ASM TABLESPACE Scripts
http://files.meetup.com/1729503/1729503/All_about_ASM.pdf

When a tablespace is getting filled up, action need to be taken (Preference-wise)

1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.

Note:-
Check the availability of the free space on the disk at OS level.
df -h (Linux,AIX)
df -gt

oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon

Set the environment for the database
------------------------------------
uname

cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab

export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin

Database Details
----------------
sqlplus "/as sysdba"

set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME,INSTANCE_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

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 file_name,bytes/1024/1024 Size_MB,maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files where tablespace_name='&tablespace_name' order by 1,2;


select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB
from dba_temp_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;



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




1. Check the database details.
2. Check the tablespace Utilization.
3. Check the details of the datafiles for a particular TableSpace which needs attention.
4. Resize or Add the datafiles as per the standards of the existing datafiles on the database.

1. Check the database details.
$ sqlplus "/as sysdba"

set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE from v$database,v$instance;

2. Check the tablespace Utilization.
Tablespace Utilization Script including AUTOEXTEND (IN GB)
----------------------------------------------------------
$ sqlplus "/as sysdba"

set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
        ,a.alloc_size/1024/1024/1024 Allocated_size
        ,a.cur_size/1024/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from  dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) u
        ,(select d.tablespace_name
        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
        ,sum(d.bytes) cur_size
        ,count(*) file_count
        from dba_data_files d
        group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/

3. Check the details of the datafiles for a particular TableSpace which needs attention.
Datafiles of a particular TableSpace:
------------------------------------
set pages 50000 lines 32767
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;

Note:- If required, can get the DDL of a tablespace as below.

TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;














Datafile shrink






set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/




select df.tablespace_name TABLESPACE_NAME,
round(df.bytes/(1024*1024),2) TOTAL_MB,
round(sum(fs.bytes)/(1024*1024),2) FREE_MB,
round(sum(fs.bytes)/(df.bytes) * 100,1) PCT_FREE
  2    3    4    5  from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
  6  group by tablespace_name ) df
  7  where fs.tablespace_name = df.tablespace_name
  8  group by df.tablespace_name, df.bytes
  9  order by pct_free asc;







select df.tablespace_name TABLESPACE_NAME,
  2  round(df.bytes/(1024*1024),2) TOTAL_MB,
  3  round(sum(fs.bytes)/(1024*1024),2) FREE_MB,
  4  round(sum(fs.bytes)/(df.bytes) * 100,1) PCT_FREE
  5  from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
  6  group by tablespace_name ) df
  7  where fs.tablespace_name = df.tablespace_name
  8  group by df.tablespace_name, df.bytes
  9  order by pct_free asc;












Rman backup status:




select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork)* 100 done,
   sysdate + TIME_REMAINING/3600/24 end_at
   from v$session_longops
   where totalwork > sofar
   AND opname NOT LIKE '%aggregate%'
   AND opname like 'RMAN%';
/



Check restore status
=====================
@/usr/local/script/oracle/dba/show_longops.sql


alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';


set pages 1000 lines 140
column units for a10

select sid,
       serial#,
       sofar,
       totalwork,
       TRUNC(sofar/elapsed_seconds) "Blocks/sec",
       ROUND(time_remaining/60) "Mins to go",
       start_time,
       last_update_time,
       ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE"
  from v$session_longops
 where sofar != totalwork
   and OPNAME LIKE 'RMAN%'
   AND OPNAME NOT LIKE '%aggregate%'
   AND TOTALWORK != 0
/





oracle script to check the database growth


SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
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;



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



set linesize 132 tab off trimspool on
set pagesize 105
set pause off
set echo off
set feedb on

column tablespace_name format a25
column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format  9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00

select a.tablespace_name,
       a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
       a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
       nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
       (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from ( select tablespace_name,
       sum(bytes) physical_bytes,
       sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
       from dba_data_files
       group by tablespace_name ) a,
     ( select tablespace_name, sum(bytes) tot_used
       from dba_segments
       group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
--and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and   a.tablespace_name not like 'UNDO%'
order by 1
--order by 5
/











Reorganizing Steps:-

 So first we have to check how much data got fragmented in that particular Table.

SQL> set verify off
     column owner format a10
     column alcblks heading 'Allocated|Blocks' just c
     column usdblks heading 'Used|Blocks'      just c
     column hgwtr heading 'High|Water'         just c
     break on owner skip page
     select a.owner,a.table_name,b.blocks alcblks,a.blocks usdblks,(b.blocks-a.empty_blocks-1)
     hgwtr from dba_tables a,dba_segments b where a.table_name=b.segment_name
     and a.owner=b.owner and a.owner not in('SYS','SYSTEM') and a.blocks <> (b.blocks-a.empty_blocks-1)
     and a.owner like upper('&owner')||'%'and a.table_name like upper('&table_name')||'%'
     order by 1,2;




How to find Top 10 Fragmented Tables in Oracle

SQL> select * from (select table_name,round((blocks * 8), 2) "size (kb)",round((num_rows *                        avg_row_len / 1024), 2) "actual_data (kb)", (round((blocks * 8), 2) - round((num_rows * avg_row_len / 1024), 2)) "wasted_space (kb)"  from dba_tables where (round((blocks * 8), 2) > round((num_rows * avg_row_len / 1024), 2)) order by 4 desc)WHERE ROWNUM <= 10;

Create Database on target Exadata Machine using DBCA

You can use DBCA silent option to create database too. I strongly recommended using DBCA GUI to create databases on Exadata Machine.

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname mart -sid mart -responseFile NO_VALUE -characterSet AL32UTF8 -sysPassword OraPasswd1 -systemPassword OraPasswd1 -createAsContainerDatabase false -pdbAdminPassword OraPasswd1 -databaseType MULTIPURPOSE -automaticMemoryManagement false -totalMemory 5000 -storageType ASM -diskGroupName DATAC1 -redoLogFileSize 50 -emConfiguration NONE -nodeinfo ex01dbadm01,ex01dbadm02,ex01dbadm03 –ignorePreReqs

Step 2 : Create tablespaces based on source database

I have to enable encryption and compression for my migration but I recommend that you should enable OLTP complete at least for Exadata Machine.

select 'create bigfile tablespace '||tablespace_name ||' DATAFILE ''+DATAC1'' SIZE '||sum(bytes)/1024/1024 ||'M ENCRYPTION

 USING ''AES256'' DEFAULT COMPRESS for OLTP STORAGE(ENCRYPT) ;'

       from dba_data_files

       group by tablespace_name

Step 3 : Create public database link to source database  

Note : – DB link be used for network data pump and compare objects between source and target databases.

CREATE public DATABASE LINK src_db CONNECT TO umair IDENTIFIED BY password! USING 'MART';

Step 4 : Create migration directory for logs and dump files

Create directory for Data Pump Usage both on Source and Target

create or replace directory migration as '/etc/migration/mart';

Step 5 : Import Full database META DATA only using DB Link

nohup impdp / full=y content=metadata_only network_link=src_db directory=migration PARALLEL=16 logfile=imp_full_mart.log &

Step 6 : Import Data schema by schema (FIN_MART)

Note : – I am using network option for my data migration about you don’t have network bandwidth or a lot of unsupported objects , you should first export and then import data using dump files.

nohup impdp / schemas=FIN_MART network_link=src_db exclude=index,constraint TABLE_EXISTS_ACTION=REPLACE directory=migration parallel=16 logfile=impdpFIN_MART.log &

 Step 7 : Generate Indexes & Constraints scripts

First create export dump file (META DATA Only)

expdp / schemas=FIN_MART content=metadata_only directory=migration dumpfile=FIN_MART.dmp logfile=expdpFIN_MART.log

Then generate seperate SQL scripts for Indexes and Constraints.

impdp / schemas=FIN_MART include=index sqlfile=FIN_MART_IDX.sql dumpfile=FIN_MART.dmp  directory=migration logfile=imp_FIN_MART_IDX.log

impdp / schemas=FIN_MART include=contraint sqlfile=FIN_MART_CONS.sql dumpfile=FIN_MART.dmp  directory=migration logfile=imp_FIN_MART_CONS.log

Step 8 : Create Indexes using SQLscript

Update FIN_MART_IDX.sql script and replace parallel 1 with 16 then execute it

nohup sqlplus umair/password! @FIN_MART_IDX.sql &

Step 9 : Enable Contraints using SQL script

Update FIN_MART_CONS.sql script and replace ENABLE with ENABLE NOVALIDATE then execute it.

nohup sqlplus umair/password! @FIN_MART_CONS.sql &

 Step 10 : Validate objects

Lastly Validate objects using following SQL and bring any missing objects.

select owner,object_type,count(*) MISSING_OBJECTS

from (

select owner,object_type,object_name

from dba_objects@src_dih

where owner =’FIN_MART’

minus

select owner, object_type, object_name

from dba_objects where owner = ‘FIN_MART’

)

group by owner,object_type

order by owner,object_type

select substr(OWNER,1,10),substr(TABLESPACE_NAME,1,10) ,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments where owner not in ('SYS','SYSTEM','APEX_04020','WMSYS','XDB','MDSYS','AUDSYS','DBSNMP','SCOTT') group by owner, TABLESPACE_NAME order by 3 desc,1;

Patch list

https://nadeemmohammed.wordpress.com/2012/03/13/list-of-oracle-database-patch-set-updates-psu/#11204


select * from gv$asm_operation order by 1,2,3;

col name format a15;
col path format a25;
col failgroup format a20;
--
select dg.name, d.path, d.failgroup, d.failgroup_type,dg.VOTING_FILES
from v$asm_diskgroup dg, v$asm_disk d
where dg.group_number = d.group_number and dg.name = 'GRID'
order by dg.name, d.path, d.failgroup;

crsctl query css votedisk

ASM command

https://mdinh.wordpress.com/2018/08/05/recreate-asm-disks-rac/


[oracle@rac2 ~]$ asmcmd lsdsk -G DATA
Path
/dev/oracleasm/disks/DISK
/dev/oracleasm/disks/FRA

[oracle@rac2 ~]$ asmcmd -p
ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     71319    69503                0           69503              0             N  DATA/
ASMCMD [+] >

+++ Remove DG from Cluster.
[oracle@racnode-dc1-1 sql]$ srvctl remove diskgroup -diskgroup DATA -force
[oracle@racnode-dc1-1 sql]$ srvctl remove diskgroup -diskgroup FRA -force

Table and Index

Table and index extract

set heading off;
 set echo off;
 Set pages 999;
 set long 90000;
 
spool ddl_list.sql
 select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual; 
 select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
 spool off;



set pagesize 0

 set long 90000

 set feedback off

 set echo off 
 spool scott_schema.sql 
 connect scott/tiger;
 SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_TABLES u;
 SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
     FROM USER_INDEXES u;
 spool off;



For table

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME) FROM USER_TABLES;

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME) FROM USER_INDEXES WHERE INDEX_TYPE ='NORMAL';

 DBMS_METADATA




________________________________________________________________________________________


ORA-31693: Table data object
"PMFADMIN"."TMP_PRODUCT_DESC_KWD" failed to load/unload and
is being skipped due to error:



ORA-02354: error in exporting/importing data



ORA-39826: Direct path load of view or synonym
(PMFADMIN.TMP_PRODUCT_DESC_KWD) could not be resolved.













select owner, object_name, object_type, last_ddl_time
from   dba_objects
where  owner = 'SDB' and object_name in ('XSDB_ALL_39A_PLANTS_CNTRCT');

DBlink s

select owner,db_link,username,host from dba_db_links where owner='DdBMON';

select owner,db_link, host from all_db_links;


select instance_name from v$instance@OMD_CACHE;

select max(lengthb(app_lic_nbr)) from v_omdirect_cutadr@omd_cache;

set heading off;

set echo off;

Set pages 999;

set long 90000;

SELECT DBMS_METADATA.GET_DDL('DB_LINK','OMD_CACHE','PUBLIC') from dba_db_links db;

select DB_LINK,owner from dba_db_links where db_link='OMD_CACHE';

$ sqlplus "/as sysdba"
DBLINKS DETAILS
---------------
set pages 50000 lines 32767
col owner for a15
col username for a10
col host for a20
col created for a20
COL DB_LINK FORMAT A30
select owner, db_link, username, host, to_char(created,'dd-mon-yyyy hh24:mi:ss') CREATED from DBA_DB_LINKS order by owner, db_link;

set head off
set pages 0
set long 9999999

select dbms_metadata.get_ddl('DB_LINK','PMF_LINK','STAT_PRODGOV') from dual;


select DB_LINK,owner from dba_db_links where db_link='PM_LINK';


select dbms_metadata.get_ddl ('DB_LINK', db_link, owner) from dba_db_links where owner like 'DBdMON';

or


SELECT DBMS_METADATA.GET_DDL('DB_LINK',db.db_link,db.owner) Text from dba_db_links db WHERE owner like 'DBMON';


CREATE DATABASE LINK POEMDB_BACKUP_REPORT connect to DBdMON identified by "Mokk" using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = OMIP.omi.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = POEddMDB)))';

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;
select * from global_name;
SELECT
'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# and U.NAME in ( 'PRINCEADMIN');



create database link PRINCEADMIN.OARTEST.OMI.COM
connect to APPSQ identified by "OMAPPSQTEST#13" using '(DESCRIPTION=(ADDRESS=(PR
OTOCOL=tcp)(HOST=omidrl031.omi.com)(PORT=1524)) (CONNECT_DATA= (SERVER = DEDICAT
ED) (SERVICE_NAME = omtest)))'
;

Drop database link
==============

select * from dba_db_links where OWNER='SCOTT';
OWNER DB_LINK USERNAME HOST CREATED
------------------------------ -------------------- ------------------------------ -------------------- ---------
SCOTT LINK1 SCOTT testdb 04-NOV-11




SQL>drop database link scott.LINK1;
drop database link scott.LINK1
*
ERROR at line 1:
ORA-02024: database link not found


ORA-02021: DDL operations are not allowed on a remote database





create a procedure

SQL> CREATE PROCEDURE scott.drop_db_link AS

BEGIN

EXECUTE IMMEDIATE 'drop database link LINK1';

END drop_db_link; 2 3 4

5 /

Procedure created

SQL> exec scott.drop_db_link

PL/SQL procedure successfully completed.

SQL> select * from dba_db_links where OWNER='SCOTT';

no rows selected

Troubleshooting Dblinks issues.

1. What is the local and remote database version involved in database link? local database version 11.2.0.3.0 remote database version 18.0.0.0.0 2.For below query uploaded HTML format. set markup html on spool dist_queries.html show user select name from v$DATABASE; SELECT * FROM DBA_DB_LINKS; SELECT * FROM GLOBAL_NAME; select * from DBA_2PC_PENDING; select * from DBA_2PC_NEIGHBORS; select * from sys.pending_trans$; select * from SYS.PENDING_SESSIONS$; select * from SYS.PENDING_SUB_SESSIONS$; select * from V$GLOBAL_TRANSACTION; SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status, KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE'; select * from v$active_services; spool off set markup html off 3. current setting for SQLNET.EXPIRE_TIME on both local and remote database. local database sqlnet.ora NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ADR_BASE = /oraclebase/app/oracle Remote database # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10 NAMES.DEFAULT_DOMAIN = HQ.DAL.OMI.COM SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10 SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

Now check the DBA_2PC_PENDING view for the LOCAL_TRAN_ID.

SET LINESIZE 500;
COL LOCAL_TRAN_ID FORMAT A13
COL GLOBAL_TRAN_ID FORMAT A50
COL STATE FORMAT A8
COL MIXED FORMAT A3
COL HOST FORMAT A10
COL COMMIT# FORMAT A10

SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT#
   FROM DBA_2PC_PENDING
/