58.View the Exhibit and examine the parameters.

58.View the Exhibit and examine the parameters.

User A executes the following command to update the TRANS table:
SQL> UPDATE B.trans SET tr_amt=tr_amt+500 WHERE c_code='C005';
Before user A issues a COMMIT or ROLLBACK command, user B executes the following command on
the TRANS table:
SQL> ALTER TABLE trans MODIFY (tr_type VARCHAR2(3));
What would happen in this scenario?
A.The ALTER TABLE command modifies the column successfully.
B.The DDL operation gets higher priority and transaction for user A is rolled back.
C.The ALTER TABLE command waits indefinitely until user A ends the transaction.
D.The ALTER TABLE command fails after waiting for 60 seconds due to the resource being busy.
答案：D
解析：
DDL_LOCK_TIMEOUT:指定ddl语句需要等待一个dml锁的时间，默认为0表示不等待，最大的值为1,000,000秒，表示的时一直等待
DISTRIBUTED_LOCK_TIMEOUT:指定分布式事务需要等待锁定资源的时间,单位是秒
DML_LOCKS:指定了dml锁的最大数量，这个值应该等于所有用户在表上锁的总数
GC_FILES_TO_LOCKS:rac的参数，It controls the mapping of pre-release 9.0.1 parallel cache management (PCM) locks to datafiles.
LOCK_NAME_SPACE:过时了，仅仅是为了向后兼容，它的作用是为生成锁名称的分布式锁管理指定表空间
LOCK_SGA:设置为true的时候，整个sga被锁定到物理内存中，避免将sga中某些部分分配到虚拟内存中默认为false，如果it平台不支持该参数，那么它将被忽略
--测试一下看看
一、DDL_LOCK_TIMEOUT值为默认0
SQL> show parameter ddl_lock_timeout
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
在一个crt窗口执行update sales操作
SQL>  update sh.sales set quantity_sold=10 where prod_id=13;
6002 rows updated.
在另外一个crt窗口中执行alter操作
SQL> ALTER TABLE sh.sales MODIFY (tr_type VARCHAR2(3));
ALTER TABLE sh.sales MODIFY (tr_type VARCHAR2(3))
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> 
--这里马上报错了
二、如果更新没有数据，是否ddl还会报错
一个窗口执行
SQL> update sh.sales set quantity_sold=10 where prod_id=1;
0 rows updated.
--一个窗口执行
SQL> ALTER TABLE sh.sales MODIFY (tr_type VARCHAR2(3));
ALTER TABLE sh.sales MODIFY (tr_type VARCHAR2(3))
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> 
--所以与数据没有关系
三、将DDL_LOCK_TIMEOUT修改为60
SQL> alter system set DDL_LOCK_TIMEOUT=60;
System altered.


SQL> update sh.sales set quantity_sold=10 where prod_id=13;
6002 rows updated.
SQL> 


SQL> ALTER TABLE sh.sales MODIFY (tr_type VARCHAR2(3));
ALTER TABLE sh.sales MODIFY (tr_type VARCHAR2(3))
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
--过了相应的时间提示错误