1Z0-052 · Question #133
You created a profile APP_USER and assigned it to the users. After a month, you decide to drop the profile. Some user sessions are currently connected to the database instance and are using the…
The correct answer is A. The command produces an error. Dropping a profile that is still assigned to users requires the CASCADE option; omitting it causes Oracle to raise an error rather than complete the drop.
Question
You created a profile APP_USER and assigned it to the users. After a month, you decide to drop the profile. Some user sessions are currently connected to the database instance and are using the APP_USER profile. This command is used to drop the profile:
SQL> DROP PROFILE app_user; Which statement describes the result?
Options
- AThe command produces an error.
- BThe profile is dropped and current user sessions use the DEFAULT profile immediately.
- CThe profile is dropped and only the subsequent user sessions use the DEFAULT profile.
- DThe profile is dropped, the sessions are terminated, and the subsequent user sessions use the
How the community answered
(45 responses)- A78% (35)
- B2% (1)
- C13% (6)
- D7% (3)
Why each option
Dropping a profile that is still assigned to users requires the CASCADE option; omitting it causes Oracle to raise an error rather than complete the drop.
Oracle raises ORA-02382 when you attempt to DROP PROFILE without CASCADE and the profile still has users assigned to it. The presence of active sessions using the profile is secondary - the mere fact that users are assigned prevents the command from executing. To successfully drop the profile, the DBA must use DROP PROFILE app_user CASCADE, which automatically reassigns affected users to the DEFAULT profile.
The drop command fails with an error before any reassignment can occur, so no switch to the DEFAULT profile happens immediately or otherwise.
The command does not succeed at all without CASCADE, so it cannot affect either current or subsequent sessions.
Oracle does not terminate sessions as part of this command; the command simply fails with an error when CASCADE is omitted.
Concept tested: Oracle DROP PROFILE CASCADE clause requirement
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/DROP-PROFILE.html
Topics
Community Discussion
No community discussion yet for this question.