您的当前位置：首页 Oracle 数据库常用操作手册

Oracle 数据库常用操作手册

来源：爱问旅游网

ORACLE 数据库常用操作手册第一章 SHELL篇

1. RAC维护命令 1.1. RAC启动与停顿

启动单一节点数据库

srvctl start nodeapps -n srvctl start asm -n

srvctl start instance -d -i emctl start dbconsole

停顿单一节点数据库 emctl stop dbconsole

srvctl stop instance -d -i srvctl stop asm -n srvctl stop nodeapps -n

开启关闭监听 lsnrctl stop lsnrctl start 或

srvctl stop listener -n srvctl start listener -n

停顿启动效劳

srvctl stop service -d gzedu srvctl start service -d gzedu

停顿启动全局数据库

srvctl stop database -d base srvctl start database -d base

关闭整个群集数据库 root权限 crs_stop -all 或

crsctl stop crs word版

启动整个群集数据库 root权限 crs_start –all 或

crsctl start crs

停顿群集守护进程 /etc/init.d/init.crs stop

在Oracle环境中,当RAC不同节点间的时间差超过30秒时,会导致oracle数据库反复重启或者状态异常.

重新启动整个数据库群集

Usage: crs_stop resource_name [...] [-f] [-q] [\"attrib=value ...\"] crs_stop -c cluster_member [...] [-q] [\"attrib=value ...\"] crs_stop -all [-q]

crs_stop -all crs_start -all

1.2. RAC配置修改

修改VIP地址 oifcfg getif -global

oifcfg iflist

/etc/init.d/init.crs stop

1.3. RAC数据库检查

查看群集数据库状态

srvctl status database -d crs_stat -t

检查单一节点状态

srvctl status nodeapps -n 所有实例和效劳的状态 srvctl status database -d racdb word版

单个实例的状态

srvctl status instance -d racdb -i racdb2

在数据库全局命名效劳的状态

srvctl status service -d racdb -s racdb_taf

特定节点上节点应用程序的状态 srvctl status nodeapps -n linux1

ASM 实例的状态

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

查询vote的路径磁盘信息 crsctl query css votedisk

查看节点状态

srvctl status nodeapps -n oradb3

srvctl status instance -d gzedu -i gzedu1

检查crsXX情况 crsctl check crs

crsctl start resources

gsdctl stop gsdctl status

word版

2. 数据库维护命令 2.1. 数据库的启动与停顿

正常启动单机数据库 su - oracle lsnrctl start sqlplus /nolog connect /as sysdba startup

sqlplus /nolog connect /as sysdba startup nomount alter database mount alter database open startup的几个选项 nomount只启动实例，不安装和翻开数据库 mount启动实例、安装数据库但不翻开数据库，此参数用于修改数据库的运行模式或进展数据库恢复，如更改数据库的为归档模式: alter database archivelog; alter database open; archive log list read only只读属性 read write读写属性 open翻开数据库 restrict表示数据库以受方式翻开 pfile选项，用于参数文件破坏，或者另外指定参数文件，如： startup pfile=/u01/user/proddb.ora force以强制方式启动数据库，可以用于重新启动数据库以上参数可以联合使用启动管理控制台 $emctl start dbconsole 启动iSQLPLUS $isqlplusctl start

停顿单机数据库 sqlplus /nolog connect /as sysdba shutdown immediate shutdown有四个参数 word版

normal 需要等待所有的用户断开连接 immediate 等待用户完成当前的语句 transactional 等待用户完成当前的事务 abort 不做任何等待，直接关闭数据 3. 数据的导入与导出 3.1. 数据导入

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

3.2. 数据泵导入

impdp directory=backup_dir dumpfile=base-base_msgz-091111.dmp schemas=base_msgz include=TABLE:\"in('RAC_USER_ROLE')\" logfile=base_msgz_export.log impdp 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_lms

impdp directory=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.log

impdp fvdb/fvdb directory=admin_dir dumpfile=FVDB.DMP schemas=fvdb remap_tablespace=fvdb:ts_fvdb logfile=fvdb_export.log

3.3. 数据导出

备份整个数据库

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-070428.dmp word版

导出一个完整数据库

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=seapark

exp system/manager file=seapark log=seapark owner=(seapark,amy,amyc,harold) exp system/fitness1388 file=gzedu_open-bayi_new.dmp owner=open_bayi

3.4. 数据泵导出

在命令行中使用要注意使用转意字符因为' \" ( )会被认为特殊字符

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\\',\\'WFS_TB_WFDTL\\'\$\\\" dumpfile=wfl16.dump logfile=wfl16.log include/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-20211118.dmp parallel=4 logfile=base_digischool_export.log

4. Linux下常命令

查杀所有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 占用的用户。例如： word版

ps auxgw|sort +2|tail

显示Oracle 的活动连接用户数量

ps -ef|grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l

5. 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 的工具。这个工具显示效劳器上的所有内存的使用情况，包括页交换和内存使用。例如： svmon

svmon -P pid

显示文件被进程谁占用 fuser -u /dev/lv_index2_2g

AIX下查看逻辑卷的大小 dbfsize /dev/rlv_gzdec01_2g

Linux 下看内核参数 ulimit -a

AIX内存优化参数和命令集

查看内存参数

vmo -a |grep strict_maxclient strict_maxclient = 1 vmo -a |grep lru_file_repage lru_file_repage = 1 word版

vmo -a |grep minperm%

minperm% = 10 vmo -a |grep maxclient%

maxclient% = 20 vmo -a |grep maxperm%

maxperm% = 20 vmo -a |grep minfree

minfree = 960 vmo -a |grep maxfree

maxfree = 1088

vmo -a |grep strict_maxperm

oradb1上设置的优化值 vmo -p -o v_pinshm=1 vmo -p -o maxpin%=75

oradb2上设置的优化值

vmo -p -o v_pinshm=1 vmo -p -o maxpin%=75 vmo -p -o minperm%=3 vmo -p -o maxclient%=8 vmo -p -o maxperm%=8

vmo -p -o minperm%=5 vmo -p -o maxperm%=90 vmo -p -o maxclient%=90

vmo -p -o minperm%=10 -o maxperm%=20 -o maxclient%=20 -o strict_maxperm=1 -o strict_maxclient=1 word版

应用的需求，因此需要进展一些调整。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_repage=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和numclient在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 10

avm Active virtual pages

avm定义为the number 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 space po Pages paged out to paging space

正常情况下pi和po不应该持续为非0值; fr Pages freed (page replacement).

sr Pages scanned by page-replacement algorithm 正常情况下fr和sr根本一致; word版

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. 正常情况下一般r<5

b 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(在队列中等待的线程数目)有可能是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 -v

numperm 和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 -G

In use work表示的是正在使用的work类型的内存; in use pers表示的是正在使用的persistent类型的内存(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 username='BASE_TUTOR';

AIX 下mount NSF文件

nfso -o nfs_use_reserved_ports=1

AIX下使用nmon 每10分抓一次资源使用情况 ./nmon_aix53 -f -s 600 -c 10000

第二章 SQL语句篇

1. 根底操作 1.1. 用户操作

建立数据库用户

create user tutor1 identified by tutor18756 default tablespace develop temporary 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 TABLESPACE \"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 xxx_user profile limit_2;

ALTER USER xxx_user profile default; ------取消

解锁用户

alter user system account unlock; word版

修改用户密码和显示用户信息 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 force=y;

orapwd file=/dev/rlv_pwdfile_240m password= entries=10 force=y

删除用户

drop user tutor1 cascade;

1.2. 表空间操作

建表空间

CREATE TABLESPACE FVDB DATAFILE

'D:\\ORADATA\\FVDB04.DBF' SIZE 4096M AUTOEXTEND OFF, 'D:\\ORADATA\\FVDB03.DBF' SIZE 4096M AUTOEXTEND OFF, 'D:\\ORADATA\\FVDB02.DBF' SIZE 4096M AUTOEXTEND OFF, 'D:\\ORADATA\\FVDB01.DBF' SIZE 4096M AUTOEXTEND OFF LOGGING

EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

create user scott identified by tiger default tablespace users; grant dba, resource, connect to scott;

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 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 50m maxsize 2048m;

select tablespace_name, file_name word版

from dba_data_files union

select tablespace_name, file_name from dba_temp_files;

把用户从一个表空间导入到另一表空间. 回收用户unlimited权限

revoke unlimited tablespace from childpalace_demo; alter 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 tablespace_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='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$$';

查出此表上的所有索引

select index_name from user_indexes where table_name='CMS_INFO_HIS'; word版

重命名表名字

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_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 service

在当前实例中将 cluster_database 设置为 FALSE 来禁用集群实例参数 alter system set cluster_database=false scope=spfile sid='racdb1';

archive log list

1.3. 监控查询操作

集群中所有正在运行的实例 SELECT inst_id

, instance_number inst_no , instance_name inst_name , parallel , status

, database_status db_status , active_state state , host_name host FROM gv$instance ORDER BY inst_id; word版

位于磁盘组中的所有数据文件 select name from v$datafile union

select member from v$logfile union

select name from v$controlfile union

select name from v$tempfile;

属于“RACDB_DATA1〞磁盘组的所有 ASM 磁盘 SELECT path

FROM v$asm_disk

WHERE group_number IN (select group_number from v$asm_diskgroup

where name = 'RACDB_DATA1');

运行缓冲中的 SQL 语句：

COLUMN instance_name FORMAT a13 COLUMN host_name FORMAT a9 COLUMN failover_method FORMAT a15 COLUMN failed_over FORMAT a11

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';

识别出5个最消耗cpu时间的SQL查询(适用于9i以上版本) select * from

(select * from v$sql order by cpu_time desc) q where rownum<=5; word版

诊断系统中磁盘上已经完成的排序数量育内存中的排序数量的百分比〔一般小于0.05)，如果排序率大于阀值，那么应当增加sort_area_size select disk.value disk,mem.value memory,

round((disk.value/mem.value)*100,2) sort_ratio from v$sysstat disk, v$sysstat mem

where disk.name = 'sorts (disk)' and mem.name = 'sorts (memory)'

每次查询最多读盘次数的10个SQL查询 select * from

(select round((disk_reads/decode(executions,0,-99999999,executions))) disk_reads_per_exec, v$sql.* from v$sql

order by disk_reads_per_exec desc)Q where rownum<=10;

数据文件的读写统计

select fs.file#,df.name,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 fs WHERE df.file# =fs.file#;

临时文件的读写统计

select df.name file_name, fs.phyrds reads, fs.phywrts writes from v$datafile df,v$filestat fs where df.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_id AND block_id + blocks - 1;

根据操作系统PID,查询SESSION信息

SELECT a.sid, a.serial#, b.spid, a.terminal, a.machine, a.program, a.osuser FROM v$session a, v$process b word版

WHERE a.paddr = b.addr AND b.spid = '&SPID';

根据SESSION SID,查询操作系统PID

SELECT a.sid, a.serial#, b.spid, a.terminal, a.machine, a.program, a.osuser FROM v$session a, v$process b

WHERE a.paddr = b.addr AND a.sid = '&SID';

查询用户正在执行的SQL SELECT 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 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, s.program FROM v$session s, v$session_wait w WHERE s.sid = w.sid

AND w.event 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正在执行的SQL SELECT s.sid, s.username, t.hash_value, t.piece, t.sql_text FROM v$session s, v$session_wait w, v$sqltext 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对应的数据库对象 word版

SELECT /*+ RULE*/ s.sid, s.username, w.seq#, w.event,

d.segment_type, d.owner || '.' || d.segment_name AS segment_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;

查询导致LOCK的SID,SPID,LOCKED_OBJECT,LOCK_TYPE等信息 SELECT /*+ RULE*/

l.sid, p.spid, s.username,s.logon_time, s.osuser, s.program, 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 WHEN 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, 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, s.program 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 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.start_time, t.status, t.start_scnb, t.xidusn rollid, r.name rollname FROM v$session s, v$transaction t, v$rollname r WHERE s.saddr = t.ses_addr AND t.xidusn = r.usn;

查询LIBRARY CACHE PIN等待事件等待的对象 word版

视图缩写:[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject

SELECT /*+ RULE*/ addr, kglhdadr, kglhdpar, kglnaobj, kglnahsh, kglhdobj FROM x$kglob

WHERE kglhdadr IN (SELECT p1raw

FROM v$session_wait

WHERE event LIKE '%library%');

查询LIBRARY CACHE PIN等待事件中持有被等待对象的SESSION信息视图缩写:[K]ernel [G]eneric [L]ibrary Cache Manager Object [P]i[N]s

SELECT /*+ RULE*/ a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse, b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq FROM v$session a, x$kglpn b WHERE a.saddr = b.kglpnuse AND b.kglpnmod <> 0

AND b.kglpnhdl IN (SELECT p1raw

FROM v$session_wait

WHERE event LIKE '%library%');

查询LIBRARY CACHE PIN等待事件中持有被等待对象的SESSION执行的SQL语句 SELECT sql_text FROM v$sqlarea

WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN ( SELECT sql_address, sql_hash_value FROM v$session WHERE SID IN (

SELECT /*+ RULE*/ SID

FROM v$session a, x$kglpn b WHERE a.saddr = b.kglpnuse AND b.kglpnmod <> 0

AND b.kglpnhdl IN (SELECT p1raw

FROM v$session_wait

WHERE event LIKE '%library%')));

查询哪个SESSION正在使用某个对象(LIBRARY CACHE) SELECT DISTINCT s.sid, s.username, s.logon_time, s.osuser, s.program,

b.kglnahsh as SQL_HASH_VALUE, b.kglnaobj as SQL_TEXT FROM v$session s, x$kglpn n, x$kglob b WHERE n.kglpnuse = s.saddr

AND upper(b.kglnaobj) LIKE upper('%&OBJECT_NAME%') word版

AND n.kglpnhdl = b.kglhdadr;

查询V$SESSION_WAIT用户PIN住了哪些对象(LIBRARY CACHE) SELECT DISTINCT s.sid, s.username, s.logon_time, s.osuser, s.program, n.kglpnmod,

b.kglnahsh AS SQL_HASH_VALUE, b.kglnaobj AS SQL_TEXT FROM v$session s, x$kglpn n, x$kglob b WHERE n.kglpnuse = s.saddr AND n.kglpnhdl = b.kglhdadr AND s.sid IN (SELECT sid

FROM v$session_wait

WHERE event NOT LIKE '%SQL*Net%' AND event NOT LIKE '%rdbms%' AND event NOT LIKE '%timer%' AND event NOT LIKE '%jobq%') ORDER BY s.username;

查询哪些大对象被载入SHARED POOL时导致其它对象被老化 SELECT s.sid, s.username, s.logon_time, s.osuser, s.program,

k.ksmlrcom, k.ksmlrsiz, k.ksmlrnum, k.ksmlrhon, k.ksmlrses FROM x$ksmlru k, v$session s WHERE s.saddr = k.ksmlrses AND ksmlrsiz > 0;

查询Schema哪些表是全表扫描 SELECT o.name, x.tch

FROM obj$ o, x$bh x, dba_users u WHERE x.obj = o.dataobj#

AND STANDARD.bitand(x.flag, 524288) > 0 AND u.username = UPPER('&username') ORDER BY x.tch DESC;

查询低效率的SQL(BUFFER_GETS排序) SELECT *

FROM (SELECT s.sid, b.spid,

s.sql_hash_value, s.username, q.sql_text, word版

q.executions, q.buffer_gets,

ROUND(q.buffer_gets / q.executions) AS buffer_per_exec,

ROUND(q.elapsed_time / q.executions) AS cpu_time_per_exec, q.cpu_time, q.elapsed_time, q.disk_reads, q.rows_processed

FROM v$session s, v$process b, v$sql q WHERE s.sql_hash_value = q.hash_value AND s.paddr = b.addr AND s.status = 'ACTIVE' AND s.TYPE = 'USER' AND q.buffer_gets > 0 AND q.executions > 0

ORDER BY buffer_per_exec DESC) WHERE ROWNUM <= 10;

监控BufferCache命中率

SELECT a.value + b.value logical_reads, c.value phys_reads,

ROUND (100 * (1 - c.value / (a.value + b.value)), 4) hit_ratio FROM v$sysstat a, v$sysstat b, v$sysstat c WHERE a.NAME = 'db block gets' AND b.NAME = 'consistent gets' AND c.NAME = 'physical reads';

监控LibraryCache命中率

SELECT SUM (pins) total_pins, SUM (reloads) total_reloads, SUM (reloads) / SUM (pins) * 100 libcache_reload_ratio FROM v$librarycache;

查询产生的跟踪文件名

SELECT p1.VALUE || '/' || p2.VALUE || '_ora_' || p.spid || '.trc' filename FROM v$process p, v$session s, v$parameter p1, v$parameter p2 WHERE p1.NAME = 'user_dump_dest' AND p2.NAME = 'db_name' AND p.addr = s.paddr

AND s.audsid = USERENV ('SESSIONID');

删除表中的重复记录

DELETE FROM table_name a WHERE ROWID >

(SELECT MIN (ROWID) word版

FROM table_name b

WHERE b.pk_column_1 = a.pk_column_1 AND b.pk_column_2 = a.pk_column_2);

查询锁表信息

select t2.username,t2.sid,t2.serial#,t2.logon_time,t3.sql_text from v$locked_object t1,v$session t2,v$sqltext t3 where t1.session_id=t2.sid and t2.sql_address=t3.address order by t2.logon_time;

观察数据库SGA各内存池的状态 show parameter pga;

查看SHARED_POOL命中率

select sum(pinhits-reloads)/sum(pins)*100 \"hit radio\" from v$librarycache;

删除表中数据，并将占用空间释放，下次使用该表时需系统分配空间 truncate table tablename;

删除表内容以及表构造，但并不将占用空间释放，下次再次创立该表时，将自动使用该空间。 drop table tablename;

再看各节点temp空间的分布

select INST_ID,TABLESPACE_NAME TSNAME,

TOTAL_EXTENTS TOTAL,USED_EXTENTS USED,FREE_EXTENTS FREE from gv$sort_segment order by inst_id;

1.4. 会话进程操作

根据系统pid查找oracle进程所对应的sid、serial#等数据

select ses.status,ses.machine,ses.username,ses.sid,ses.serial# from v$session ses,v$process pro where pro.spid=13327 and ses.paddr=pro.addr;

根据SID查询SQL语句

select t2.sid, t2.serial#, t3.sql_text from v$session t2, v$sqltext t3 where t2.sid in (480) and t2.sql_address = t3.address;

select vsq.sql_text,vse.username from v$sqlarea vsq,v$session vse where address in word版

(select sql_address from v$session vse

where paddr in (select addr from v$process where spid = 12491));

查找当前的数据库会话

select status,machine,username,sid,serial# from v$session;

set linesize 500;

select status,machine,username,sid,serial# from v$session where username like 'BASE_%' order by username;

set linesize 500;

select status,machine,username,sid,serial# from v$session where username= 'OPEN_OFFICIAL' and status='ACTIVE';

停某个连接

alter base_famous kill session 'sid,serial#'; alter tj_open_law kill session 'sid,serial#';

找它UNIX的进程数

select pro.spid from v$session ses,v$process pro where ses.sid=394 and ses.paddr=pro.addr; select pro.spid from v$session ses,v$process pro where ses.sid=171 and ses.paddr=pro.addr;

查看版本号

select * from v$version;

查看当前进程数

select count(*) as process from v$process ; select * from v$process;

查看数据库会话数和进程数

SELECT * FROM V$PARAMETER WHERE NAME LIKE 'session%'; SELECT * FROM V$PARAMETER WHERE NAME LIKE 'proc%';

查看数据库当前会话数和最大到达的会话数

select sessions_current,sessions_highwater from v$license;

查找某个时间段的数据库连接数记录

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'; select * from session_num where

num>40 and reportdate>'10-may-2004 19:00:00'; word版

1.5. 其它信息查询

查看两表的active对应

select ttc.active as ttca,r.active as ra

from reckontab r ,temp_teachercourese ttc where r.reckoning_no=ttc.reckoning_no and r.active=0

在sys用户下，查询特定用户的表字段信息

select owner,b.object_type,object_name,col$.name column_name from col$, all_objects b

where col$.obj# =b.object_id and col$.name like '_TYPE' and b.object_type = 'TABLE' and owner='TUTOR1';

2. SQL查询

用什么语句查询字段呢？

desc table_name 可以查询表的构造

select field_name,... from ... 可以查询字段的值

select * from all_tables where table_name like '%' select * from all_tab_columns where table_name='??'

long类型的数据拷贝到long类型的字段中

copy from / insert using select * from

第三章数据库连接篇

DBA连接 sqlplus /nolog connect /as sysdba

RAC数据库连接字串

jdbc:oracle:thin:(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = g841o-db134-vip)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = word版

g842o-db136-vip)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = g843o-db138-vip)(PORT = 1521))(LOAD_BALANCE = yes))(CONNECT_DATA =(SERVICE_NAME = gzedu )(FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))

jdbc:oracle:thin:(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = P801B-DB032-VIP)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = P802B-DB034-VIP)(PORT = 1521))(LOAD_BALANCE = yes))(CONNECT_DATA =(SERVICE_NAME = base)(FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))

word版

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文