| |
 |
|
Oracle Tips by Burleson |
Write a Oracle File Mapping
Script
Your challenge in this exercise is to write a
report that joins dba_data_files and dba_tablespaces to get detailed
information about the mapping for an Oracle database.
Part 1 – Basic report
Your finished report should:
1 - Account for multiple data files per
tablespace
2 - Use the “compute sum” SQL*Plus command to
display totals for each tablespace
Your finished dictionary query should look
like this:
TABLESPACE FILE_NAME
BYTES
--------------- --------------------------------------------------
------------
CWMLITE
C:\ORACLE\ORADATA\DIOGENES\CWMLITE01.DBF 20,971,520
***************
------------
sum
20,971,520
DRSYS C:\ORACLE\ORADATA\DIOGENES\DRSYS01.DBF
20,971,520
***************
------------ sum
20,971,520
EXAMPLE C:\ORACLE\ORADATA\DIOGENES\EXAMPLE01.DBF
159,907,840
***************
------------
sum
159,907,840
INDX
C:\ORACLE\ORADATA\DIOGENES\INDX01.DBF 26,214,400
***************
------------
sum
26,214,400
SYSTEM C:\ORACLE\ORADATA\DIOGENES\SYSTEM01.DBF
340,787,200
***************
------------
sum
340,787,200
TOOLS C:\ORACLE\ORADATA\DIOGENES\TOOLS01.DBF
10,485,760
***************
------------
sum
10,485,760
TS_16K C:\ORACLE\ORADATA\DIOGENES\16K_TS.DBF
10,485,760
***************
------------
sum
10,485,760
UNDOTBS C:\ORACLE\ORADATA\DIOGENES\UNDOTBS01.DBF
209,715,200
***************
------------
sum
209,715,200
USERS C:\ORACLE\ORADATA\DIOGENES\USERS01.DBF
26,214,400
C:\ORACLE\ORADATA\DIOGENES\USERS02.DBF
1,048,576
C:\ORACLE\ORADATA\DIOGENES\USERS03.DBF
512,000
C:\ORACLE\ORADATA\DIOGENES\USERS04.DBF
102,400
***************
------------
sum
27,877,376
Part 2 – Aggregate report (optional)
For an extra challenge, modify your existing
query to display only the totals, and the total for the whole
database. In this report you will use the “sum(bytes)” aggregation
function and the analytical function “group by rollup (t.tablespace_name)
to get totals for each tablespace and a total size for the entire
instance.
If you need to see examples, just do a Google
search on oracle “group by rollup”, and lots of examples will appear.
Your finished report should look like this:
TABLESPACE TOT_BYTES
------------------------------
------------
CWMLITE
20,971,520
DRSYS
20,971,520
EXAMPLE 159,907,840
INDX
26,214,400
SYSTEM 340,787,200
TOOLS 10,485,760
TS_16K
10,485,760
UNDOTBS
209,715,200
USERS 27,877,376
SUM of
database ==>
827,416,576
ANSWERS
Part 1:
column
file_name format a50
column tablespace format a15
column bytes format 999,999,999
break on
tablespace skip 2
compute sum of bytes on tablespace
select
t.tablespace_name tablespace,
file_name,
bytes
from
dba_data_files d,
dba_tablespaces t
where
t.tablespace_name = d.tablespace_name
;
Part 2:
column
tablespace format a30
column tot_bytes format 999,999,999
break on tablespace skip 2
compute sum of bytes on tablespace
select
decode(
grouping(t.tablespace_name),
1, 'SUM of database ==>',
t.tablespace_name
) as tablespace,
sum(bytes) tot_bytes
from
dba_data_files d,
dba_tablespaces t
where
t.tablespace_name = d.tablespace_name
group by rollup (t.tablespace_name);
For more details,
see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology. |
