This discussion is archived
7 Replies Latest reply: Nov 18, 2012 10:54 PM by 923095 RSS

Multiple select staement in 1 query

923095 Newbie
Currently Being Moderated
Hell Team,

I am New to this SQL world. Please help me out with htis:-

I have following queries:-
#select name from v\$database;
#select log_mode from v\$database;
#select count(*)"INVALID_OBJECTS" from dba_objects where status='INVALID';
#select count(*) "INVALID_N/A_INDEXES" from dba_indexes where status!='VALID';
#select count(*)"Invalid Triggers" from user_objects where OBJECT_NAME like '%TRIGGERS%' and status='VALID';
#select count(*) "Broken Jobs" from dba_jobs where broken!='Y';
#select count(*) "Block Corruption" from v\$database_block_corruption;

i want a table which can be generated just by select cmd and it will list the result of all the above queires as follow:-

DB_NAME ARCH_MOD INV_OBJ INV_IDX INV_TRG B_JOB BLK_CRP
---------- -------------------------------------- -------------------------------------- ---------- ---------- ---------- ----------
PROD NOARCHIVELOG 0 86 6 3 0


I mean to say i want multiple select queries into 1 table (note:- i m not saying to create a tables and then insert,update(using select from other tables), its just a sheel script that will fetch these record into a txt file)
  • 1. Re: Multiple select staement in 1 query
    sb92075 Guru
    Currently Being Moderated
    920092 wrote:
    I mean to say i want multiple select queries into 1 table (note:- i m not saying to create a tables and then insert,update(using select from other tables), its just a sheel script that will fetch these record into a txt file)
    make up your mind.
    into 1 table
    table is not a file
    into a txt file
    the easy solution is into a file the do as below

    SPOOL CAPTURE.RESULTS
    goes above first SELECT
    below goes after last SELECT
    SPOOL OFF
  • 2. Re: Multiple select staement in 1 query
    rp0428 Guru
    Currently Being Moderated
    >
    i want a table which can be generated just by select cmd and it will list the result of all the above queires as follow:-
    >
    Well then you need to do a CREATE TABLE myTable as SELECT ...

    And then UNION ALL each of your queries together. Each query needs to return the same number of columns of the same datatype. You can use NULL in the queries where the value for that column isn't appropriate.

    The first query is the one that needs to provide the proper NAME and datatype for each column.
  • 3. Re: Multiple select staement in 1 query
    923095 Newbie
    Currently Being Moderated
    I am creating a shell script that hit multiple database on various node and put that result into 1 txt file, So

    Option 1:- SPOOL will not work as data is being fetched from multiple node databases

    Option 2:- i cannot create a table as i m not static on 1 node, 20 node will be hit in sequence so i dnt have any option to create table as export cmd will change the SID's one by one!!

    So any other select (select ....) somethinbg like this command that i can do and >>filename.txt

    example of script:-

    #!/bin/bash
    export hostname=$2;
    echo " export started"
    export ORACLE_SID=$1;
    export ORACLE_HOME=/$1db/db/10.2.0;
    export LD_LIBRARY_PATH=/lib:/usr/lib:/usr/local/lib:$ORACLE_HOME/lib;
    export TNS_ADMIN=/$1db/db/tech_st/11.2.0/network/admin/$1_$2;
    PATH=/usr/bin:/usr/sbin:/usr/ccs/bin:/usr/local/oracle/bin:/usr/local/bin:/usr/dt/bin:/usr/openwin/bin:/usr/ucb:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch;
    export PATH=$PATH:$ORACLE_HOME/bin;
    echo $ORACLE_HOME
    echo "all export complete"
    #export var1=/$1db/db/tech_st/11.2.0/$1_$2.env
    #export var2=/$1db/db/tech_st/11.1.0/$1_$2.env
    export var3=/$1db/db/10.2.0/$1_$2.env
    #. /$1db/db/tech_st/11.2.0/$1_$2.env
    #. /$1db/db/tech_st/11.1.0/$1_$2.env
    #./$1db/db/10.2.0/$1_$2.env

    sqlplus / as sysdba <<eof
    select name from v\$database;
    select log_mode from v\$database;
    select count(*)"INVALID_OBJECTS" from dba_objects where status='INVALID';
    select count(*) "INVALID_N/A_INDEXES" from dba_indexes where status!='VALID';
    select count(*)"Invalid Triggers" from user_objects where OBJECT_NAME like '%TRIGGERS%' and status='VALID';
    select count(*) "Broken Jobs" from dba_jobs where broken!='Y';
    select count(*) "Block Corruption" from v\$database_block_corruption;
    exit; >>filename.txt


    i want this result to come in one row... just thas my problem
  • 4. Re: Multiple select staement in 1 query
    sb92075 Guru
    Currently Being Moderated
    920092 wrote:
    I am creating a shell script that hit multiple database on various node and put that result into 1 txt file, So

    Option 1:- SPOOL will not work as data is being fetched from multiple node databases
    SPOOL will work when intelligently used, but since you claim to know better, I'll allow you to struggle.
  • 5. Re: Multiple select staement in 1 query
    Girish Sharma Guru
    Currently Being Moderated
    Something like this ?
    SQL> ed
    Wrote file afiedt.buf
    
      1  select a.name,
      2  a.log_mode,
      3  b."INVALID_OBJECTS",
      4  c."INVALID_N/A_INDEXES",
      5  d."Invalid Triggers",
      6  e."Broken Jobs",
      7  f."Block Corruption"
      8  from
      9  v$database a,
     10  (select count(*) "INVALID_OBJECTS" from dba_objects where status ='INVALID') b,
     11  (select count(*) "INVALID_N/A_INDEXES" from dba_objects where status !='VALID') c,
     12  (select count(*) "Invalid Triggers" from user_objects where OBJECT_NAME like '%TRIGGERS%' and status='VALID') d,
     13  (select count(*) "Broken Jobs" from dba_jobs where broken!='Y') e,
     14* (select count(*) "Block Corruption" from v$database_block_corruption) f
    SQL> /
    
    NAME      LOG_MODE     INVALID_OBJECTS INVALID_N/A_INDEXES Invalid Triggers Broken Jobs Block Corruption
    --------- ------------ --------------- ------------------- ---------------- ----------- ----------------
    ORCL      ARCHIVELOG                 1                   1                0           3                0
    
    SQL>
    Now just try to replace your script with this :

    #!/bin/bash
    export hostname=$2;
    echo " export started"
    export ORACLE_SID=$1;
    export ORACLE_HOME=/$1db/db/10.2.0;
    export LD_LIBRARY_PATH=/lib:/usr/lib:/usr/local/lib:$ORACLE_HOME/lib;
    export TNS_ADMIN=/$1db/db/tech_st/11.2.0/network/admin/$1_$2;
    PATH=/usr/bin:/usr/sbin:/usr/ccs/bin:/usr/local/oracle/bin:/usr/local/bin:/usr/dt/bin:/usr/openwin/bin:/usr/ucb:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch;
    export PATH=$PATH:$ORACLE_HOME/bin;
    echo $ORACLE_HOME
    echo "all export complete"
    #export var1=/$1db/db/tech_st/11.2.0/$1_$2.env
    #export var2=/$1db/db/tech_st/11.1.0/$1_$2.env
    export var3=/$1db/db/10.2.0/$1_$2.env
    #. /$1db/db/tech_st/11.2.0/$1_$2.env
    #. /$1db/db/tech_st/11.1.0/$1_$2.env
    #./$1db/db/10.2.0/$1_$2.env

    sqlplus / as sysdba <<eof
    select a.name,
    a.log_mode,
    b."INVALID_OBJECTS",
    c."INVALID_N/A_INDEXES",
    d."Invalid Triggers",
    e."Broken Jobs",
    f."Block Corruption"
    from
    v\$database a,
    (select count(*) "INVALID_OBJECTS" from dba_objects where status ='INVALID') b,
    (select count(*) "INVALID_N/A_INDEXES" from dba_objects where status !='VALID') c,
    (select count(*) "Invalid Triggers" from user_objects where OBJECT_NAME like '%TRIGGERS%' and status='VALID') d,
    (select count(*) "Broken Jobs" from dba_jobs where broken!='Y') e,
    (select count(*) "Block Corruption" from v\$database_block_corruption) f;
    exit; >>filename.txt

    Let us know if it works for you or not.

    Regards
    Girish Sharma
  • 6. Re: Multiple select staement in 1 query
    923095 Newbie
    Currently Being Moderated
    Thanks Girish, You made my day dude.. thanks now this has been moved to production.. thanks girish once againi
  • 7. Re: Multiple select staement in 1 query
    923095 Newbie
    Currently Being Moderated
    Please dnt misunderstand me, spool is correct what ur saying is right, spool will work...

    But m saying that spool requires a static env to capture data, since i m not capturing data in each node so i cannot use spool as output is stored in a file which is on different node. No offence to ur query sir..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points