CODEWITHSUNDEEP
oracle11gstorage
plditallo
plditallo

Reputation: 701

Newbie Alert: How Do I Determine How Much Storage I'm Using in an Oracle 11g Database?

Techies--

I am migrating an Oracle 11g database to an MS SQL Server 2012 instance. Before I begin the actual physical copy of data from 11g to MSSQL, how do I determine what kind of space I'll need to set aside? In Oracle SQL Developer I was able to find the Statistics tab while in a SQL session. I see that this tab may give me some of the high-level/rough idea version of what I'm looking for--however, I have several hundred tables, so this approach doesn't seem practical. Is there a way to issue a sql statement, or execute an existing stored proc to determine how much physical space will be needed to contain this data?

enter image description here

Upvotes: 0

Views: 89

Answers (1)

Frederic Henri
Frederic Henri

Reputation: 53703

A few queries that I keep on my hand and are useful for this exercise

find out tablespaces and the space they use

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

Get storage from table within a tablespace

COLUMN TABLE_NAME FORMAT A20
COLUMN TABLESPACE_NAME FORMAT A20

SELECT
SUBSTR(s.segment_name,1,20) TABLE_NAME,
SUBSTR(s.tablespace_name,1,20) TABLESPACE_NAME,
ROUND(DECODE(s.extents, 1, s.initial_extent,
(s.initial_extent + (s.extents-1) * s.next_extent))/1024000,2) ALLOCATED_MB,
ROUND((t.num_rows * t.avg_row_len / 1024000),2) REQUIRED_MB
FROM
dba_segments s,
dba_tables t
WHERE
s.owner = t.owner AND
s.segment_name = t.table_name and
s.tablespace_name = '<yourtablespacename>'
ORDER BY 3 ASC;

Upvotes: 1

Related Questions