Summary
1. Dealing database
2. Dealing Tablespaces and datfiles
3. Dealing bigfile tablespace
4. Dealing Undo Tablespace
5. Dealing Temporary Tablespace
6. Dealing Redo logs
1. Dealing database
=> Gather total database size:
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.gv$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from gv$controlfile) "Size in GB"
from dual;
=> Total db free and used size:
SELECT sum((((bytes)/1024)/1024)/1024) "free space in GB" FROM dba_free_space;
-------------------------------------------------------------------------------
SELECT sum((((bytes)/1024)/1024)/1024) "used space in GB" FROM dba_segments;
---
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 gv$log) used, (select sum(bytes) as p from dba_free_space) free
group by free.p;
----------------------------------------------------------------------------------------
=> Total datafile size with unit variation:
select case
when trunc(sum(d.bytes)/1024) < 1024 then to_char(round(sum(d.bytes/1024),2),'99999999999999.99')||' KB'
when trunc(sum(d.bytes)/1024/1024) < 1024 then to_char(round(sum(d.bytes/1024/1024),2),'99999999999999.99')||' MB'
when trunc(sum(d.bytes)/1024/1024/1024) < 1024 then to_char(round(sum(d.bytes/1024/1024/1024),2),'99999999999999.99')||' GB'
when trunc(sum(d.bytes)/1024/1024/1024/1024) < 1024 then to_char(round(sum(d.bytes/1024/1024/1024/1024),2),'99999999999999.99')||' TB'
end DB_SIZE
From dba_data_files d;
=> Individual component size:
select round(sum(bytes)/1024/1024/1024,2) DBF_SIZE_GB from dba_data_files;
---
select round(nvl(sum(bytes),0)/1024/1024/1024,2) TEMP_SIZE_GB from dba_temp_files;
---
select sum(bytes)/1024/1024/1024 REDO_SIZE_GB from gv$log;
---
select round(sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024,2) CTL_SIZE_MB from v$controlfile;
2. Dealing Tablespaces and datfiles
=> All tablespaces usage in percentage
set lines 190;
set pages 190;
SELECT TABLESPACE_NAME TBSP_NAME, USED_SPACE/128/1024 TBSP_USEDSPS_gb, TABLESPACE_SIZE/128/1024 TBSP_SIZE_GB, USED_PERCENT
FROM DBA_TABLESPACE_USAGE_METRICS ;
---
set lines 180
set pages 500
col tablespace_name format a25;
select d.tablespace_name,
case
when trunc(sum(d.bytes)/1024) < 1024 then
to_char(round(sum(d.bytes/1024),2),'99999999999999.99')||' KB'
when trunc(sum(d.bytes)/1024/1024) < 1024 then
to_char(round(sum(d.bytes/1024/1024),2),'99999999999999.99')||' MB'
when trunc(sum(d.bytes)/1024/1024/1024) < 1024 then
to_char(round(sum(d.bytes/1024/1024/1024),2),'99999999999999.99')||' GB'
when trunc(sum(d.bytes)/1024/1024/1024/1024) < 1024 then
to_char(round(sum(d.bytes/1024/1024/1024/1024),2),'99999999999999.99')||' TB'
end total_space,
case
when trunc((sum(d.bytes)-nvl(sum(f.bytes),0))/1024) < 1024 then
to_char(round((sum(d.bytes)-nvl(sum(f.bytes),0))/1024,2),'99999999999999.99')||' KB'
when trunc((sum(d.bytes)-nvl(sum(f.bytes),0))/1024/1024) < 1024 then
to_char(round((sum(d.bytes)-nvl(sum(f.bytes),0))/1024/1024,2),'99999999999999.99') ||' MB'
when trunc((sum(d.bytes)-nvl(sum(f.bytes),0))/1024/1024/1024) < 1024 then
to_char(round((sum(d.bytes)-nvl(sum(f.bytes),0))/1024/1024/1024,2),'99999999999999.99') ||' GB'
when trunc((sum(d.bytes)-nvl(sum(f.bytes),0))/1024/1024/1024/1024) < 1024 then
to_char(round((sum(d.bytes)-nvl(sum(f.bytes),0))/1024/1024/1024/1024,2),'99999999999999.99') ||' TB'
end USED,
case
when trunc(nvl(sum(f.bytes),0)/1024) < 1024 then
to_char(round(nvl(sum(f.bytes),0)/1024,2),'99999999999999.99')||' KB'
when trunc(nvl(sum(f.bytes),0)/1024/1024) < 1024 then
to_char(round(nvl(sum(f.bytes),0)/1024/1024,2),'99999999999999.99') ||' MB'
when trunc(nvl(sum(f.bytes),0)/1024/1024/1024) < 1024 then
to_char(round(nvl(sum(f.bytes),0)/1024/1024/1024,2),'99999999999999.99') ||' GB'
when trunc(nvl(sum(f.bytes),0)/1024/1024/1024/1024) < 1024 then
to_char(round(nvl(sum(f.bytes),0)/1024/1024/1024/1024,2),'99999999999999.99') ||' TB'
end free,
to_char(round((sum(d.bytes)-nvl(sum(f.bytes),0))/sum(d.bytes)*100,2),'99999999999990.99')||'%' "%used",
to_char(round(nvl(sum(f.bytes),0)/sum(d.bytes)*100,2),'99999999999990.99')||'%' "%free"
from (select tablespace_name, file_id, file_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name, file_id, file_name
union all
select tablespace_name, file_id, file_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name, file_id, file_name) d,
(select tablespace_name, file_id, sum(bytes) bytes
from dba_free_space
group by tablespace_name, file_id) f
where d.tablespace_name = f.tablespace_name(+)
and d.file_id = f.file_id(+)
group by d.tablespace_name
order by 2;
=> All tablespaces allocated & max size
set pagesize 999;
set linesize 160;
set tablespace_name format a35;
Select tablespace_name, round(sum(bytes/1024/1024/1024),2) "Allocated_GB", round(sum(user_bytes/1024/1024/1024),2) "Used_GB",
round(sum(maxbytes/1024/1024/1024),2) "Max_size_GB" from dba_data_files group by tablespace_name order by 2;
=> Specific tablespace size
set pagesize 100;
set linesize 140;
col tablespace_name format a20;
col file_name format a55;
Select file_name, tablespace_name, round((bytes/1024/1024),2) "Allocated_MB", round((user_bytes/1024/1024),2) "Used_MB", autoextensible,
increment_by, round((maxbytes/1024/1024),2) "Max_size_MB" from dba_data_files where tablespace_name like '%IBM%' order by 2;
Alter tablespace SIEBELINDX add datafile '+DATAC1' size 30g;
=> Datafile used and free space
col file_name format a60;
SELECT SUBSTR (df.NAME, 1, 50) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;
=> Datafile current and autoextend info
set linesize 140;
col file_name format a58;
col tablespace_name format a19;
Select file_name, tablespace_name, round((bytes/1024/1024/1024),2) " Allocated_GB", autoextensible AUTO,
increment_by, round((maxbytes/1024/1024/1024),2) "Max_size_GB",
round((user_bytes/1024/1024/1024),2) " Used_in_GB" from dba_data_files
order by tablespace_name;
=> Resize datafile:
Alter database datafile 'data_file_name' resize 7000M;
=> Enable auto extend and resize:
Alter database datafile 'datafile_name' autoextend on next 100M maxsize unlimited;
=> Add datafile
Alter tablespace tbs_name add datafile 'datafile_name' size 2g autoextend on next 100m maxsize unlimited;
Alter tablespace SIEBELINDX add datafile '+DATAC1' size 30g autoextend on next 100m maxsize 20g;
=> Create Tablespace
create tablespace CRMOPSRPT_P datafile '+DATAC1' size 10g autoextend on next 100m maxsize 30g;
create temporary tablespace CRMOPSRPT_T tempfile '+DATAC1' size 2g autoextend on next 100m maxsize 10g;
=> Taking tablespace offline:
Alter tablespace <tablespace_name> offline normal;
3. Dealing bigfile tablespace
select name, bigfile from v$tablespace order by 2,1;
---
select tablespace_name,bigfile from dba_tablespaces where bigfile='YES' order by 2;
---
set pagesize 100;
set linesize 140;
col tablespace_name format a20;
col file_name format a55;
Select file_name, tablespace_name, round((bytes/1024/1024),2) "Allocated_MB", round((user_bytes/1024/1024),2) "Used_MB", autoextensible,
increment_by, round((maxbytes/1024/1024),2) "Max_size_MB" from dba_data_files where tablespace_name like 'MUP_DW_DATA%' order by 2;
Alter database datafile '+DATAC1/obieprd/datafile/mup_dw_data.723.862262011' autoextend on next 100m maxsize 8429568m;
--- Temporary tablespace ---
set linesize 140;
col file_name format a55;
col tablespace_name format a20;
Select file_name, tablespace_name, round((bytes/1024/1024/1024),2) "Allocated_GB", autoextensible,
increment_by, round((maxbytes/1024/1024/1024),2) "Max_size_GB",
round((user_bytes/1024/1024/1024),2) " Used_in_GB" from dba_temp_files
where tablespace_name like 'MUP_DW_TEMP';
4. Dealing Undo Tablespace:
show parameter undo
--
set linesize 160;
col name format a40;
col value format a50;
set pagesize 500;
--
select inst_id, name, value from gv$parameter where name in ('undo_retention','undo_management','undo_tablespace') order by value;
=> Check undo tablespace utiliztion
Select distinct status, tablespace_name, sum(bytes), count(*) from dba_undo_extents group by status, tablespace_name order by status;
--
select max(maxquerylen),max(tuned_undoretention) from v$undostat;
--
select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT;
--
select * from DBA_HIST_UNDOSTAT where maxquerysqlid='1b0254sv18yu9' and
to_char(begin_time,'dd-MM-RRRR HH24:MI') BETWEEN TO_char('23-2-2015 04:00') AND TO_CHAR('25-2-2015 11:00')
select sum(bytes) from dba_free_space where tablespace_name='&UNDOTBS';
Note: Before proceed, Invistiagte/Resolve any excessive allocation of ACTIVE/UNEXPIRED extents and high calculation of tuned_undoretention.
=> Undo tablespaces size
col file_name format a60;
select file_name, tablespace_name, (bytes/1024/1024) toltal_size_mb, (user_bytes/1024/1024) used_size_mb, autoextensible,
round(maxbytes/1024/1024/1024,2) max_size_gb
from dba_data_files
where tablespace_name like '%UN%' order by tablespace_name;
=> Total undo tablespace size
---------------------------
Select tablespace_name, round(sum(bytes/1024/1024/1024),2) " Size_in_GB", round(sum(user_bytes/1024/1024/1024),2) " User_bytes_in_GB",
round(sum(maxbytes/1024/1024/1024),2) "Max_size_in_GB"
from dba_data_files
where tablespace_name like '%UN%'
group by tablespace_name order by tablespace_name;
Select round(sum(bytes/1024/1024/1024),2) " Size_in_GB", round(sum(user_bytes/1024/1024/1024),2) " User_bytes_in_GB",
round(sum(maxbytes/1024/1024/1024),2) "Max_size_in_GB"
from dba_data_files
where tablespace_name like '%UN%';
=> Resize undo datafile
Alter database datafile 'datafile_name' resize 30g;
=> Add datafile in undo tablespace
Alter tablespace UNDOTBS2 add datafile '+DATAC1' size 5g autoextend on next 1g maxsize unlimited;
=> On autoextend
Alter database datafile 'file_name' autoextend on next 1g maxsize unlimited;
=> Drop undo tablespace and datafile
Drop tablespace undotbs including contents and datafiles;
=> Create undo tablespace
Create undo tablespace UNDO01 datafile '+DBFS_TMP1' size 5g autoextend on next 1g maxsize 10g
=> Setting a new undo tablespace
Alter system set undo_tablespace=UNDO01 scope=both;
show parameter undo
Drop tablespace UNDOTBS including contents and datafiles;
=> Drop undo datafile (be careful) - It is best practice to create new undo and set parameter then start with new one
Alter database datafile '+DBFS_TMP1/sblprd/datafile/undotbs1_424.dbf' OFFLINE DROP including datafiles;
Alter database datafile '+DBFS_TMP1/sblprd/datafile/undotbs1_424.dbf' DROP;
5. Dealing Temporary Tablespace:
=> Review default temporary tablespaces of all schemas
set lines 190;
set pagesize 100;
Select username,account_status,default_tablespace, temporary_tablespace from dba_users order by 2;
=> Temporary tablepsaces size
Select tablespace_name, sum(bytes/1024/1024/1024) " Size_in_GB", sum(maxbytes/1024/1024/1024) "Max_size_in_GB",
sum(user_bytes/1024/1024/1024) " User_bytes_in_GB"
from dba_temp_files group by tablespace_name;
=> Individual temp file size
set linesize 190;
col file_name format a60;
col tablespace_name format a20;
Select file_name, tablespace_name, round((bytes/1024/1024/1024),2) " Size_in_GB", autoextensible,
increment_by, round((maxbytes/1024/1024/1024),2) "Max_size_in_GB",
round((user_bytes/1024/1024/1024),2) " User_bytes_in_GB"
from dba_temp_files
order by tablespace_name;
-- Size in MB
set linesize 140;
col file_name format a60;
col tablespace_name format a20;
Select file_name, tablespace_name, round((bytes/1024/1024),2) " Size_in_MB", autoextensible,
increment_by, round((maxbytes/1024/1024),2) "Max_size_in_MB",
round((user_bytes/1024/1024),2) " User_bytes_in_MB" from dba_temp_files;
=> Resize temp datafile
Alter database tempfile 'tempfile_name' resize 7000M ;
=> Enable auto extend of temp datafile
Alter database tempfile 'tempfile_name' autoextend on next 5g maxsize 31g;
=> Adding temp file
Alter tablespace TEMP1 add tempfile 'tempfile_name' size 2g autoextend on next 1g maxsize 31g;
=> Drop a temp file
Alter database tempfile 'tempfile_name' drop including datafiles;
=> Shring temporary tablespace
Alter tablespace tablespace_name shrink space;
Alter tablespace temp shrink tempfile 'name';
Alter tablespace temp shrink space keep 100m;
=> Create temporary tablespace
Create temporary tablepsace temp1 tempfile '+DATA_DS' size 5g autoextend on maxsize 10g;
=> Change default temporary tablespace
Alter database default temporary tablespace temp1;
Select 'Alter user ' ||username|| ' temporary tablespace temp1; ' from dba_users;
Alter user <username> temporary tablespace TEMP1;
=> Drop temporary tablespace
drop tablespace temp1 including contents and datafiles;
=> Dealing Temporary tablespace group:
-----------------------------------
Create group by adding existing tablespace.
sql> Alter tablespace temp tablespace group temp_ts_group;
Add a new tablespace to the group.
sql> Create temporary tablespace temp2 tempfile '<path/temp2_01.dbf' SIZE 1g tablespace group temp_ts_group;
sql> Select * from dba_tablespace_groups;
A tablespace can also be removed from a group.
sql> Alter tablespace temp2 tablespace group 'temp_ts_group';
Assign group as the temporary tablespace for a user.
sql> Alter user <ODIDWT> temporary tablespace <Temp_group_name>;
Switch from the group to a specific tablespace.
sql> Alter database default temporary tablespace temp;
6. Dealing Redo logs:
=> Review redo path
set pages 100;
set lines 180;
col member format a60;
select group#,status, type, member from v$logfile order by 1;
=> Review Size and status
select group#,thread#, (bytes/1024/1024) size_mb, MEMBERS, status from v$log order by 1,2;
=> Review path and size in 1 query
Select lf.group#,l.thread#,lf.type,lf.member,l.bytes/1024/1024 AS size_mb,l.status
From v$logfile lf Join v$log l ON l.group#=lf.group#
Order by l.thread#,lf.group#, lf.member;
=> Adding redo log group
Alter database add logfile thread 1/2 group 4/5/6 'D:\oracle9i\oradata\redo04/5/6.LOG' size 10M;
=> Adding a member in logfile
Alter database add logfile thread 1 group 5 '+DATA_DS/sbletl/onlinelog/group_5b.log' size 10240M;
Alter database add logfile thread 1 group 4 ('+DATA_DS/sbldb/onlinelog/group_4a.log','+DATA_DS/sbldb/onlinelog/group_4b.log') size 4g;
Alter database add logfile member '+DATA_DS/sbletl/onlinelog/group_6b.log' to group 6;
=> Dropping redo log
Alter database drop logfile group 1;
=> Forcedly switch a log file.
Alter system switch logfile;
=> Disable a thread
alter database disable thread 2;
To check tablespace quotas
===========================
select * from dba_ts_quotas;
0 Comments