Dear Readers,
My name is Franz Devantier, creator of this blog. I am an Oracle Certified
Professional (OCP DBA 11g) Security DBA.
I will be sharing with you the basic duties of an Oracle DBA, and also
some of the undocumented, and not so well known tasks.
Formatting SQL Code
(Essential tool for programming or understanding code)
Let’s imagine that you have been given a piece of SQL code, that you need to modify and use for various requirements. However you were given an unformatted piece of code, as illustrated below.
/* Given */
select Physical_Size.tablespace_name, Physical_Size.Max_Mega_bytes, Physical_Size.Physical_Mega_Bytes,
Free_Space.Free_Mega_Bytes, round(((( Physical_Size.Physical_Mega_Bytes - Free_Space.Free_Mega_Bytes ) /
Physical_Size.Max_Mega_Bytes)*100 ),2) Percentage_Used, round(((( Physical_Size.Max_Mega_Bytes -
Physical_Size.Physical_Mega_Bytes + Free_Space.Free_Mega_Bytes) / Physical_Size.Max_Mega_Bytes )*100),2)
Percentage_Free from (select tablespace_name, round(sum(bytes/1024/1024),0) Physical_Mega_Bytes,round(nvl
(sum(decode (autoextensible,'YES',maxbytes,'NO',bytes)/1024/1024),1),0) Max_Mega_Bytes from dba_data_files
group by tablespace_name ) Physical_Size,(select tablespace_name,round(sum(bytes/1024/1024),0)
Free_Mega_Bytes from dba_free_space group by tablespace_name ) Free_Space where
Physical_Size.tablespace_name = Free_Space.tablespace_name order by free_space.Free_Mega_Bytes;
How do you even begin to understand what this piece of SQL is supposed to do.
The first thing to do is to format it, to make it more understandable, and usable. Suddenly once it is formatted you will notice how it becomes easier to understand.
We start by putting each item selected on its own line, and indenting it a bit. Some of the items include inline calculations, and so may even flow over a few lines. You can indicate this with indentation again.
When we get to the from clause, you notice that you are selecting from inline views, and not just tables, again you can make this clear by using indentation. Finally you get to the where clause, and things are looking a lot clearer now, then it was to start with. Also while you were formatting the code, you were working through it, and this would have helped with your understanding as well.
Does this not look far more understandable to you, than the given at the top of the page?
select Physical_Size.tablespace_name,
Physical_Size.Max_Mega_bytes,
Physical_Size.Physical_Mega_Bytes,
Free_Space.Free_Mega_Bytes,
round(((( Physical_Size.Physical_Mega_Bytes - Free_Space.Free_Mega_Bytes )
/ Physical_Size.Max_Mega_Bytes)*100 ),2) Percentage_Used,
round(((( Physical_Size.Max_Mega_Bytes - Physical_Size.Physical_Mega_Bytes
+ Free_Space.Free_Mega_Bytes)
/ Physical_Size.Max_Mega_Bytes )*100),2) Percentage_Free
from (select tablespace_name,
round(sum(bytes/1024/1024),0) Physical_Mega_Bytes,
round(nvl(sum(decode(autoextensible,'YES',
maxbytes,'NO',bytes)/1024/1024),1),0) Max_Mega_Bytes
from dba_data_files
group by tablespace_name ) Physical_Size,
(select tablespace_name,
round(sum(bytes/1024/1024),0) Free_Mega_Bytes
from dba_free_space
group by tablespace_name ) Free_Space
where Physical_Size.tablespace_name = Free_Space.tablespace_name
order by free_space.Free_Mega_Bytes;
Before we go ahead and run this in a SQ*Plus environment, lets first set up some environment variables. The linesize is by default 80, which is too small for our purposes, so we will set it to 120. The pagesize is 14 by default, so to prevent getting headings through our output every few lines, we can set this to 250.
/* setup values for the report */
set linesize 120 pagesize 250 trimspool on feedback off
Now that we have our piece of SQL formatted into a more understandable format, we can format the columns, to ensure that our output is more readable.
/* format the column output */
col tablespace_name for a30
col Physical_Mega_Bytes for 99,999,999
col Max_Mega_bytes for 99,999,999
col Free_Mega_Bytes for 99,999,999
col Percentage_Used for 999.99
col Percentage_Free for 999.99
Now let’s assume that we did not want to return all the tablespaces, in the databases, but just one or a few of them. Because the SQL is formatted it is easy to see where to make this change. Just after the where clause we can add something like this:
“and Physical_Size.tablespace_name in ('SYSTEM','SYSAUX') “
Now we will only receive info back for the two tablespace specified.
/* Specific tablespaces */
select Physical_Size.tablespace_name,
Physical_Size.Max_Mega_bytes,
Physical_Size.Physical_Mega_Bytes,
Free_Space.Free_Mega_Bytes,
round(((( Physical_Size.Physical_Mega_Bytes - Free_Space.Free_Mega_Bytes )
/ Physical_Size.Max_Mega_Bytes)*100 ),2) Percentage_Used,
round(((( Physical_Size.Max_Mega_Bytes - Physical_Size.Physical_Mega_Bytes
+ Free_Space.Free_Mega_Bytes)
/ Physical_Size.Max_Mega_Bytes )*100),2) Percentage_Free
from (select tablespace_name,
round(sum(bytes/1024/1024),0) Physical_Mega_Bytes,
round(nvl(sum(decode(autoextensible,'YES',
maxbytes,'NO',bytes)/1024/1024),1),0) Max_Mega_Bytes
from dba_data_files
group by tablespace_name ) Physical_Size,
(select tablespace_name,
round(sum(bytes/1024/1024),0) Free_Mega_Bytes
from dba_free_space
group by tablespace_name ) Free_Space
where Physical_Size.tablespace_name = Free_Space.tablespace_name
and Physical_Size.tablespace_name in ('SYSTEM','SYSAUX')
order by Physical_Size.tablespace_name,
free_space.Free_Mega_Bytes;
TABLESPACE_NAME MAX_MEGA_BYTES PHYSICAL_MEGA_BYTES FREE_MEGA_BYTES PERCENTAGE_USED PERCENTAGE_FREE
------------------------------ -------------- ------------------- --------------- --------------- ---------------
SYSAUX 20,000 600 128 2.36 97.64
SYSTEM 20,000 650 108 2.71 97.29
SQL>
But what if you did not want all the details but only the maximum megabytes, then you can change the query in this way. You see it is easy now, that you can easily understand what is going on in the query.
/* Specific tablespaces, Maximum Megabytes */
select Physical_Size.tablespace_name,
Physical_Size.Max_Mega_bytes
from (select tablespace_name,
round(sum(bytes/1024/1024),0) Physical_Mega_Bytes,
round(nvl(sum(decode(autoextensible,'YES',
maxbytes,'NO',bytes)/1024/1024),1),0) Max_Mega_Bytes
from dba_data_files
group by tablespace_name ) Physical_Size,
(select tablespace_name,
round(sum(bytes/1024/1024),0) Free_Mega_Bytes
from dba_free_space
group by tablespace_name ) Free_Space
where Physical_Size.tablespace_name = Free_Space.tablespace_name
and Physical_Size.tablespace_name in ('SYSTEM','SYSAUX')
order by Physical_Size.tablespace_name,
free_space.Free_Mega_Bytes;
TABLESPACE_NAME MAX_MEGA_BYTES
------------------------------ --------------
SYSAUX 20,000
SYSTEM 20,000
SQL>
Maybe you only wanted to look at the Physical megabytes for one or more tablespaces. Now see how easy it is to bring this change into the SQL code, now that you have formatted it.
/* Specific tablespaces, Physical Mega Bytes */
select Physical_Size.tablespace_name,
Physical_Size.Physical_Mega_Bytes
from (select tablespace_name,
round(sum(bytes/1024/1024),0) Physical_Mega_Bytes,
round(nvl(sum(decode(autoextensible,'YES',
maxbytes,'NO',bytes)/1024/1024),1),0) Max_Mega_Bytes
from dba_data_files
group by tablespace_name ) Physical_Size,
(select tablespace_name,
round(sum(bytes/1024/1024),0) Free_Mega_Bytes
from dba_free_space
group by tablespace_name ) Free_Space
where Physical_Size.tablespace_name = Free_Space.tablespace_name
and Physical_Size.tablespace_name in ('SYSTEM','SYSAUX')
order by Physical_Size.tablespace_name,
free_space.Free_Mega_Bytes;
TABLESPACE_NAME PHYSICAL_MEGA_BYTES
------------------------------ -------------------
SYSAUX 600
SYSTEM 650
Maybe you were only really interested in the free megabytes.
/* Specific tablespaces, Free Mega Bytes */
select Physical_Size.tablespace_name,
Free_Space.Free_Mega_Bytes
from (select tablespace_name,
round(sum(bytes/1024/1024),0) Physical_Mega_Bytes,
round(nvl(sum(decode(autoextensible,'YES',
maxbytes,'NO',bytes)/1024/1024),1),0) Max_Mega_Bytes
from dba_data_files
group by tablespace_name ) Physical_Size,
(select tablespace_name,
round(sum(bytes/1024/1024),0) Free_Mega_Bytes
from dba_free_space
group by tablespace_name ) Free_Space
where Physical_Size.tablespace_name = Free_Space.tablespace_name
and Physical_Size.tablespace_name in ('SYSTEM','SYSAUX')
order by Physical_Size.tablespace_name,
free_space.Free_Mega_Bytes;
TABLESPACE_NAME FREE_MEGA_BYTES
------------------------------ ---------------
SYSAUX 128
SYSTEM 108
SQL>
Maybe you needed to answer questions as to the percentage used of certain tablespaces. So you would adjust your query like this. You adjust it by simply taking the parts out that you don’t need.
/* Specific tablespaces, percentage used */
select Physical_Size.tablespace_name,
round(((( Physical_Size.Physical_Mega_Bytes - Free_Space.Free_Mega_Bytes )
/ Physical_Size.Max_Mega_Bytes)*100 ),2) Percentage_Used
from (select tablespace_name,
round(sum(bytes/1024/1024),0) Physical_Mega_Bytes,
round(nvl(sum(decode(autoextensible,'YES',
maxbytes,'NO',bytes)/1024/1024),1),0) Max_Mega_Bytes
from dba_data_files
group by tablespace_name ) Physical_Size,
(select tablespace_name,
round(sum(bytes/1024/1024),0) Free_Mega_Bytes
from dba_free_space
group by tablespace_name ) Free_Space
where Physical_Size.tablespace_name = Free_Space.tablespace_name
and Physical_Size.tablespace_name in ('SYSTEM','SYSAUX')
order by Physical_Size.tablespace_name,
free_space.Free_Mega_Bytes;
TABLESPACE_NAME PERCENTAGE_USED
------------------------------ ---------------
SYSAUX 2.36
SYSTEM 2.71
Maybe the requirement is to determine the percentage free in a select list of tablespaces. Now you adjust your formatted SQL to look like this:
/* Specific tablespaces, percentage free */
select Physical_Size.tablespace_name,
round(((( Physical_Size.Max_Mega_Bytes - Physical_Size.Physical_Mega_Bytes
+ Free_Space.Free_Mega_Bytes)
/ Physical_Size.Max_Mega_Bytes )*100),2) Percentage_Free
from (select tablespace_name,
round(sum(bytes/1024/1024),0) Physical_Mega_Bytes,
round(nvl(sum(decode(autoextensible,'YES',
maxbytes,'NO',bytes)/1024/1024),1),0) Max_Mega_Bytes
from dba_data_files
group by tablespace_name ) Physical_Size,
(select tablespace_name,
round(sum(bytes/1024/1024),0) Free_Mega_Bytes
from dba_free_space
group by tablespace_name ) Free_Space
where Physical_Size.tablespace_name = Free_Space.tablespace_name
and Physical_Size.tablespace_name in ('SYSTEM','SYSAUX')
order by Physical_Size.tablespace_name,
free_space.Free_Mega_Bytes;
TABLESPACE_NAME PERCENTAGE_FREE
------------------------------ ---------------
SYSAUX 97.64
SYSTEM 97.29
Maybe you wanted to compare the physical size of a selection of tablespaces to their maximum size.
/* Specific tablespaces, physical size, plus maximum size */
select Physical_Size.tablespace_name,
Physical_Size.Max_Mega_bytes,
Physical_Size.Physical_Mega_Bytes
from (select tablespace_name,
round(sum(bytes/1024/1024),0) Physical_Mega_Bytes,
round(nvl(sum(decode(autoextensible,'YES',
maxbytes,'NO',bytes)/1024/1024),1),0) Max_Mega_Bytes
from dba_data_files
group by tablespace_name ) Physical_Size,
(select tablespace_name,
round(sum(bytes/1024/1024),0) Free_Mega_Bytes
from dba_free_space
group by tablespace_name ) Free_Space
where Physical_Size.tablespace_name = Free_Space.tablespace_name
and Physical_Size.tablespace_name in ('SYSTEM','SYSAUX')
order by Physical_Size.tablespace_name,
free_space.Free_Mega_Bytes;
TABLESPACE_NAME MAX_MEGA_BYTES PHYSICAL_MEGA_BYTES
------------------------------ -------------- -------------------
SYSAUX 20,000 600
SYSTEM 20,000 650
You could if you wanted to go on, and create many different ways of looking at the data. If you remember the given piece of SQL, which was very difficult to adjust. After the formatting, things had become much easier to understand and manipulate. Remember it is best practice to set the default values back when you have finished. Although strictly speaking, if you are going to close the SQL*Plus session, then you don’t need to do this.
/* set back to default values */
set linesize 80 pagesize 14 trimspool off feedback on
clear columns
Franz Devantier,
Need a database health check?
Income stabilizer (win-win opportunity)
Join the carefully selected and tested cash-flow generating
program below to potentially create a long-term residual or annuity type income
enhancer for yourself.
|
Traffic Wave - Free Report: The report will
give you all the information you need to start making a nice long-term residual
income stream for yourself.
|
No comments:
Post a Comment