OCP-1Z0-052-V8.02-162题

162. You created a profile APP_USER and assigned it to the users. After a month, you decide to drop theprofile.

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?

A.The command produces an error.

B.The profile is dropped and current user sessions use the DEFAULT profile immediately.

C.The profile is dropped and only the subsequent user sessions use the DEFAULT profile.

D.The profile is dropped, the sessions are terminated, and the subsequent user sessions use the

DEFAULT profile.

Answer: A

答案解析：

参考：http://blog.csdn.net/rlhua/article/details/12277557

在Enterprise Manager 中和sql中，无法删除用户使用的概要文件。但是，如果删除具有CASCADE选项的概要文件（例如在SQL*Plus 中），则会向具有该概要文件的所有用户自动分配DEFAULT概要文件。

题意问，删除一个正在连接数据库的用户，并且这个是用了APP_USER这个概要文件，这个时候要删除这个别的用户正在是用的概要文件，会出现什么问题？

环境测试：

1、首先创建一个用户，并且授予连接数据库的权限。

sys@TEST0924> create user test01 identified by test01 account unlock;

User created.

sys@TEST0924> grant create session,resource to test01;

Grant succeeded.

2、创建一个概要文件

sys@TEST0924> create profile testpro LIMIT PASSWORD_LOCK_TIME 10;

Profile created.

3、将用户和概要文件绑定起来

sys@TEST0924> alter user test01 profile testpro;

User altered.

4、该用户连接到数据库

sys@TEST0924> conn test01/test01

Connected.

test01@TEST0924>

5、另起一个窗口，删除概要文件

[oracle@rtest ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 12 23:15:28 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@TEST0924> drop profile testpro;

drop profile testpro

*

ERROR at line 1:

ORA-02382: profile TESTPRO has users assigned, cannot drop without CASCADE

报错。所以选A正确。

6、如果真想删除这个概要文件，则加上选项CASCADE

sys@TEST0924> drop profile testpro CASCADE;

Profile dropped.