Fragmentation & Re-organisation




Total  Savings:

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
/



HWM query for all datafiles:

col data_file_name for a50
col tablespace_name for a20
set lines 300

select tablespace_name,
file_id,
file_name DATA_FILE_NAME,
Allocated_MBYTES,
High_Water_Mark_MBYTES,
FREE_MBYTES,
trunc((FREE_MBYTES/Allocated_MBYTES)*100,2) "% Free",
trunc(Allocated_MBYTES-High_Water_Mark_MBYTES,2) Resizable
from
(
select ddf.tablespace_name tablespace_name,
ddf.file_id file_id,
ddf.file_name file_name,
ddf.bytes/1024/1024 Allocated_MBYTES,
trunc((ex.hwm*(dt.block_size))/1024/1024,2) High_Water_Mark_MBYTES,
FREE_MBYTES
from
dba_data_files ddf,
dba_tablespaces dt,
(
select file_id, sum(bytes/1024/1024) FREE_MBYTES
from dba_free_space
group by file_id
) free,
(
select file_id, max(block_id+blocks) hwm
from dba_extents
group by file_id
) ex
where ddf.file_id = ex.file_id
and ddf.tablespace_name = dt.tablespace_name
and ddf.file_id = free.file_id (+)
order by ddf.tablespace_name, ddf.file_id
);

Extents near to HWM:

select file_name data_file_name,
segment_type,
owner||'.'||segment_name segment_name,
partition_name,
block_id,
blockId_Mbytes
from
(
select
de.owner owner,
de.segment_name segment_name,
de.segment_type segment_type,
de.block_id block_id,
DE.PARTITION_NAME partition_name,
ddf.file_name file_name,
trunc((de.block_id*(dt.block_size))/1024/1024,2) blockId_Mbytes
from dba_extents de, dba_data_files ddf, dba_tablespaces dt
where ddf.file_id = &file_id 
and ddf.file_id = de.file_id
and ddf.tablespace_name = dt.tablespace_name
order by de.block_id desc
)
where rownum <= 100;

Database Size:
                   
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
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
/               
                   
Top 20 Segments Used:
                   
set lines 300
set pages 1000
col owner for a30
col segment_name for a30
col tablespace_name for a30

select * from ( select segment_type, segment_name, owner, tablespace_name, bytes/1024/1024 as Size_MB from dba_segments order by 4 desc) where rownum <=20;

Top 20 LOB Segments Used:

set lines 300
set pages 1000
col owner for a30
col table_name for a30
col column_name for a30
col segment_name for a30
col tablespace_name for a30
col size_mb for 999999999.00

select * from ( select l.owner,l.table_name,l.column_name,1.segment_name,1.tablespace_name,round(s.bytes/1024/1024,2) as size_mb from dba_lobs l join dba_segments s on s.owner=l.owner and s.segment_name=l.segment_name order by 6 desc ) where rownum <=20;

 Tablespace Size:

select t.tablespace,  t.totalspace as " Totalspace(MB)",
     round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
     nvl(fs.freespace,0) as "Freespace(MB)",
     round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
     round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
     from
     (select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
     from dba_data_files d
     group by d.tablespace_name) t,
     (select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
     from dba_free_space f
     group by f.tablespace_name) fs
     where t.tablespace=fs.tablespace (+)
     order by "% Free";         

Get DDL for tablespace

set long 999999999
select dbms_metadata.get_ddl('TABLESPACE','<old_tablespace_name>') from dual;

col file_name for a60
col tablespace_name for a30
select file_id,tablespace_name,file_name,bytes/1024/1024/1024 as Size_GB, autoextensible, status from dba_data_files where tablespace_name='<TABLESPACE_NAME>';

select distinct segment_name,owner,segment_type,bytes/1024/1024 as Size_MB,tablespace_name from dba_segments where tablespace_name='<TABLESPACE_NAME>' order by segment_name;

select owner,segment_name,segment_type,partition_name,tablespace_name from dba_segments where tablespace_name='<TABLESPACE_NAME>' ;

select owner,table_name,column_name,segment_name,index_name,tablespace_name from dba_lobs where segment_name in ('<SEGMENT_NAME>');

col object_name for a40
select owner,object_name,object_type,created,last_ddl_time,status from dba_objects where status <>'VALID';

col owner for a20
col referenced_name for a30
col referenced_link_name for a10
col dependency_type for a10
select * from dba_dependencies where owner='<OWNER_NAME>';
select owner,name,type,referenced_owner,referenced_name,referenced_type,referenced_link_name,dependency_type from dba_dependencies where owner='<OWNER_NAME>';

select * from dba_tab_privs where owner='<OWNER_NAME>';
select * from dba_sys_privs where grantee='<OWNER_NAME>';
select * from dba_role_privs where grantee='<OWNER_NAME>';


Table:

select 'alter table '||owner||'.'||table_name||' move tablespace '||'<new_tablespace_name> ;' from dba_tables where table_name in ( select distinct segment_name from dba_segments where tablespace_name='<old_tablespace_name>' and partitioned='NO';
  
  Index:
  
  select 'alter index '||owner||'.'||index_name||' rebuild online parallel '||degree||' ;' from dba_indexes where table_name in ( select distinct segment_name from dba_segments where tablespace_name='<old_tablespace_name>') and partitioned='NO';
  
  Table Partition:
  
  set pages 3000
  set lines 300
  select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name||' tablespace <new_tablespace_name> ;' from dba_tab_partitions where table_name in ( select distinct segment_name from dba_segments where tablespace_name='<old_tablespace_name>' and segment_type='TABLE PARTITION');
  
  Index partition:
  
  select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' ;' from dba_ind_partitions where index_name in ( select index_name from dba_indexes where table_name in ( select distinct segment_name from dba_segments where tablespace_name='<old_tablespace_name>'));
  
  Run Below last for partitioned parent table:
  
  select 'alter table '||owner||'.'||table_name||' move tablespace '||'<new_tablespace_name> parallel '||degree||';' from dba_tables where table_name in ( select distinct segment_name from dba_segments where tablespace_name='<old_tablespace_name>') and partitioned='YES';

Comments

Post a Comment

Popular Posts

AWR Report Analysis

Image
AWR report analysis is important for  Oracle DBA on Daily issues or P1/P2 issues time. Below some of the main things we general look to figure out issue. 1. 2.  3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13.
Read more

Goldengate 19c Installation with issue resolution

Image
  Basic Installation of GoldenGate 19c in OEL6. Tested with below requirements: Step1: Vmware requirements:   For installing OEL6.8 and Oracle DB 12c  installation, please refer URL https://venuoracledba.blogspot.com/2020/08/oracle-12c-2-node-rac-setup-in-vmware.html     Step2: After OEL6.8 installation finished. We will use /opt for goldengate installation.   Step3: unzip GG software in folder. Here unzipped in gg_software folder.     Folder fbo_ggs_Linux_x64_shiphome will create. Step4:  Enable GUI by issuing x host + command in root user. and keep open this root terminal.   Step5:  Enable GUI by issuing x host + command in oracle user.   Move to software path   execute ./runInstaller and select database version. Selected 12c version for my environment. and click Next     provide GG home, oracle home and port number and click Next.     After verifying paths and information, Click Install   ...
Read more

Oracle 12c 2-node RAC setup in VMware Player 15 -- Part01

Image
Tried to install Oracle 12c 2-node RAC for testing purpose but due to low Hardware configuration, ended up with Single Node RAC. I got lot of issues during Installation and tried to resolve them with my best knowledge. Please find below installation steps and troubleshooting. Main issues started from Step90. you can go through quickly. Requirements Most of the times, bridge option in VMWARE will not connect with host machine. I tried below option to use internet connection from host machine. We need to cross verify vmnet8 is NAT or not. Installation Steps Step1 launch VMware and click on New Virtual Machine Step2   select OEL iso Image Step3 Provide New Virtual Machine Name Step4 Provide HDD size and select single virtual disk. Step5  Select host setting for monitor and Network as NAT Step6 New Virtual Machine is ready for launch. Step7 OEL Installation starts automatically. Press Enter Step8 Skip Disk Checking Step9 Click Next Step10   Select langu...
Read more

Execution_Explain Plan

Whenever we get request for application slowness or sql query then we need to identify sql_id and to analyze sql_id, we need to check SQL execution/explain plan. 1. EXECUTION_PLAN: select id,plan_hash_value,operation, options,object_name,cardinality,cost,bytes,cpu_cost,io_cost,time,partition_start,partition_stop, object_node,other_tag,qblock_name,other_xml from dba_hist_sql_plan where sql_id='<SQL_ID>'; or select * from table(dbms_xplan.display_awr('<SQL_ID>')); EXPLAIN_PLAN: explain plan for <SQL_QUERY>; select * from table(dbms_xplan.display);
Read more

Oracle to Postgres

Image
  Round 1: Created metadata for tables, sequences, packages Round 2: Created indexes Round 3: freeze column name to freezes to_date function updated GEOMETRY datatype , installed POSTGIS package to support this datatype Round 4: created triggers created Functions Migration: bash-4.2$ pwd /usr/edb/migrationtoolkit/etc bash-4.2$ cat toolkit.properties SRC_DB_URL=jdbc:oracle:thin:@localhost:1521:orcl SRC_DB_USER=hr SRC_DB_PASSWORD=hr TARGET_DB_URL=jdbc:edb://postoffline:5444/dev TARGET_DB_USER=enterprisedb TARGET_DB_PASSWORD=password bash-4.2$  -bash-4.2$ pwd /usr/edb/migrationtoolkit/bin -bash-4.2$ ls -lrt total 724 -rwxr-xr-x. 1 root root   2439 Dec 11 13:29 runJavaApplication.sh -rwxr-xr-x. 1 root root    544 Dec 11 13:33 runMTK.sh -rwxr-xr-x. 1 root root 729258 Dec 11 13:33 edb-migrationtoolkit.jar -bash-4.2$  ./runMTK.sh HR OUTPUT: Running EnterpriseDB Migration Toolkit (Build 55.7.0) ... Source database connectivity info... conn =jdbc:oracle:thin:@o...
Read more

Active Session History Analysis

Image
ASH  analysis is important for  Oracle DBA on Daily issues or P1/P2 issues time. Below some of the main events we general look to figure out issue. RAC / Cloud RDS: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. Redo Generation: 14. 15. 16. 17. 18. 19.
Read more

General Queries

                                                                              Tablespace growth Size We can use view to check tablespace size greater than 80% RAC / NON-RAC / Cloud RDS: SELECT tbm.TABLESPACE_NAME,   round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) as USED_SPACE_GB,   round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) as TBS_SIZE_GB,   round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) as TBS_FREE_SIZE_GB,   round(tbm.USED_PERCENT) as PERCENT_USEDFROM dba_tablespace_usage_metrics tbm      join dba_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME w...
Read more

Oracle 11g Physical Standy Setup

  Step1: take Binaries tar backup from Primary DB server tar -cvf /path/11.2.0.4.tar 11.2.0.4 Step2: untar Binaries tar back on Standby DB server tar -xvf /path/11.2.0.4.tar -C /path ###################### Binaries Cloning ################### Step3: run clone command on Standby DB server export ORACLE_HOME=<PATH> cd $ORACLE_HOME/clone/bin perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_BASE='<PATH>' ORACLE_HOME='<PATH>' ORACLE_HOME_NAME='<ANY_NAME>' ###################### POST Binaries Cloning ################### Step4: run root scripts $ORACLE_BASE/oraInventory/orainstRoot.sh $ORACLE_HOME/root.sh ###################### Pre-requisites ################### Standby: Step5: update hostnames in listener and tnsnames files Step6: start the listener in standby Server Primary: Step7: check force logging enabled -- to make sys activities will logged in redo logs SQL> select force_logging from v$database; - NO ( default ) to enable force logging - S...
Read more

Postgresql installation offline using NGINX

  [root@localhost yumdownloaddir]# sudo  yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm Loaded plugins: langpacks, ulninfo epel-release-latest-7.noarch.rpm                                                                                                                                                            |  15 kB  00:00:00      Examining /var/tmp/yum-root-dewJ5H/epel-release-latest-7.noarch.rpm: epel-release-7-14.noarch Marking /var/tmp/yum-root-dewJ5H/epel-release-latest-7.noarch.rpm to be installed Resolving Dependencies --...
Read more