7 Replies Latest reply: Nov 19, 2012 12:54 AM by 923095 RSS

    Multiple select staement in 1 query

    923095
      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
          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
            >
            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
              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
                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
                  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
                    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
                      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..