Store SQL*Plus variables
Oracle SQL*Plus has a very useful subparameter to the column parameter called NEW_VALUE. The NEW_VALUE directive allows you to store data retrieved from an Oracle table as a variable inside the SQL*Plus script.
By using the NEW_VALUE parameter, you can make your SQL*Plus script behave like a real programming language, storing and addressing program variables, just like in PL/SQL.
The ability to store SQL*Plus variables and fill them with Oracle data is a very powerful feature, and it makes SQL*Plus scripts more efficient because it reduces database access.
col log_mode_val new_value log_mode noprint |
Now that we understand how SQL*Plus variables are stored, let's examine a real-world example. Here's an example from a STATSPACK report that reports on table growth as a function of the block size of the database. Because the DB_BLOCK_SIZE is a constant for the whole database, we can use the NEW_VALUE parameter to capture this value once and then redisplay it as part of our output.
In this example, we define a variable called &blksz and then use it inside the main query to determine the percentage of free space inside the table. The example below shows how we do this calculation. Knowing the block size allows us to quickly estimate the amount of free space inside the table.
(num_rows*avg_row_len)
--------------------- *
100
(blocks*&blksz)
Here is the entire query.
column c1 heading "TABLE
NAME" format a15;
column c2 heading
"EXTS" format
999;
column c3 heading
"FL" format
99;
column c4 heading "# OF
ROWS" format
99,999,999;
column c5 heading "#_rows*row_len" format
9,999,999,999;
column c6 heading "SPACE ALLOCATED" format
9,999,999,999;
column c7 heading "PCT
USED" format
999;
column db_block_size new_value blksz noprint
select value db_block_size from v$parameter where name =
'db_block_size';
set pages 999;
set lines 80;
spool tab_rpt.lst
select
table_name c1,
b.extents c2,
b.freelists c3,
num_rows c4,
num_rows*avg_row_len c5,
blocks*&blksz c6,
((num_rows*avg_row_len)/(blocks*&blksz))*100 c7
from
perfstat.stats$tab_stats a,
dba_segments b
where
b.segment_name = a.table_name
and
to_char(snap_time,'yyyy-mm-dd') =
(select
max(to_char(snap_time,'yyyy-mm-dd')) from
perfstat.stats$tab_stats)
and
avg_row_len > 500
order by c5 desc
;
Donald Burleson has been a database administrator for 23 years and has written 14 database books and over 100 articles. He is editor in chief of Oracle Internals and runs Burleson Oracle Consulting.