Oracle数据库常用操作手册

1、ORACLE 数据库常用操作手册SHELL篇RAC维护命令RAC启动与停止启动单一节点数据库srvctl start nodeapps -n srvctl start asm -n srvctl start instance -d -i emctl start dbconsole停止单一节点数据库emctl stop dbconsolesrvctl stop instance -d -i srvctl stop asm -n srvctl stop nodeapps -n 开启关闭监听lsnrctl stoplsnrctl start或srvctl stop listener -n srvct

2、l start listener -n 停止启动服务srvctl stop service -d gzedusrvctl start service -d gzedu停止启动全局数据库srvctl stop database -d basesrvctl start database -d base关闭整个群集数据库root权限crs_stop -all 或crsctl stop crs启动整个群集数据库root权限crs_start all或crsctl start crs停止群集守护进程/etc/init.d/init.crs stop在Oracle环境中,当RAC不同节点间的时间差超过30

3、秒时,会导致oracle数据库反复重启或者状态异常.重新启动整个数据库群集Usage: crs_stop resource_name . -f -q attrib=value . crs_stop -c cluster_member . -q attrib=value . crs_stop -all -qcrs_stop -all crs_start -allcrs_stop ora.oradb3.gsdRAC配置修改修改VIP地址oifcfg getif -globaloifcfg setif -global eth0/:publicoifcfg iflist/etc/init.d/init

4、.crs stopsrvctl modify nodeapps -n rac1 -A 91/eth0RAC数据库检查查看群集数据库状态srvctl status database -d crs_stat -t检查单一节点状态srvctl status nodeapps -n 所有实例和服务的状态srvctl status database -d racdb单个实例的状态srvctl status instance -d racdb -i racdb2在数据库全局命名服务的状态srvctl status service -d racdb -s racdb_taf特定节点上节点应用程序的状态srv

5、ctl status nodeapps -n linux1ASM 实例的状态srvctl status asm -n linux1列出配置的所有数据库srvctl config database显示 RAC 数据库的配置srvctl config database -d racdb显示指定集群数据库的所有服务srvctl config service -d racdb显示节点应用程序的配置 （VIP、GSD、ONS、监听器）srvctl config nodeapps -n linux1 -a -g -s -l显示 ASM 实例的配置srvctl config asm -n linux1查询v

6、ote的路径磁盘信息crsctl query css votedisk查看节点状态srvctl status nodeapps -n oradb3srvctl status instance -d gzedu -i gzedu1检查crs健康情况crsctl check crscrsctl start resourcesgsdctl stopgsdctl status数据库维护命令数据库的启动与停止正常启动单机数据库su - oraclelsnrctl startsqlplus /nologconnect /as sysdbastartupsqlplus /nolog connect /as

7、sysdba startup nomount alter database mount alter database open startup的几个选项 nomount只启动实例，不安装和打开数据库 mount启动实例、安装数据库但不打开数据库，此参数用于修改数据库的运行模式或进行数据库恢复，如更改数据库的为归档模式:alter database archivelog; alter database open; archive log listread only只读属性 read write读写属性 open打开数据库 restrict表示数据库以受限制方式打开 pfile选项，用于参数文件破

8、坏，或者另外指定参数文件，如：startup pfile=/u01/user/proddb.ora force以强制方式启动数据库，可以用于重新启动数据库 以上参数可以联合使用启动管理控制台$emctl start dbconsole启动iSQLPLUS$isqlplusctl start停止单机数据库sqlplus /nologconnect /as sysdbashutdown immediateshutdown有四个参数 normal 需要等待所有的用户断开连接immediate 等待用户完成当前的语句 transactional 等待用户完成当前的事务 abort 不做任何等待，直接关

9、闭数据数据的导入与导出数据导入imp fromuser=tutor1 touser=tutor1 file=gzdec-tutor1-060221.dmp;imp open_teacher/teacher987 file=db4_TJXJY_070723.dmp fromuser=tjxjy touser=open_teacher tables=(bbs,rbbs)imp open_netcsou/netcsou987 file=db4_netcsou_070713.dmp fromuser=netcsou touser=open_netcsou grants=n数据泵导入impdp dire

10、ctory=backup_dir dumpfile=base-base_msgz-091111.dmp schemas=base_msgz include=TABLE:in(RAC_USER_ROLE) logfile=base_msgz_export.logimpdp tt_gd_lms/888888 directory=expdp_dir dumpfile=gzedu-gd_lms-090810.dmp schemas=gd_lms remap_schema=gd_lms:tt_gd_lms remap_tablespace=gd_lms:tt_gd_lmsimpdp directory=

11、backup_dir dumpfile=base-base_msgz-091111.dmp schemas=base_msgz parallel=4 table_exists_action=replace tables=RAC_USER_ROLE impdp directory=backup_dir dumpfile=base-base_msgz-091111.dmp schemas=base_msgz include=TABLE:in(RAC_USER_ROLE) logfile=base_msgz_export.logimpdp fvdb/fvdb directory=admin_dir

12、dumpfile=FVDB.DMP schemas=fvdb remap_tablespace=fvdb:ts_fvdb logfile=fvdb_export.log数据导出备份整个数据库exp system/manager inctype=complete file=gzdec-tutorl-070428.dmp增量型”增量导出exp system/manager inctype=incremental file=gzdec-tutorl-070428.dmp累计型”增量导出exp system/manager inctype=cumulative file=gzdec-tutorl-07

13、0428.dmp 导出一个完整数据库exp system/manager file=bible_db log=dible_db full=y导出数据库定义而不导出数据exp system/manager file=bible_db log=dible_db full=y rows=n导出一个或一组指定用户所属的全部表、索引和其他对象exp system/manager file=seapark log=seapark owner=seaparkexp system/manager file=seapark log=seapark owner=(seapark,amy,amyc,harold)e

14、xp system/fitness1388 file=gzedu_open-bayi_new.dmp owner=open_bayi数据泵导出在命令行中使用要注意使用转意字符因为 ( )会被认为特殊字符expdp directory=test dumpfile=sfca09.dump logfile= sfca09.log schemas=sfcdata include=table:like SFCA%expdp directory=erp schemas=wfl content=data_only exclude=table:IN(WFS_TB_FCSMAIL,WFS_TB_WFMAS,WF

15、S_TB_WFDTL) dumpfile=wfl16.dump logfile=wfl16.loginclude/exclude 例子：include=table:”in(DB,TS)”或者include=table:”like %E%”或者include=function,package,procedure,table:”=EMP”或者exclude=SEQUENCE,TABLE:”IN (EMP,DEPT)”expdp schemas=base_digischool directory=backup_dir dumpfile=base-base_digischool-20091118.dm

16、p parallel=4 logfile=base_digischool_export.logLinux下常命令查杀所有oracle进程ps -ef|grep ora_|grep -v grep|awk print $2 |xargs kill -9显示Oracle 的Unix 进程 ps -ef|grep ora_|grep -v grep 查看消耗CPU 时间最长的进程： ps -ef|grep oracle|sort +6|tail按照此列排序来获得当前高CPU 占用的用户。例如： ps auxgw|sort +2|tail 显示Oracle 的活动连接用户数量 ps -ef|grep

17、$ORACLE_SID|grep -v grep|grep -v ora_|wc -l AIX下常用命令显示aix 中的服务器设备信息 在AIX 中也可以使用lsdev 命令来查看硬件设备信息。 lsdev -C 在AIX 下查看系统核心参数 我们需要使用lsattr 命令。例如： lsattr -El sys0 aix 下显示内存大小： /usr/sbin/lsattr -E -l sys0 -a realmem 使用Aix 的svmon 工具 IBM AIX 提供一个叫做 svmon 的工具。这个工具显示服务器上的所有内存的使用情 况，包括页交换和内存使用。例如： svmonsvmon -

18、P pid显示文件被进程谁占用fuser -u /dev/lv_index2_2gAIX下查看逻辑卷的大小dbfsize /dev/rlv_gzdec01_2gLinux 下看内核限制参数ulimit -aAIX内存优化参数和命令集查看内存参数vmo -a |grep strict_maxclient strict_maxclient = 1vmo -a |grep lru_file_repage lru_file_repage = 1vmo -a |grep minperm% minperm% = 10vmo -a |grep maxclient% maxclient% = 20vmo -a

19、 |grep maxperm% maxperm% = 20vmo -a |grep minfree minfree = 960vmo -a |grep maxfree maxfree = 1088vmo -a |grep strict_maxclientvmo -a |grep lru_file_repagevmo -a |grep minperm%vmo -a |grep maxclient%vmo -a |grep maxperm%vmo -a |grep minfreevmo -a |grep maxfreevmo -a |grep strict_maxpermoradb1上设置的优化值

20、vmo -p -o v_pinshm=1vmo -p -o maxpin%=75oradb2上设置的优化值vmo -p -o v_pinshm=1vmo -p -o maxpin%=75vmo -p -o minperm%=3vmo -p -o maxclient%=8vmo -p -o maxperm%=8vmo -p -o minperm%=5vmo -p -o maxperm%=90vmo -p -o maxclient%=90vmo -p -o minperm%=10 -o maxperm%=20 -o maxclient%=20 -o strict_maxperm=1 -o stri

21、ct_maxclient=1应用的需求，因此需要进行一些调整。AIX操作系统中，一般将内存的使用分成两个部分，一个部分用于应用程序运行使用，称为计算内存（Computational），另一部分用于文件缓存，称为文件缓存（Non-Comp），AIX操作系统通过 minperm%，maxperm%, maxclient%, strict_maxclient, lru_file_repage，minfree, maxfree, 等参数控制系统的内存使用.在SAP应用环境下建议将以上参数设置为： vmo -p -o strict_maxclient=0 vmo -p -o lru_file_repag

22、e=0 vmo -p -o minperm%=3 vmo -p -o maxclient%=8 vmo -p -o maxperm%=8 vmo -p -o minfree=CPU数量*120 vmo -p -o maxfree=CPU数量*128如果CPU数量是12，则minfree=1440, maxfree=1536 使用AIX 并行I/O (Concurrent I/O) 来提高数据库的性能numperm 和numclient是一个比较好的系统信息,表示的是当前内存中基于文件的page占的百分比. 从中可以判断当前可能的page replacement的机制. 如果numperm和nu

23、mclient在minperm和maxperm/maxclient之间, 参考lru_file_repage, 如果lru_file_repage=0, 将先replace文件类型的page, 如果lru_file_repage=1, 系统会平衡计算型和文件型page的repage情况来决定监控AIX内存使用情况列出内存占用率排名前15名的进程和相关信息svmon -Pt15 | perl -e while()print if($.=2|$&!$s+);$.=0 if(/-+$/)显示内存使用信息10行vmstat 1 10avm Active virtual pagesavm定义为the n

24、umber of virtual-memory working segment pages that have actually been touched. 此值可能会比实际物理内存的frame要大,因为一些active virtual memory可能会被写入到paging space中. 表示的是当前进程使用的stack,变量,共享内存段等类型的内存,但是不包括进程可能打开的文件所占用的内存.fre Size of the free list fre物理内存实际剩余的page数目pi Pages paged in from paging spacepo Pages paged out to

25、 paging space 正常情况下pi和po不应该持续为非0值;fr Pages freed (page replacement).sr Pages scanned by replacement algorithm 正常情况下fr和sr基本一致;r Average number of runnable kernel threads over the sampling interval. Runnable refers to threads that are ready but waiting to run and to those threads already running. 正常情况

26、下一般r5b Average number of kernel threads placed in the VMM wait queue (awaiting resource, awaiting input/output) over the sampling interval.wa CPU idle time during which the system had outstanding disk/NFS I/O request(s). See detailed description above. b和wa正常的情况下都不大,高的wa(I/O wait)和高的b(在队列中等待的线程数目)有可

27、能是paging in和out导致的.工程经验avm可以作为长期监控系统内存使用率的趋势分析,如果你有监控软件, 长时间监控avm可以给你很好的内存使用的趋势. 虽然它不代表实际系统用了多少内存,但是作为趋势判断还是非常有效的. 判断内存是否缺少内存的一个工程依据: fre少于minfree并且有持续的page in和page out出现. 其它的值:fr,sr,r,b,wa可以作为一些参考的值.vmstat -s作为page ins, page outs, paging space page ins和paging space page outs,应该不会有大量的增长vmstat -vnump

28、erm 和numclient是一个比较好的系统信息,表示的是当前内存中基于文件的page占的百分比. 从中可以判断当前可能的page replacement的机制. 如果numperm和numclient在minperm和maxperm/maxclient之间,参考lru_file_repage, 如果lru_file_repage=0, 将先replace文件类型的page, 如果lru_file_repage=1, 系统会平衡计算型和文件型page的repage情况来决定svmon -GIn use work表示的是正在使用的work类型的内存; in use pers表示的是正在使用的p

29、ersistent类型的内存(JFS类型); in use clnt表示的正在使用的client类型的内存(包括remote文件系统和Enhanced JFS类型)查看用户连接的负载均衡状态select instance_name,host_name,NULL AS failover_type,NULL AS failover_method,NULL AS failed_over FROM v$instance UNION SELECT NULL,NULL,failover_type,failover_method,failed_over FROM v$session WHERE userna

30、me=BASE_TUTOR;select instance_name,host_name,NULL AS failover_type,NULL AS failover_method,NULL AS failed_over FROM v$instance UNION SELECT NULL,NULL,failover_type,failover_method,failed_over FROM v$session WHERE username=SYSTEM;AIX 下mount NSF文件nfso -o nfs_use_reserved_ports=1AIX下使用nmon 每10分抓一次资源使用情

31、况./nmon_aix53 -f -s 600 -c 10000SQL语句篇基础操作用户操作建立数据库用户create user tutor1 identified by tutor18756default tablespace developtemporary tablespace temp;grant dba to tutor1;grant create session to tutor1;alter user tutor1 quota 300M on develop;建立数据库用户(TOAD)CREATE USER FVDB IDENTIFIED BY FVDB DEFAULT TABL

32、ESPACE FVDB TEMPORARY TABLESPACE TEMP PROFILE DEFAULT QUOTA UNLIMITED ON FVDB;GRANT CREATE SESSION TO FVDB WITH ADMIN OPTION;GRANT DBA TO FVDB WITH ADMIN OPTION;ALTER USER FVDB DEFAULT ROLE DBA;限制用户会话数create profile limit_2 limit sessions_per_user 2;alter system set resource_limit=true;ALTER USER xx

33、x_user profile limit_2;ALTER USER xxx_user profile default; 取消解锁用户alter user system account unlock;修改用户密码和显示用户信息alter user sys identified by 密码;alter user system identified by 密码;select username,password from dba_users where username=SYS;orapwd file=/oracle/10gr2/db/dbs/orapwee2 password= entries=10

34、 force=y;orapwd file=/dev/rlv_pwdfile_240m password= entries=10 force=y删除用户drop user tutor1 cascade;表空间操作建表空间CREATE TABLESPACE FVDB DATAFILE D:ORADATAFVDB04.DBF SIZE 4096M AUTOEXTEND OFF, D:ORADATAFVDB03.DBF SIZE 4096M AUTOEXTEND OFF, D:ORADATAFVDB02.DBF SIZE 4096M AUTOEXTEND OFF, D:ORADATAFVDB01.DB

35、F SIZE 4096M AUTOEXTEND OFFLOGGINGEXTENT MANAGEMENT LOCAL AUTOALLOCATEBLOCKSIZE 8KSEGMENT SPACE MANAGEMENT AUTOFLASHBACK ON;create user scott identified by tiger default tablespace users;grant dba, resource, connect to scott;alter database datafile +RACDB_DATA1/racdb/datafile/users.264.570913355 res

36、ize 1024m;alter tablespace users add datafile +RACDB_DATA1 size 1024m autoextend off;create tablespace indx datafile +RACDB_DATA1 size 1024m autoextend on next 50m maxsize unlimited extent management local autoallocate segment space management auto;alter database datafile +RACDB_DATA1/racdb/datafile

37、/system.262.570913215 resize 800m;alter database datafile +RACDB_DATA1/racdb/datafile/sysaux.260.570913287 resize 500m;alter tablespace undotbs1 add datafile +RACDB_DATA1 size 1024m autoextend on next 50m maxsize 2048m;alter tablespace undotbs2 add datafile +RACDB_DATA1 size 1024m autoextend on next

38、 50m maxsize 2048m;alter database tempfile +RACDB_DATA1/racdb/tempfile/temp.258.570913303 resize 1024m;select tablespace_name, file_namefrom dba_data_filesunionselect tablespace_name, file_namefrom dba_temp_files;把用户从一个表空间导入到另一表空间.回收用户unlimited权限revoke unlimited tablespace from childpalace_demo;alte

39、r user childpalace_demo quota 0 on develop;alter user childpalace_demo quota 0 on users;alter user childpalace_demo quota unlimited on childpalace;如何將表移動表空間?ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;批量修改表的表空间select alter table | table_name | move tablespace filmmusicdata; from user_tables where ta

40、blespace_name=USERS; 如何將索引移動表空間?ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;批量修改索引表空间select table_name,tablespace_name from user_tables ;select index_name,tablespace_name from user_indexes ;select alter table | table_name | move tablespace netcs; from user_tables where tablespace_name=

41、USERS;select alter index | index_name | rebuild tablespace netcs; from user_indexes where tablespace_name=USERS;#REBUILD LOG类型索引如果有 LOB 类型的索引（形如：SYS_IL0000033021C00010$），REBUILD时也会报错。此类索引是创建表时自动生成的，所以需要重建表和其它的索引。根据索引找到相应表名字select table_name from user_indexes where index_name=SYS_IL0000082969C00009$;

42、 查出此表上的所有索引select index_name from user_indexes where table_name=CMS_INFO_HIS;重命名表名字rename EI_T_TRANSFER to EI_T_TRANSFER_OLD; 新建表create table EI_T_TRANSFER as select * from EI_T_TRANSFER_OLD;此步以后，LOB索引已经自动创建了，后面就不用再操作了重命名表上的索引（第二步查出的除LOB外的所有索引）alter index PK_EI_T_TRANSFER rename to PK_EI_T_TRANSFER_

43、OLD;alter index IDX_T_TRANSFER_USERID rename to IDX_T_TRANSFER_USERID_OLD; 查出索引的创建语句select dbms_metadata.get_ddl(INDEX,PK_EI_T_TRANSFER_OLD) from dual;select dbms_metadata.get_ddl(INDEX,IDX_T_TRANSFER_USERID_OLD) from dual;#参数配置操作增加服务名show parameter servicealter system set service_names =, racdb_taf

44、 scope=both;在当前实例中将 cluster_database 设置为 FALSE 来禁用集群实例参数alter system set cluster_database=false scope=spfile sid=racdb1;archive log list监控查询操作集群中所有正在运行的实例SELECT inst_id , instance_number inst_no , instance_name inst_name , parallel , status , database_status db_status , active_state state , host_nam

45、e hostFROM gv$instanceORDER BY inst_id;位于磁盘组中的所有数据文件select name from v$datafileunionselect member from v$logfileunionselect name from v$controlfileunionselect name from v$tempfile;属于“RACDB_DATA1”磁盘组的所有 ASM 磁盘SELECT pathFROM v$asm_diskWHERE group_number IN (select group_number from v$asm_diskgroup wh

46、ere name = RACDB_DATA1);运行缓冲中的 SQL 语句： COLUMN instance_name FORMAT a13COLUMN host_name FORMAT a9COLUMN failover_method FORMAT a15COLUMN failed_over FORMAT a11SELECT instance_name , host_name , NULL AS failover_type , NULL AS failover_method , NULL AS failed_overFROM v$instanceUNIONSELECT NULL , NULL

47、 , failover_type , failover_method , failed_overFROM v$sessionWHERE username = SYSTEM;识别出5个最消耗cpu时间的SQL查询(适用于9i以上版本)select * from(select * from v$sql order by cpu_time desc) qwhere rownum=5;诊断系统中磁盘上已经完成的排序数量育内存中的排序数量的百分比（一般小于0.05)，如果排序率大于阀值，那么应当增加sort_area_sizeselect disk.value disk,mem.value memory

48、,round(disk.value/mem.value)*100,2) sort_ratiofrom v$sysstat disk, v$sysstat memwhere = sorts (disk) and = sorts (memory)每次查询最多读盘次数的10个SQL查询select * from(select round(disk_reads/decode(executions,0,-99999999,executions) disk_reads_per_exec, v$sql.* from v$sqlorder by disk_reads_per_exec desc)Qwhere

49、rownum=10;数据文件的读写统计select fs.file#,fs.phyrds,fs.phywrts from v$filestat fs,v$datafile df where fs.file#=df.file#; SELECT name,phyrds,phywrts FROM v$datafile df,v$filestat fsWHERE df.file# =fs.file#; 临时文件的读写统计select file_name, fs.phyrds reads, fs.phywrts writes from v$datafile df,v$filestat fswhere d

50、f.file#=fs.file#;根据SID查找语名SELECT T2.SID, T2.SERIAL#, T3.SQL_TEXT FROM V$SESSION T2, V$SQLTEXT T3 WHERE T2.SID IN (168) AND T2.SQL_ADDRESS = T3.ADDRESS;根据FILE_ID & BLOCK_ID获得对象名称SELECT /*+ RULE*/ owner, segment_name, segment_type FROM dba_extents WHERE file_id = &file_id AND &block_id BETWEEN block_i

51、d AND block_id + blocks - 1;根据操作系统PID,查询SESSION信息SELECT a.sid, a.serial#, b.spid, a.terminal, a.machine, gram, a.osuser FROM v$session a, v$process b WHERE a.paddr = b.addr AND b.spid = &SPID;根据SESSION SID,查询操作系统PIDSELECT a.sid, a.serial#, b.spid, a.terminal, a.machine, gram, a.osuser FROM v$session

52、 a, v$process b WHERE a.paddr = b.addr AND a.sid = &SID;查询用户正在执行的SQLSELECT sql_text FROM v$sqltext WHERE hash_value = (SELECT sql_hash_value FROM v$session WHERE sid = &sid) ORDER BY piece;查询当前的系统等待事件SELECT * FROM v$session_wait WHERE event NOT LIKE %SQL*Net% AND event NOT LIKE %rdbms% AND event NOT

53、 LIKE %timer% AND event NOT LIKE %jobq% ORDER BY event, seconds_in_wait;查询详细的当前系统等待事件SELECT s.sid, s.username, w.seq#, w.event, w.p1text, w.p1, w.p2text, w.p2, w.p3text, w.p3, w.seconds_in_wait, w.state, s.logon_time, s.osuser, gram FROM v$session s, v$session_wait w WHERE s.sid = w.sid AND w.event

54、NOT LIKE %SQL*Net% AND w.event NOT LIKE %rdbms% AND w.event NOT LIKE %timer% AND w.event NOT LIKE %jobq% ORDER BY w.event, w.seconds_in_wait;查询等待db file sequential/scattered read的Session正在执行的SQLSELECT s.sid, s.username, t.hash_value, t.piece, t.sql_text FROM v$session s, v$session_wait w, v$sqltext

55、t WHERE s.sid = w.sid AND s.sql_hash_value = t.hash_value AND w.event IN (db file sequential read, db file scattered read) ORDER BY s.sid, t.piece;查询等待db file sequential/scattered read对应的数据库对象SELECT /*+ RULE*/ s.sid, s.username, w.seq#, w.event, d.segment_type, d.owner | . | d.segment_name AS segmen

56、t_name, w.seconds_in_wait, w.state, s.logon_time FROM v$session s, v$session_wait w, dba_extents d WHERE s.sid = w.sid AND d.file_id = w.p1 AND w.p2 BETWEEN d.block_id AND d.block_id + d.blocks - 1 AND w.event IN (db file sequential read, db file scattered read) ORDER BY w.event, segment_name;查询导致LO

57、CK的SID,SPID,LOCKED_OBJECT,LOCK_TYPE等信息SELECT /*+ RULE*/ l.sid, p.spid, s.username,s.logon_time, s.osuser, gram, l.type, CASE l.TYPE WHEN TM THEN O.object_name WHEN TX THEN END as OBJECT_NAME, DECODE (l.lmode, 0, 0=NONE, 1, 1=NULL, 2, 2=RS, 3, 3=RX, 4, 4=S, 5, 5=SRX, 6, 6=X) lmode, CASE l.request WHE

58、N 0 THEN ELSE BLOCKED BY | l.id2 END as BLOCKED, CASE l.block WHEN 0 THEN ELSE l.id2 | IS BLOCKING END as BLOCKING, l.request, l.ctime FROM v$lock l, v$session s, dba_objects o, v$process p WHERE l.type in (TX, TM) AND s.paddr = p.addr AND l.sid = s.sid AND l.id1 = o.object_id(+) ORDER BY s.username

59、, l.sid, l.ctime;查询导致DDL LOCK的详细信息SELECT s.sid, p.spid, s.username, a.owner | . | a.NAME AS OBJECT_NAME, a.TYPE, a.mode_held, a.mode_requested, s.osuser, s.logon_time, gram FROM dba_ddl_locks a, v$session s, v$process p WHERE s.sid = a.session_id AND s.paddr = p.addr AND (a.mode_held = Exclusive OR

60、a.mode_requested = Exclusive) ORDER BY s.USERNAME, a.NAME;查询事务使用的回滚段SELECT s.username, s.sid, s.serial#, t.ubafil UBA filenum, t.ubablk UBA Block number, t.used_ublk Number of undo Blocks Used, t.start_time, t.status, t.start_scnb, t.xidusn rollid, rollname FROM v$session s, v$transaction t, v$rolln