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?
When attempting to add a column with a NOT NULL constraint to an existing table that is not empty, it results in an error. The table EMP in APP_ROOT likely does not contain data, allowing the column to be added without issue. However, when attempting to synchronize the APP_PDB1 with APP_ROOT, the table EMP in APP_PDB1 is not empty. During the SYNC operation, Oracle tries to add the column SAL with the NOT NULL constraint to APP_PDB1.EMP. Since this table is not empty in APP_PDB1, it produces an error, specifically ORA-01758. Therefore, the correct outcome is that the operation will return an error because EMP is not empty in APP_PDB1.
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.. :(
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.
A in my opinion
I'm not sure of E
A is corect
E. Is the root issue
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
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:
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?
for explanation of E, Column is empty not Table. So for the reason you explan i think E
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):
(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.