Explore topic-wise InterviewSolutions in .

YES, stored PROCEDURES and FUNCTIONS WITHIN the same schema share the same namespace.

Yes, stored procedures and functions within the same schema share the same namespace.

The SQL STATEMENT is GIVEN as FOLLOWS:

The SQL statement is given as follows:

The SQL STATEMENT is GIVEN as FOLLOWS:

The SQL statement is given as follows:

Yes, it is POSSIBLE to create a PACKAGE body and store it in the DATABASE, ALTHOUGH it would be in an INVALID state and cannot be executed.

Yes, it is possible to create a package body and store it in the database, although it would be in an invalid state and cannot be executed.

It Is not possible to DROP a package specification and leave the package BODY. If the package is DROPPED, the whole package is removed from a DATABASE.

It Is not possible to drop a package specification and leave the package body. If the package is dropped, the whole package is removed from a database.

If the declaration of the proc_2 procedure is not ALTERED in the PACKAGE SPECIFICATION, then the proc_1 procedure is not invalidated. However, if the specification of the procedure is altered, then the DEPENDENT procedure gets the invalid STATUS.

If the declaration of the proc_2 procedure is not altered in the package specification, then the proc_1 procedure is not invalidated. However, if the specification of the procedure is altered, then the dependent procedure gets the invalid status.

DCL STATEMENTS CONTROL the security of the DATABASE. The two statements, GRANT and REVOKE, are used to give permissions to users to access the DIFFERENT schema objects; therefore, enabling security and access control on the database.

DCL statements control the security of the database. The two statements, GRANT and REVOKE, are used to give permissions to users to access the different schema objects; therefore, enabling security and access control on the database.

The USER_TABS_PRIVS data DICTIONARY view gives the LIST of SYSTEM and object privileges on the object ALONG with the owner name of the object.

The USER_TABS_PRIVS data dictionary view gives the list of system and object privileges on the object along with the owner name of the object.

The ADMIN OPTION column in the USER_SYS_PRIVS VIEW is a YES/NO column that INDICATES if the PRIVILEGE was granted WITH ADMIN OPTION.

The ADMIN OPTION column in the USER_SYS_PRIVS view is a YES/NO column that indicates if the privilege was granted WITH ADMIN OPTION.

The STATEMENT is GIVEN as FOLLOWS:

The statement is given as follows:

No ADDITIONAL privilege is required to execute the TRIGGER as the schema B already has ACCESS privilege to the TRIGGERED table, TAB_1.

No additional privilege is required to execute the trigger as the schema B already has access privilege to the triggered table, TAB_1.

The CREATE TRIGGER PRIVILEGE is REQUIRED to create, alter, and DROP TRIGGERS in a SCHEMA.

The CREATE TRIGGER privilege is required to create, alter, and drop triggers in a schema.

The statement is as FOLLOWS:

The privilege is always granted on the whole PACKAGE and not on the individual procedures or functions.

The statement is as follows:

The privilege is always granted on the whole package and not on the individual procedures or functions.

The SESSION_PRIVS data dictionary view shows all the directly and indirectly GRANTED SYSTEM privileges to a SCHEMA.

The SESSION_PRIVS data dictionary view shows all the directly and indirectly granted system privileges to a schema.

The USER_TAB_PRIVS DATA dictionary VIEW is used to view information about all the privileges that have been MADE by and to the CURRENT SCHEMA.

The USER_TAB_PRIVS data dictionary view is used to view information about all the privileges that have been made by and to the current schema.

INDIRECTLY GRANTED privileges are those privileges that have been granted to a role, which in TURN granted to a SCHEMA through a role.

Indirectly granted privileges are those privileges that have been granted to a role, which in turn granted to a schema through a role.

The STATEMENT to REMOVE the CREATE ANY VIEW privilege on the SCHEMA SAMPLE_SCHEMA is as FOLLOWS:

The statement to remove the CREATE ANY VIEW privilege on the schema SAMPLE_SCHEMA is as follows:

The KEYWORD AUTHID CURRENT_USER is used to DEFINE a PROGRAM UNIT as an invoker rights program unit.

The keyword AUTHID CURRENT_USER is used to define a program unit as an invoker rights program unit.

A PROGRAM unit created as an owner rights program unit EXECUTES ACCORDING to the PRIVILEGES that the owner of the program unit have at the time of execution; whereas, a program unit created as an invoker rights program unit executes according to the privileges that the invoker of the program unit have at the time of execution.

A program unit created as an owner rights program unit executes according to the privileges that the owner of the program unit have at the time of execution; whereas, a program unit created as an invoker rights program unit executes according to the privileges that the invoker of the program unit have at the time of execution.

No, a schema A with the CREATE ANY PROCEDURE privilege cannot alter or MODIFY a PACKAGE X in the schema B. The schema must have the ALTER ANY PROCEDURE system privilege for ALTERING or MODIFYING any program unit.

No, a schema A with the CREATE ANY PROCEDURE privilege cannot alter or modify a package X in the schema B. The schema must have the ALTER ANY PROCEDURE system privilege for altering or modifying any program unit.

The CREATE ANY PROCEDURE SYSTEM privilege ALLOWS the schema to create any package, procedure, or function WITHIN its schema or owned by any other schema.

The CREATE ANY PROCEDURE system privilege allows the schema to create any package, procedure, or function within its schema or owned by any other schema.

ROLES are easy to manage and helpful in granting multiple system PRIVILEGES to SCHEMAS. Any privilege can be added to a role or removed from a role. This WOULD AUTOMATICALLY affect all the schemas with that role.

Roles are easy to manage and helpful in granting multiple system privileges to schemas. Any privilege can be added to a role or removed from a role. This would automatically affect all the schemas with that role.

No, the PRIVILEGE on the SCHEMA C will not be revoked AUTOMATICALLY; only the privilege on the schema B is revoked.

No, the privilege on the schema C will not be revoked automatically; only the privilege on the schema B is revoked.

Granting system privilege WITH ADMIN OPTION MEANS that the schema can in turn GRANT the same privilege to any other schema.

Granting system privilege WITH ADMIN OPTION means that the schema can in turn grant the same privilege to any other schema.

The CREATE TABLE privilege GRANTS the schema to create TABLES in its own schema; however, CREATE ANY TABLE grants the privilege to create any table owned by any other schema as WELL.

The CREATE TABLE privilege grants the schema to create tables in its own schema; however, CREATE ANY TABLE grants the privilege to create any table owned by any other schema as well.

The END user MAY not require any SYSTEM PRIVILEGE except the CREATE ANY SESSION, which grants them the privilege to log in as an ORACLE user.

The end user may not require any system privilege except the CREATE ANY SESSION, which grants them the privilege to log in as an Oracle user.

Granting a privilege to PUBLIC MEANS it is granted to all the SCHEMAS in the DATABASE that ALREADY exist or CREATED in the future.

Granting a privilege to PUBLIC means it is granted to all the schemas in the database that already exist or created in the future.

A SYSTEM privilege is the right to PERFORM a PARTICULAR action in the database. Privileges are granted to a schema. They may be granted to a role, which in turn is granted to schemas.

A system privilege is the right to perform a particular action in the database. Privileges are granted to a schema. They may be granted to a role, which in turn is granted to schemas.

Yes, the status of the local PROCEDURE will be set to invalid if the RETURN type of the remote FUNCTION is CHANGED because the SIGNATURE of the function is changing.

Yes, the status of the local procedure will be set to invalid if the RETURN type of the remote function is changed because the signature of the function is changing.

The LAST_DDL_TIME column of the USER_OBJECTS DATA DICTIONARY VIEW records the last time a SQL statement was issued to change the structure of the objects. This column is compared to determine if the referenced program unit has been changed since the dependent unit was last COMPILED.

The LAST_DDL_TIME column of the USER_OBJECTS data dictionary view records the last time a SQL statement was issued to change the structure of the objects. This column is compared to determine if the referenced program unit has been changed since the dependent unit was last compiled.

The INSERT statements should have column NAMES so that if any UNRELATED change to the table is made after the program unit is compiled, AUTOMATIC recompilation should SUCCEED.

The INSERT statements should have column names so that if any unrelated change to the table is made after the program unit is compiled, automatic recompilation should succeed.

Yes, SCHEMA A can CREATE a TRIGGER on the table X of schema B.

Yes, schema A can create a trigger on the table X of schema B.

No, a program unit in the schema 1 cannot reference the database objects in the schema 2, EVEN if the schema 2 has GRANTED necessary privileges to the schema 1 through roles. This is because granting privileges through roles does not work for program units. They can reference other schemp objects only if the necessary PRIVILEGE has been granted directly to the schema.

No, a program unit in the schema 1 cannot reference the database objects in the schema 2, even if the schema 2 has granted necessary privileges to the schema 1 through roles. This is because granting privileges through roles does not work for program units. They can reference other schemp objects only if the necessary privilege has been granted directly to the schema.

A table or SYNONYM can NEVER be INVALIDATED; only DEPENDENT OBJECTS are invalidated.

A table or synonym can never be invalidated; only dependent objects are invalidated.

A few guidelines that would minimize the dependency failures are as follows:

•  Refer to tables indirectly using VIEWS
•  Include column list with INSERT statements
•  QUERY with SELECT * notation
•  Declare records with the %ROWTYPE ATTRIBUTE
•  Declare variables with the %TYPE attribute
•  Use packages for procedures and FUNCTIONS

A few guidelines that would minimize the dependency failures are as follows:

When a private table, which is referenced by a dependent procedure, is DROPPED, the dependent procedure is invalidated. When the procedure is recompiled and a PUBLIC table of the same name EXISTS with all the columns REQUIRED by the procedure, then the procedure will recompile successfully but refer to the public table.

When a private table, which is referenced by a dependent procedure, is dropped, the dependent procedure is invalidated. When the procedure is recompiled and a public table of the same name exists with all the columns required by the procedure, then the procedure will recompile successfully but refer to the public table.

The TIME stamp of the remote procedure and the local procedure will not match and the dependency checking will not be successful. Therefore, while WORKING on remote databases ACROSS time ZONES, signature checking should be used.

The time stamp of the remote procedure and the local procedure will not match and the dependency checking will not be successful. Therefore, while working on remote databases across time zones, signature checking should be used.

Signature checking eliminates the problem of unnecessary recompilation of the program units ACROSS networks. The signature of the REMOTE procedure is stored in the p-code of the local procedure. In signature checking, the signature of the remote procedure is compared and not the time stamp. If the remote program is MODIFIED and recompiled but its signature does not change, then the local procedure can be executed without any runtime error. Therefore, signature checking does not degrade the SYSTEM performance by repetitive compilation of the local procedure.

Signature checking eliminates the problem of unnecessary recompilation of the program units across networks. The signature of the remote procedure is stored in the p-code of the local procedure. In signature checking, the signature of the remote procedure is compared and not the time stamp. If the remote program is modified and recompiled but its signature does not change, then the local procedure can be executed without any runtime error. Therefore, signature checking does not degrade the system performance by repetitive compilation of the local procedure.

The disadvantage of time-stamped checking is that it CAUSES UNNECESSARY recompilation of the dependent objects ACROSS networks when it MAY not be required, leading to the degradation of SYSTEM performance.

The disadvantage of time-stamped checking is that it causes unnecessary recompilation of the dependent objects across networks when it may not be required, leading to the degradation of system performance.

The signature of a procedure refers to:

•  The name of the procedure
•  The datatype of the PARAMETERS
•  The MODES of the parameters
•  The number of parameters
•  The datatype of the return value in case of FUNCTION

The signature of a procedure refers to:

The parameter used to DETERMINE if the mode of CHECKING for the management of remote dependencies is time-stamped or SIGNATURE checking is set in the init.ora parameter file as REMOTE_DEPENDENCIES_MODE = value, where value can be TIMESTAMP or SIGNATURE.

The parameter used to determine if the mode of checking for the management of remote dependencies is time-stamped or signature checking is set in the init.ora parameter file as REMOTE_DEPENDENCIES_MODE = value, where value can be TIMESTAMP or SIGNATURE.

Time-stamped checking is based on the fact that every program unit CARRIES a time STAMP, which is set when it is CREATED or recompiled. Whenever a program unit or a schema object is altered, all its dependent units are invalidated and must be recompiled before they can be executed. The time stamp is recorded in the p-code of the procedure.

The time stamp of the REMOTE procedure is also stored in the p-code of the local procedure when it is COMPILED. A local procedure, which refers to a remote procedure, is invalidated if the remote procedure is recompiled after the local procedure is compiled. When the time stamp of the remote procedure, which is stored in the local procedure, is compared with the time stamp of the recompiled remote procedure, both the time stamps differ. This invalidates the local procedure and returns a runtime error.

Time-stamped checking is based on the fact that every program unit carries a time stamp, which is set when it is created or recompiled. Whenever a program unit or a schema object is altered, all its dependent units are invalidated and must be recompiled before they can be executed. The time stamp is recorded in the p-code of the procedure.

The time stamp of the remote procedure is also stored in the p-code of the local procedure when it is compiled. A local procedure, which refers to a remote procedure, is invalidated if the remote procedure is recompiled after the local procedure is compiled. When the time stamp of the remote procedure, which is stored in the local procedure, is compared with the time stamp of the recompiled remote procedure, both the time stamps differ. This invalidates the local procedure and returns a runtime error.

No, a change to a remote procedure does not automatically change the STATUS of DEPENDENT objects in local DATABASE to INVALID.

No, a change to a remote procedure does not automatically change the status of dependent objects in local database to invalid.

Oracle does not manage dependencies across remote schema objects on DISTRIBUTED NETWORKS other than local-procedure-to- remote procedure dependencies. It only manages dependencies among stored procedures (procedures, functions, and PACKAGES) using time-stamped checking or signature checking. If there is a local VIEW BASED on a remote table, and the remote table definition is altered, the local view is not invalidated.

Oracle does not manage dependencies across remote schema objects on distributed networks other than local-procedure-to- remote procedure dependencies. It only manages dependencies among stored procedures (procedures, functions, and packages) using time-stamped checking or signature checking. If there is a local view based on a remote table, and the remote table definition is altered, the local view is not invalidated.

REFERENCING tables indirectly using views ALLOWS:

• ADDING columns to the table without invalidating DEPENDENT views or dependent PL/SQL objects
• Modifying or DELETING columns of the table that are not referenced by the view without invalidating the dependent objects

Referencing tables indirectly using views allows:

No, a VIEW is not INVALIDATED if a NEW column is added to the table it REFERENCES.

No, a view is not invalidated if a new column is added to the table it references.

When a PACKAGE body is MODIFIED, there is no CHANGE in the DEPENDENT objects.

When a package body is modified, there is no change in the dependent objects.

The SECURITY DEPENDENCIES can be TRACKED USING the USER_TAB_PRIVS data dictionary view.

The security dependencies can be tracked using the USER_TAB_PRIVS data dictionary view.

The IDEPTREE view has the HIERARCHICAL information of the DEPENDENCY tree in a single column called DEPENDENCIES. This column stores a single string of information SHOWING the DEPENDENT objects indented as per the hierarchy.

The IDEPTREE view has the hierarchical information of the dependency tree in a single column called DEPENDENCIES. This column stores a single string of information showing the dependent objects indented as per the hierarchy.

The DEPTREE_FILL PROCEDURE needs to be executed before the IDEPTREE VIEW can be USED.

The DEPTREE_FILL procedure needs to be executed before the IDEPTREE view can be used.