beautifying output in sqlplus

Q:
Why SQLPLUS output is not coming properly?
How to get proper output in SQLPLUS?

A:

Here are some techniques to enhance the readability of sqlplus output.

Check this snap. The out put is really difficult to understand.


What can we do to make it readable?
First set the page size to a high values (it will be 6 by default). I used to set it to 100 so that i can see the headings after each 100 lines(including headings lines footers etc).
SQL>set pagesize 100
OR
SQL>set pages 100

If you don't want to see the heading at all. You can set
SQL>set pages 0
lets see the output after setting the page size.

Even now it is not much clear. Now lets set the line size also.I will set the line size to 150 because my screen can accommodate 150 letters in a single line
You can set it as
SQL>set linesize 150
OR
SQL>set lines 150



 Here we cant see much differences in the output apart from the line under FILE_NAME has became longer till the end of the screen. But still you have to do this because this will be the length of the line in your output after formatting the rest as well.

Here the output is coming like this because the column FILE_NAME is 513 bytes long.
Now we can format the column array size.
Now I am setting the column length of FILE_NAME to array of 30 characters. (you can do it depending up on the length of the names of the files in your output).
You can do it as
SQL> column file_name format a30
OR
SQL>col file_name for a30

Now things became under control :P

I am making the output a little more packed so that you don't have to scroll to see the entire outout. I am changing the lengths of the columns TABLESPACE_NAME & FILE_NAME



 OKEY?

Now we can go a little more additional. Here I have 11 rows selected, some of the tablespaces have more than one data file.  I want each tablespace names to be printed only once, it will be easy for me to identify the tablespaces. I will do like this.



Now I feel like calculating the sum of the sizes of each tablespaces here it self. You don't have to re write the query.!!!



 The out put will be a little confusing at the beginning but will be very useful for daily dba activities.
If you don't want to give your own label, you can simply type like
SQL>compute sum of max_gb on tablespace_name
here the label will be 'sum'

If you want the total sum, You can do like this.



 like sum, you can calculate average, max, min etc using this function.
for more information go to oracle documentation
(if you are getting page not found message, click at the address and press enter)

Hope it is helpful,

Thanks
alimajaf






 

cached bytes in temporary tablespce, oracle

What is the difference between bytes_cached and bytes_used in v$temp_extent_pool?

Why 'ORA-1652: unable to extend temp segment by xxxx in tablespace TEMP' comes in alert log but free space is there in temp files?

How segment is managed in temporary tablespace, ORACLE?

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

Normally sorting will happen in PGA. If PGA is not large enough to accommodate the entire  sorting, sorting will be done as chunks and sorted data will be stored in the temporary tablespace temporary. Later the results will be merged together and will produce the desired output.

The temporary space will not be reserved for any users on sessions (in RAC) at the beginning.  Space will be allocated as requirement comes for the temporary space.

You can monitor the space utilization of the temporary tablespace from DBA_TEMP_FREE_SPACE or V$TEMP_EXTENT_POOL. If you want to monitor per user you can use v$tempseg_usage view for it.

When an activity related to the temporary tablespce is completed (for example, Sorting has been completed and the result is given to the user process), The space allocated for the activity  will be marked free but will not be completely released from the segment unless some other users or instance ask for the space ( it is called steeling extents in some cases). This makes a soft reservation on that space by a user or a session. The bytes reserved like this is called cached bytes.

You can monitor this cached bytes from   V$TEMP_EXTENT_POOL. Cached bytes and used bytes will be equal at the beginning of a session but latter there may be huge difference in between. See the difference in one of the production databases where I am working.

  
 
SQL> select TABLESPACE_NAME,sum(BYTES_CACHED)/(1024*1024) MB_CACHED,sum (BYTES_USED)/(1024*1024) MB_USED from  v$temp_extent_pool group by TABLESPACE_NAME;


TABLESPACE_NAME    MB_CACHED    MB_USED
---------------- ----------- ----------
TEMP1                   6218      9.375
TEMP                       0          0
TEMP2                   8190        102

Sometimes it may take time to release the cached bytes by a user or a session, then you may get ORA-1652 even though you have enough free space in the temporary tablespace.

Thanks,

alimajaf