oracle维护手册(八)（oracle运维手册）

五、对象管理

1、显示一个表的结构

SQL> desc table_name;

SQL> describe table_name;

2、查看对象及状态

查看当前用户的函数与存储过程及状态：

SQL> select object_name,status from user_objectswhere object_type='FUNCTION';

SQL> select object_name,status from user_objectswhere object_type='PROCEDURE';

object_type可以是：

SEQUENCE

PROCEDURE

LOB

PACKAGE

PACKAGE BODY

TRIGGER

INDEX

TABLE

VIEW

FUNCTION

JAVA CLASS

JAVA SOURCE

TYPE

...

3、查看对象的源代码

可用于查看存储过程、函数、包等源代码

SQL> select text from all_source where owner=userand name=upper('&plsql_name');

4、查看当前用户所有的表

SQL> select * from user_tables;

SQL> select table_name from user_tables;

tabs是user_tables的同义词，所以可以直接使用tabs更加简洁。

5、编译对象

SQL> alter function FUNCTION_NAME compile;

SQL> alter procedure PROCEDURE_NAME compile;

SQL> alter trigger TRIGGER_NAME compile;

...

6、锁对象

1）当前所有被锁的对象信息

SQL> select * from v$locked_object;

2）查询出被锁对象、锁的模式及所属用户

SQL> select b.owner, b.object_name, a.session_id,a.locked_mode

from v$locked_object a, dba_objects b

where b.object_id = a.object_id;

3）被锁对象的会话信息

SQL> select b.username, b.sid, b.serial#,logon_time

from v$locked_object a, v$session b

where a.session_id = b.sid

order by b.logon_time;

有些锁可能等待很久都没释放，此时可能需要强行关闭：

语法：alter system kill session 'sid,serial#';

SQL> alter system kill session '104,1894';

4）查询发生锁对应的语句

a）

SQL> select sql_text

from v$sql

where hash_value in

(select sql_hash_value

from v$session

where sid in (selectsession_id from v$locked_object));

b）

SQL> select distinct sql_text

from v$sql

where hash_value in

(select sql_hash_value

from v$session

where sid in (selectsession_id from v$locked_object));

c）

SQL> select sql_text,count(*)

from v$sql

where hash_value in

(select sql_hash_value

from v$session

where sid in (selectsession_id from v$locked_object)) group by sql_text;