Setting the schema name using a parameter
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Setting the schema name using a parameter

  1. #1
    Join Date
    Mar 2005
    Posts
    2

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    (you might need to juggle the quotes . . . )
    @query_tables2.sql 'SURVEY'
    then pick up the argement in the .sql as '&1' e.g LIKE '%&1%'

    http://download-west.oracle.com/docs...13.htm#1006742

  3. #3
    Join Date
    Mar 2005
    Posts
    2
    Your suggestion worked. Thank you very much for your time and 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
  •  


Click Here to Expand Forum to Full Width