Topic: 1z0-083 topic 1 question 161

Examine this configuration:

1.    CDB1 is a container database.
2.    DEFAULT_SHARING is METADATA.
3.    APP_ROOT is an application root contained in CDB1.
4.    APP_PDB1 is an application PDB contained in APP_ROOT.
5.    COMPANYAPP is an application contained in APP_ROOT.
6.    EMP is a common table created in APP_ROOT and all its application PDBs, created when version 1.0 of COMPANYAPP was installed.

You execute these commands:



What will be the outcome and why?

A.
It will return an error because EMP is not empty in APP_ROOT
B.
It will return an error because the SYNC operation is not allowed when constraints are added to common objects
C.
SAL will be added to APP_PDB1.EMP, with NULLs in columns of existing rows
D.
SAL will be added to APP_PDB1.EMP, with 0 in columns of existing rows
E.
It will return an error because EMP.SAL is empty in APP_ROOT

Re: 1z0-083 topic 1 question 161

E. Is the root issue

Re: 1z0-083 topic 1 question 161

my mistake, i did a lab, and.. this is the error.

SQL> alter pluggable database application app1 sync;

alter pluggable database application app1 sync
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

Re: 1z0-083 topic 1 question 161

I think A but not sure why it's not failed at first place.

CREATE TABLE test_table (
    id NUMBER
);
INSERT INTO test_table (id) VALUES (1);
commit;

ALTER TABLE test_table
ADD new_column VARCHAR2(50) NOT NULL;

ORA-01758: table must be empty to add mandatory (NOT NULL) column
01758. 00000 -  "table must be empty to add mandatory (NOT NULL) column"
*Cause:   
*Action:

Re: 1z0-083 topic 1 question 161

DEFAULT_SHARING only aplies to new common objects you create, so it does nothing here.
The table was metadata-linked, meaning the app pdbs could have their own data.
When we sync the pdb, two things can happen:
1- If table was empty at pdb level, then it wil sync with no errors
2-If table had data at pdb level, it will return:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
One way to fix this would be to ad a DEFAULT value to the column.

There is no 100% correct answer to this question.
A: table in app root is empty(because the constraint was added whitout errors), if it had data it would return an error, but it didn't
B:It is allowed as long as the data doesnt violate constraints being added... it's not that you cant add constraints to a common object
C:No null has been specified for the new column... this doesnt make sense
D:No null has been specified for the new column... this doesnt make sense
E: Table in root is empty, but still doesnt make any sense... it would return an error if table in root had data.

So no correct answers.. sad

Re: 1z0-083 topic 1 question 161

I agree with the point that is no correct answer in the alternatives, but you're wrong on point 2: since the table was created with DEFAULT_SHARING  = METADATA, and the question shows a SELECT at application PDB-level, the table can be empty at application root-level.
I tested in my lab and the result was:

SQL> DESC EMP
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ENO                                                NUMBER
ENAME                                              VARCHAR2(20)

SQL> SELECT * FROM EMP;

       ENO ENAME
---------- --------------------
       100 Alan
       200 Ben

SQL> SHOW CON_NAME

CON_NAME
------------------------------
APP1_PDB1
SQL> alter pluggable database application companyapp sync;

Pluggable database altered.

SQL> SELECT * FROM EMP;

no rows selected
So the synchronization occurs, but it flushes the data on the application PDB.

  • _gio_
  • New member
  • Offline

Re: 1z0-083 topic 1 question 161

I'm not sure of E

  • vkra
  • New member
  • Offline

Re: 1z0-083 topic 1 question 161

General:
If a table has already rows inside and you try to add a column with a NOT NULL constraint, then you get an error ORA-01758. So the common table "emp" in app_root must be empty at the moment for the statement: ALTER TABLE emp ADD (sal NUMBER NOT NULL).

A - FALSE: The SHARING mode is DATA, so why should an error occurs for the sync statement?
B - FALSE: Contraints are allowed:
https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/overview-of-the-multitenant-architecture.html#GUID-D9FDDB3D-E3B8-40FA-9EC5-F88467733C92
C/D - FALSE : makes no sense
E - FALSE: makes no sense, why should it returns an error due to the table is empty?

  • _gio_
  • New member
  • Offline

Re: 1z0-083 topic 1 question 161

for explanation of E, Column is empty not Table. So for the reason you explan i think E

Re: 1z0-083 topic 1 question 161

A is corect

Re: 1z0-083 topic 1 question 161

A in my opinion

Re: 1z0-083 topic 1 question 161

eleeitor wrote:

DEFAULT_SHARING only aplies to new common objects you create, so it does nothing here.
The table was metadata-linked, meaning the app pdbs could have their own data.
When we sync the pdb, two things can happen:
1- If table was empty at pdb level, then it wil sync with no errors
2-If table had data at pdb level, it will return:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
One way to fix this would be to ad a DEFAULT value to the column.

There is no 100% correct answer to this question.
A: table in app root is empty(because the constraint was added whitout errors), if it had data it would return an error, but it didn't
B:It is allowed as long as the data doesnt violate constraints being added... it's not that you cant add constraints to a common object
C:No null has been specified for the new column... this doesnt make sense
D:No null has been specified for the new column... this doesnt make sense
E: Table in root is empty, but still doesnt make any sense... it would return an error if table in root had data.

So no correct answers.. sad