Do you know that @ (at sign) can also work as an include directive?
Suppose you have a script for getting your session statistics from v$mystat. But you need a particular list of statistics. Furthermore, you want to use the same list in several places of your script without writing it multiple times.
It is easy to do using @ (at sign).
Here is an example of a script which needs to include another script:
select m.statistic#,n.name,value from v$mystat m, v$statname n
where n.statistic#=m.statistic# and
n.name in (
@mystat_list
);
And here is content of mystat_list.sql
'session logical reads',
'CPU used by this session',
'session connect time',
'user I/O wait time',
'session pga memory max',
'consistent gets',
'physical reads',
'physical writes',
'physical reads direct temporary tablespace',
'physical writes direct temporary tablespace',
'lob reads',
'lob writes',
'parse time cpu',
'parse time elapsed',
'sorts (memory)',
'sorts (disk)',
'sorts (rows)'
The result is:
SQL> select m.statistic#,n.name,value from v$mystat m, v$statname n
2 where n.statistic#=m.statistic# and
3 n.name in (
4 @mystat_list
21 );
STATISTIC# NAME VALUE
----------- --------------------------------------------- -----------
14 session logical reads 203
19 CPU used by this session 8
24 user I/O wait time 6
29 session connect time 1479453315
38 session pga memory max 3395896
132 consistent gets 200
141 physical reads 20
152 physical writes 0
157 physical reads direct temporary tablespace 0
158 physical writes direct temporary tablespace 0
743 lob reads 0
744 lob writes 0
1082 parse time cpu 2
1083 parse time elapsed 8
1100 sorts (memory) 23
1101 sorts (disk) 0
1102 sorts (rows) 23
17 rows selected.
So, you can include whatever you need wherever you need. It can be part of PL/SQL anonymous block, part of a query or something else. Just make sure that resulted text will be syntactically correct.
Suppose you have a script for getting your session statistics from v$mystat. But you need a particular list of statistics. Furthermore, you want to use the same list in several places of your script without writing it multiple times.
It is easy to do using @ (at sign).
Here is an example of a script which needs to include another script:
select m.statistic#,n.name,value from v$mystat m, v$statname n
where n.statistic#=m.statistic# and
n.name in (
@mystat_list
);
And here is content of mystat_list.sql
'session logical reads',
'CPU used by this session',
'session connect time',
'user I/O wait time',
'session pga memory max',
'consistent gets',
'physical reads',
'physical writes',
'physical reads direct temporary tablespace',
'physical writes direct temporary tablespace',
'lob reads',
'lob writes',
'parse time cpu',
'parse time elapsed',
'sorts (memory)',
'sorts (disk)',
'sorts (rows)'
The result is:
SQL> select m.statistic#,n.name,value from v$mystat m, v$statname n
2 where n.statistic#=m.statistic# and
3 n.name in (
4 @mystat_list
21 );
STATISTIC# NAME VALUE
----------- --------------------------------------------- -----------
14 session logical reads 203
19 CPU used by this session 8
24 user I/O wait time 6
29 session connect time 1479453315
38 session pga memory max 3395896
132 consistent gets 200
141 physical reads 20
152 physical writes 0
157 physical reads direct temporary tablespace 0
158 physical writes direct temporary tablespace 0
743 lob reads 0
744 lob writes 0
1082 parse time cpu 2
1083 parse time elapsed 8
1100 sorts (memory) 23
1101 sorts (disk) 0
1102 sorts (rows) 23
17 rows selected.
So, you can include whatever you need wherever you need. It can be part of PL/SQL anonymous block, part of a query or something else. Just make sure that resulted text will be syntactically correct.
No comments:
Post a Comment