Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

simple select?

396476Aug 29 2007 — edited Aug 29 2007
Hi,

I've been attempting to select (for example) the people of which all projects are older than 1 year and of which all projects also have an inactive state (state 3 and 4). I'm pretty sure this can be done in one select, but how?????? Can someone assist?

CREATE TABLE PROJECT (
id_project NUMBER(1),
person VARCHAR2(10),
id_state NUMBER(1),
last_update DATE
);

INSERT INTO PROJECT VALUES (1, 'Joe', 3, TO_DATE('08-08-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (2, 'Mark', 2, TO_DATE('07-05-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (3, 'Mark', 3, TO_DATE('06-27-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (4, 'Mark', 4, TO_DATE('03-14-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (5, 'Carl', 3, TO_DATE('12-08-2004','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (6, 'Carl', 4, TO_DATE('01-22-2005','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (7, 'Sam', 2, TO_DATE('06-15-2006','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (8, 'Paul', 1, TO_DATE('07-30-2007','MM-DD-YYYY'));
INSERT INTO PROJECT VALUES (9, 'Paul', 2, TO_DATE('06-02-2006','MM-DD-YYYY'));

So in this case the select should only return the name Carl as both his projects are inactive and both are older than one year.

All I got this far is a statement which retrieves the people of which all projects are older than one year. How do I extend this so it will limit the result to the people of which all projects are also inactive (state 3 and 4)? Being only Carl, as Sams project is still active.

SQL> SELECT person, COUNT(id_project), MAX(last_update)
2 FROM PROJECT
3 GROUP BY person
4 HAVING MAX(last_update) < ADD_MONTHS(SYSDATE,-12)
5 ORDER BY 1;

PERSON COUNT(ID_PROJECT) MAX(LAST_
---------- --------------------------------- -----------------
Carl 2 22-JAN-05
Sam 1 15-JUN-06

Comments

Franck Pachot

Hi,

How are you connected? local? through service?

Regards,

Franck.

Ankur Raina

I just connected as:

sqlplus / as sysdba

where $ORACLE_SID is set to the CDB instance name.

unknown-7404

I can't reproduce the issue in vanilla 12.1.0.1.0.


Franck Pachot

Hi,

Can you show the execution plan, for example:

SQL> set autotrace trace explain

SQL> select * from cdb_data_files;

Execution Plan

----------------------------------------------------------

Plan hash value: 1439328272

---------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

---------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |          | 30000 |    11M|     1 (100)| 00:00:01 |       |       |        |      |            |

|   1 |  PX COORDINATOR         |          |       |       |            |          |       |       |        |      |            |

|   2 |   PX SEND QC (RANDOM)   | :TQ10000 | 30000 |    11M|     1 (100)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX PARTITION LIST ALL|          | 30000 |    11M|     1 (100)| 00:00:01 |     1 |   254 |  Q1,00 | PCWC |            |

|   4 |     FIXED TABLE FULL    | X$CDBVW$ | 30000 |    11M|     1 (100)| 00:00:01 |       |       |  Q1,00 | PCWP |            |

---------------------------------------------------------------------------------------------------------------------------------

Regards,

Franck.

Vlad Visan-Oracle

I have tried that on a 12.1.0.2 multitenant database and I get the same.

However, I am not sure if it is a bug or expected behavior.

Franck Pachot

Right it is the same in 12.1.0.2

I don't think it is an expected behavior. It can be very misleading for scripts that need to identify all datafiles.

I often use RMAN> report schema; which gives the right result.

Franck Pachot

In 12.2.0.2 select from CDB$VIEW() has been replaced with select from CONTAINERS() and there is probably a but with that (not showing seed).

Vlad Visan-Oracle

There is a parameter in 12.1.0.2 that must be set to FALSE in order to get the same info as in 12.1.0.1:

alter system set "EXCLUDE_SEED_CDB_VIEW"=FALSE scope=both;

Run the query again at the root level:

select FILE_NAME from cdb_data_files;



Franck Pachot

Thanks for the info.

However, it does not work consistently for all views:

SQL> show parameter exclude_seed_cdb_view

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

exclude_seed_cdb_view                boolean     TRUE

SQL> select CON_ID,PDB_ID,PDB_NAME from containers(dba_pdbs);

    CON_ID     PDB_ID PDB_NAME

---------- ---------- ----------

         3          3 PDB1

        2          2 PDB$SEED

Vlad Visan-Oracle

What do you mean?

Franck Pachot

As exclude_seed_cdb_view=true, I didn't expect a select from containers(dba_pdbs) to return a line with CON_ID=2

Vlad Visan-Oracle

Yeah.. maybe it is just for some views, like the cdb_data_files view. We usually do not want to see the PDB$SEED pdb database files because we rarely need them.. this is just an opinion.

Franck Pachot

Except if we read it to get all database files (for backup, maintenance, etc) and then missing some of them is an issue...

Anyway, thanks for the info about exclude_seed_cdb_view. My tests here: 12.1.0.2 CDB views are now using CONTAINERS() - dbi services Blog

Ankur Raina

Hi Franck

Thanks for the reply here. I was busy so got no time to follow up my own question here.

But what I learnt was this:

BANNER

--------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE    12.1.0.2.0      Production

TNS for Linux: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

SQL> show parameter EXCLUDE_SEED_CDB_VIEW

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

exclude_seed_cdb_view                boolean     TRUE

Note: We do see PDB$SEED even when this parameter is TRUE.

SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$CONTAINERS;

    CON_ID NAME                           OPEN_MODE

---------- ------------------------------ ----------

         1 CDB$ROOT                       READ WRITE

         2 PDB$SEED                       READ ONLY

         3 PDB1T                          MOUNTED

but CDB_DATA_FILES doesn't include the seed datafiles

SQL> SELECT CON_ID, TABLESPACE_NAME FROM CDB_DATA_FILES;

    CON_ID TABLESPACE_NAME

---------- ------------------------------

         1 SYSTEM

         1 SYSAUX

         1 UNDOTBS1

         1 USERS

When I alter the parameter EXCLUDE_SEED_CDB_VIEW to false then CDB_DATA_FILES also shows the seed files.

SQL> SELECT CON_ID, TABLESPACE_NAME FROM CDB_DATA_FILES;

    CON_ID TABLESPACE_NAME

---------- ------------------------------

         1 SYSTEM

         1 SYSAUX

         1 UNDOTBS1

         1 USERS

         2 SYSTEM

         2 SYSAUX

Thanks

1 - 14
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 26 2007
Added on Aug 29 2007
8 comments
4,747 views