Exam 1z0-083 All Questions

View all questions & answers for the 1z0-083 exam

Exam 1z0-083 topic 1 question 161 discussion

Actual exam question from Oracle's 1z0-083

Question #: 161
Topic #: 1

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

Show Suggested Answer

Suggested Answer: A 🗳️

by ilputto at Jan. 6, 2023, 1:45 p.m.

Comments

Submit Cancel

eleeitor

Highly Voted 1 year, 11 months ago

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.. :(

upvoted 5 times

piontk

1 year, 10 months ago

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.

upvoted 1 times

...

df404e2

Most Recent 7 months, 2 weeks ago

Selected Answer: E

DEFAULT_SHARING is only for new objects, so the table is METADATA linked. No data exists in this table for application root, so the ALTER TABLE succeeds. Conclusion is that the rows are inserted via the PDB, hence when SYNCED the error occurs. This is indirectly caused by the table in application root being empty, hence in application pdb not empty. So E.

upvoted 1 times

...

acesonly

1 year, 1 month ago

Selected Answer: A

TL;DR: The A answer should've read: "It will return an error because EMP is not empty in APP_PDB1". The explanation for the error is: when a SYNC starts, it will attempt adding a column to the APP_PDB1.EMP table, however, adding a NOT NULL-constrained column to a non-empty table is errored on every, even a plain, non-APPLICATION PDB-hosted table (try it for yourself). Explanation: there's an error in the offered A answer, to which I arrived by eliminating all the other answers as follows (after fiddling with the assignment):

upvoted 1 times

acesonly

1 year, 1 month ago

(continuing on the message above) Explanation: there's an error in the offered A answer, to which I arrived by eliminating all the other answers as follows (after fiddling with the assignment): (Not B): The SYNC was applied only when I emptied the EMP table in both the APP_ROOT and APP_PDB1 and rerun SYNC. (Not C): The SAL column wasn't successfully added under the conditions given in the assignment. (Not D): The SAL column wasn't successfully added under the conditions given in the assignment. (Not E): For experimental purposes, I modified the assignment by inserting and committing a row in the EMP table in the APP_ROOT.EMP table. After starting an APPLICATION UPGRADE, I tried adding a column to the table, but got the same error message: "ORA-01758: table must be empty to add mandatory (NOT NULL) column" - without even finishing the upgrade.

upvoted 1 times

...

antonica

1 year, 3 months ago

E. Is the root issue

upvoted 2 times

antonica

1 year, 3 months ago

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

upvoted 1 times

...

dancymonkey

1 year, 5 months ago

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:

upvoted 2 times

...

_gio_

2 years ago

Selected Answer: E

I'm not sure of E

upvoted 3 times

...

vkra

2 years, 4 months ago

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?

upvoted 2 times