-
Setting the schema name using a parameter
Hello,
I would like to run a script on a weekly basis that monitors table and index extents for various schemas on mutliplte databases. I am looking for a way to set the schema parameter, so I can call one sql script without having to specify the schemas multiple times. Below is the idea, but I don't know how to set a parameter for the schema name ("Alter session set current_schema=" won't work.).
This script calls query_tables2.sql
sqlplus /nolog
conn system@qhtotpd1
set SCHEMA=SURVEY
@query_tables2.sql
conn system@prsotpd1
set SCHEMA=PRICING
@query_tables2.sql
This is query_tables2.sql
select a.owner, a.table_name,a.max_extents,b.extents,c.instance_name
from v$instance c,dba_tables a,dba_segments b where a.table_name=b.segment_name
and a.owner in ('%SCHEMA%');
select a.owner, a.index_name,a.max_extents,b.extents
from dba_indexes a,dba_segments b where a.index_name=b.segment_name
and a.owner in ('"SCHEMA"')
Obviously "set SCHEMA=" won't work, but I am hoping there is something like it.
Any ideas? Thank you for your help.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|