Posts Tagged ‘Oracle data files’

Find A String In Database

July 1st, 2017, posted in Oracle
Share

I’ve tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results :

SELECT * from dba_objects WHERE
object_name like '%DTN%'

A column isn't an object. If you mean that you expect the column name to be like '%DTN%', the query you want is:

SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DTN%';
Share

Oracle : Size Of Database

March 31st, 2015, posted in Oracle
Share

An Oracle Database consists of data files, redo log files, control files, temporary files.Oracle : Size Of Database,Oracle,Size Of Database,Database,data files, redo log files, control files, temporary files,Oracle data files,Oracle  redo log files,Oracle  control files,Oracle  temporary files
Whenever you say the size of the database this actually means the summation of these files.

 

 

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from 
( select sum(bytes)/1024/1024 data_size from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) d;

-------------------------------------------------------------
For Round Figure For MB :

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from 
( select round( sum(bytes)/1024/1024 ) data_size from dba_data_files ) a,
( select round( nvl(sum(bytes),0)/1024/1024 ) temp_size from dba_temp_files ) b,
( select round( sum(bytes)/1024/1024 ) redo_size from sys.v_$log ) c,
( select round( sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 ) controlfile_size from v$controlfile) d;

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

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from 
( select sum(bytes/1024/1024/1024) data_size from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) b,
( select sum(bytes/1024/1024/1024) redo_size from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) d;

-------------------------------------------------------------
For Round Figure GB :

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from 
( select round( sum(bytes/1024/1024/1024) ) data_size from dba_data_files ) a,
( select round( nvl(sum(bytes),0)/1024/1024/1024 ) temp_size from dba_temp_files ) b,
( select round( sum(bytes/1024/1024/1024) ) redo_size from sys.v_$log ) c,
( select round( sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 ) controlfile_size from v$controlfile) d;



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

Get database size from v$datafile:

select round((sum(bytes)/1048576/1024),2) from v$datafile;

------------------------------------------------------------
Get Oracle Database size from dba_data_files:

select "Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)"
from(
select 
(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)",
(select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"
from dual
);

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

 

 

here
a is megabytes allocated to ALL datafiles
b is megabytes allocated to ALL TEMP files
c is megabytes allocated to ALL redo-logs
d is megabytes allocated to ALL control files

Share