当前位置：文档之家› oracle_dba常用sql脚本

oracle_dba常用sql脚本

Oracle SQL脚本命令

监控SQL

1.监控事例的等待：

select event,sum(decode(wait_time,0,0,1)) prev, sum(decode(wait_time,0,1,0)) curr,count(*) from v$session_wait

group by event order by 4;

2.回滚段的争用情况：

select name,waits,gets,waits/gets ratio from v$rollstat a,v$rollname b where https://www.doczj.com/doc/0d15308006.html,n=https://www.doczj.com/doc/0d15308006.html,n; 3.监控表空间的I/O 比例：

select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw

from v$filestat f,dba_data_files df

where f.file#=df.file_id

4.监空文件系统的I/O 比例：

select substr(a.file#,1,2) "#",substr(https://www.doczj.com/doc/0d15308006.html,,1,30) "name",a.status,a.bytes,

b.phyrds,b.phywrts

from v$datafile a,v$filestat b

where a.file#=b.file#

5.在某个用户下找所有的索引：

select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes

where user_ind_columns.index_name = user_indexes.index_name

and user_ind_columns.table_name = user_indexes.table_name

order by user_indexes.table_type, user_indexes.table_name,

user_indexes.index_name, column_position;

6.进程监控：

select distinct p.spid unix_process,

s.terminal,

to_char(s.logon_time,'YYYY/MON/DD HH24:MI') Logon_Time,

https://www.doczj.com/doc/0d15308006.html,ername

from v$process p, v$session s

where p.addr=s.paddr order by 2

7. 监控SGA 中字典缓冲区的命中率

select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",

(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"

from v$rowcache

where gets+getmisses <>0

group by parameter, gets, getmisses;

8. 监控SGA 中共享缓存区的命中率，应该小于1%

select sum(pins) "Total Pins", sum(reloads) "Total Reloads",

sum(reloads)/sum(pins) *100 libcache

from v$librarycache;

select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent" from v$librarycache;

9. 显示所有数据库对象的类别和大小

select count(name) num_instances ,type ,sum(source_size) source_size ,

sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size, sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required from dba_object_size

group by type order by 2;

10. 监控SGA 中重做日志缓存区的命中率，应该小于1%

SELECT name, gets, misses, immediate_gets, immediate_misses,

Decode(gets,0,0,misses/gets*100) ratio1,

Decode(immediate_gets+immediate_misses,0,0,

immediate_misses/(immediate_gets+immediate_misses)*100) ratio2

FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

11. 监控内存和硬盘的排序比率，最好使它小于.10，增加sort_area_size

SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)'); 12. 监控当前数据库谁在运行什么SQL语句

SELECT osuser, username, sql_text from v$session a, v$sqltext b

where a.sql_address =b.address order by address, piece;

13. 监控字典缓冲区

SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE; SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;

SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;

后者除以前者,此比率小于1%,接近0%为好。

SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"

FROM V$ROWCACHE

14. 找ORACLE 字符集

select * from sys.props$ where name='NLS_CHARACTERSET';

15. 监控MTS

select busy/(busy+idle) "shared servers busy" from v$dispatcher;

此值大于0.5 时，参数需加大

select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher'; select count(*) from v$dispatcher;

select servers_highwater from v$mts;

servers_highwater 接近mts_max_servers 时，参数需加大

16. 碎片程度

select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name

having count(tablespace_name)>10;

alter tablespace name coalesce;

alter table name deallocate unused;

create or replace view ts_blocks_v as

select tablespace_name,block_id,bytes,blocks,'free space' segment_name from

dba_free_space

union all

select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;

select * from ts_blocks_v;

select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space group by tablespace_name;

查看碎片程度高的表

SELECT segment_name table_name , COUNT(*) extents

FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY

segment_name

HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);

17. 表、索引的存储情况检查

select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;

select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'

group by segment_name;

18、找使用CPU 多的用户session

12 是cpu used by this session

select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c

where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;

20.监控log_buffer 的使用情况：(值最好小于1%，否则增加log_buffer 的大小)

select https://www.doczj.com/doc/0d15308006.html,,rbar.value,https://www.doczj.com/doc/0d15308006.html,,re.value,(rbar.value*100)/re.value||'%' "radio"

from v$sysstat rbar,v$sysstat re

where https://www.doczj.com/doc/0d15308006.html,='redo buffer allocation retries'

and https://www.doczj.com/doc/0d15308006.html,='redo entries';

19、查看运行过的SQL 语句：

SELECT SQL_TEXT

FROM V$SQL

常用用户SQL

表：

select * from cat;

select * from tab;

select table_name from user_tables;

视图：

select text from user_views where view_name=upper('&view_name');

索引：

select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by

table_name;

触发器：

select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;

快照：

select owner,name,master,table_name,last_refresh,next from user_snapshots order by owner,next;

同义词：

select * from syn;

序列：

select * from seq;

数据库链路：

select * from user_db_links;

约束限制：

select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS

from user_constraints WHERE TABLE_name=upper('&TABLE_Name');

本用户读取其他用户对象的权限：

select * from user_tab_privs;

本用户所拥有的系统权限：

select * from user_sys_privs;

用户：

select * from all_users order by user_id;

表空间剩余自由空间情况：

select tablespace_name,sum(bytes) 总字节数,max(bytes),count(*) from dba_free_space group

by tablespace_name；

数据字典：

select table_name from dict order by table_name;

锁及资源信息：

select * from v$lock;不包括DDL 锁

数据库字符集：

select name,value$ from props$ where name='NLS_CHARACTERSET';

inin.ora 参数：

select name,value from v$parameter order by name;

SQL 共享池：

select sql_text from v$sqlarea;

数据库：

select * from v$database

控制文件：

select * from V$controlfile;

重做日志文件信息：

select * from V$logfile;

来自控制文件中的日志文件信息：

select * from V$log;

来自控制文件中的数据文件信息：

select * from V$datafile;

NLS 参数当前值：

select * from V$nls_parameters;

ORACLE 版本信息：

select * from v$version;

描述后台进程：

select * from v$bgprocess;

查看版本信息：

select * from product_component_version;

查询表结构

select substr(table_name,1,20) tabname,

substr(column_name,1,20)column_name,

rtrim(data_type)||'('||data_length||')' from system.dba_tab_columns where owner='username'

表空间使用状态

select a.file_id "FileNo",a.tablespace_name "Tablespace_name", round(a.bytes/1024/1024,4) "Total MB",

round((a.bytes-sum(nvl(b.bytes,0)))/1024/1024,4) "Used MB", round(sum(nvl(b.bytes,0))/1024/1024,4) "Free MB",

round(sum(nvl(b.bytes,0))/a.bytes*100,4) "%Free"

from dba_data_files a, dba_free_space b

where a.file_id=b.file_id(+)

group by a.tablespace_name,

a.file_id,a.bytes order by a.tablespace_name

查询某个模式下面数据不为空的表

declare

Cursor c is select TNAME from tab;

vCount Number;

table_nm Varchar2(100);

sq varchar2(300);

begin

for r in c loop

table_nm:=r.TNAME;

sq:='select count(*) from '|| table_nm;

execute immediate sq into vCount;

if vCount>0 then

dbms_output.put_line(r.tname);

end if;

end loop;

end;

客户端主机信息

SELECT

SYS_CONTEXT('USERENV','TERMINAL') TERMINAL,

SYS_CONTEXT('USERENV','HOST') HOST,

SYS_CONTEXT('USERENV','OS_USER') OS_USER,

SYS_CONTEXT('USERENV','IP_ADDRESS') IP_ADDRESS

FROM DUAL

安装Oracle 后，经常使用的修改表空间的SQL 代码

配置：

Windows NT 4.0 中文版

5 块10.2GB SCSI 硬盘

分：C:盘、D:盘、E:盘、F:盘、G:盘

Oracle 8.0.4 for Windows NT

NT 安装在C:\WINNT，Oracle 安装在C:\ORANT

目标：

因系统的回滚段太小，现打算生成新的回滚段，

建立大的、新的表空间（数据表空间、索引表空间、回滚表空间、临时表空间、）建两个数据表空间、两个索引表空间，这样建的目的是根据实际应用，

如：现有10 个应用用户，每个用户是一个独立子系统（如：商业进销存MIS系统中的财务、收款、库存、

人事、总经理等）

尤其大型商场中收款机众多，同时访问进程很多，经常达到50-100 个进程同时访问，

这样，通过建立多个用户表空间、索引表空间，把各个用户分别建在不同的表空间里（多个用户表空间放

在不同的物理磁盘上），

减少了用户之间的I/O 竞争、读写数据与写读索引的竞争（用户表空间、索引表空间也分别放在不同的物

理磁盘上）

规划：

C:盘、NT 系统，Oracle 系统

D:盘、数据表空间1（3GB、自动扩展）、回滚表空间1（1GB、自动扩展）

E:盘、数据表空间2（3GB、自动扩展）、回滚表空间2（1GB、自动扩展）

F:盘、索引表空间1（2GB、自动扩展）、临时表空间1（0.5GB、不自动扩展）

G:盘、索引表空间2（2GB、自动扩展）、临时表空间2（0.5GB、不自动扩展）

注：这只是一个简单的规划，实际规划要依系统需求来定，尽量减少I/O 竞争

实现：

1、首先查看系统有哪些回滚段及其状态。

SQL> col owner format a20

SQL> col status format a10

SQL> col segment_name format a20

SQL> col tablespace_name format a20

SQL> SELECT

OWNER,SEGMENT_NAME,TABLESPACE_NAME,SUM(BYTES)/1024/1024 M

2 FROM DBA_SEGMENTS

3 WHERE SEGMENT_TYPE='ROLLBACK'

4 GROUP BY OWNER,SEGMENT_NAME,TABLESPACE_NAME

5 /

OWNER SEGMENT_NAME TABLESPACE_NAME M

-------------------- -------------------- -------------------- ---------

SYS RB1 ROLLBACK_DATA .09765625

SYS RB10 ROLLBACK_DATA .09765625

SYS RB11 ROLLBACK_DATA .09765625

SYS RB12 ROLLBACK_DATA .09765625

SYS RB13 ROLLBACK_DATA .09765625

SYS RB14 ROLLBACK_DATA .09765625

SYS RB15 ROLLBACK_DATA .09765625

SYS RB16 ROLLBACK_DATA .09765625

SYS RB2 ROLLBACK_DATA .09765625

SYS RB3 ROLLBACK_DATA .09765625

SYS RB4 ROLLBACK_DATA .09765625

SYS RB5 ROLLBACK_DATA .09765625

SYS RB6 ROLLBACK_DATA .09765625

SYS RB7 ROLLBACK_DATA .09765625

SYS RB8 ROLLBACK_DATA .09765625

SYS RB9 ROLLBACK_DATA .09765625

SYS RB_TEMP SYSTEM .24414063

SYS SYSTEM SYSTEM .1953125

查询到18记录.

SQL> SELECT SEGMENT_NAME,OWNER,

2 TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS

3 FROM DBA_ROLLBACK_SEGS

4 /

SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID STATUS -------------------- -------------------- -------------------- ---------- --------- ----------

SYSTEM SYS SYSTEM 0 1 ONLINE

RB_TEMP SYS SYSTEM 1 1 OFFLINE

RB1 PUBLIC ROLLBACK_DATA 2 3 ONLINE

RB2 PUBLIC ROLLBACK_DATA 3 3 ONLINE

RB3 PUBLIC ROLLBACK_DATA 4 3 ONLINE

RB4 PUBLIC ROLLBACK_DATA 5 3 ONLINE

RB5 PUBLIC ROLLBACK_DATA 6 3 ONLINE

RB6 PUBLIC ROLLBACK_DATA 7 3 OFFLINE

RB7 PUBLIC ROLLBACK_DATA 8 3 OFFLINE

RB8 PUBLIC ROLLBACK_DATA 9 3 OFFLINE

RB9 PUBLIC ROLLBACK_DATA 10 3 OFFLINE

RB10 PUBLIC ROLLBACK_DATA 11 3 OFFLINE

RB11 PUBLIC ROLLBACK_DATA 12 3 OFFLINE

RB12 PUBLIC ROLLBACK_DATA 13 3 OFFLINE

RB13 PUBLIC ROLLBACK_DATA 14 3 OFFLINE

RB14 PUBLIC ROLLBACK_DATA 15 3 OFFLINE

RB15 PUBLIC ROLLBACK_DATA 16 3 OFFLINE

RB16 PUBLIC ROLLBACK_DATA 17 3 OFFLINE

查询到18记录.

2、修改代码如下，可把以下代码存入一.sql 文件，如cg_sys.sql，然后以SQL> @cg_sys.sql 调用执行。

--注意：各个硬盘上要事先建好oradata 目录

--修改现有回滚段，使之失效，下线

alter rollback segment rb1 offline;

alter rollback segment rb2 offline;

alter rollback segment rb3 offline;

alter rollback segment rb4 offline;

alter rollback segment rb5 offline;

alter rollback segment rb6 offline;

alter rollback segment rb7 offline;

alter rollback segment rb8 offline;

alter rollback segment rb9 offline;

alter rollback segment rb10 offline;

alter rollback segment rb11 offline;

alter rollback segment rb12 offline;

alter rollback segment rb13 offline;

alter rollback segment rb14 offline;

alter rollback segment rb15 offline;

alter rollback segment rb16 offline;

--删除原有回滚段

drop rollback segment rb1;

drop rollback segment rb2;

drop rollback segment rb3;

drop rollback segment rb4;

drop rollback segment rb5;

drop rollback segment rb6;

drop rollback segment rb7;

drop rollback segment rb8;

drop rollback segment rb9;

drop rollback segment rb10;

drop rollback segment rb11;

drop rollback segment rb12;

drop rollback segment rb13;

drop rollback segment rb14;

drop rollback segment rb15;

drop rollback segment rb16;

--建数据表空间1

--收款、库存、订货、远程通信

create tablespace USER_DATA1 datafile 'd:\oradata\user1_1.ora' size 512M,

'd:\oradata\user1_2.ora' size 512M,

'd:\oradata\user1_3.ora' size 512M,

'd:\oradata\user1_4.ora' size 512M,

'd:\oradata\user1_5.ora' size 512M,

'd:\oradata\user1_6.ora' size 512M

AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

default storage (initial 128K next 2M pctincrease 0);

--initial 128K，因为，用户建在表空间上，而表建在用户里，为用户所拥有，

--用户继承数据表空间的存储参数，表继承用户的存储参数

--如果initial 设的过大，如：5M，则每建一个空表就要占用5M 的空间，即使一条记录也没有--AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED，设置数据文件自动扩展，每一次扩展增加5M，最大空间

不受限

--建数据表空间2

--物价、人事、结算、财务、总经理、合同、统计

create tablespace USER_DATA2 datafile

'e:\oradata\user2_1.ora' size 512M,

'e:\oradata\user2_2.ora' size 512M,

'e:\oradata\user2_3.ora' size 512M,

'e:\oradata\user2_4.ora' size 512M,

'e:\oradata\user2_5.ora' size 512M,

'e:\oradata\user2_6.ora' size 512M

AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

default storage (initial 128K next 2M pctincrease 0);

--建索引表空间1

create tablespace INDEX_DATA1 datafile

'f:\oradata\index1_1.ora' size 512M,

'f:\oradata\index1_2.ora' size 512M,

'f:\oradata\index1_3.ora' size 512M,

'f:\oradata\index1_4.ora' size 512M

AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

--建索引表空间2

create tablespace INDEX_DATA2 datafile

'g:\oradata\index2_1.ora' size 512M,

'g:\oradata\index2_2.ora' size 512M,

'g:\oradata\index2_3.ora' size 512M,

'g:\oradata\index2_4.ora' size 512M

AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

default storage (initial 128K next 2M pctincrease 0);

--建回滚表空间1

--设置初始值40M（initial 40M），则每在这个表空间中建一个回滚段，--此回滚段自动继承此回滚表空间的存储参数，也即默认文件为40M create tablespace ROLLBACK_DATA1 datafile

'd:\oradata\roll1_1.ora' size 512M，

'd:\oradata\roll1_2.ora' size 512M

AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

default storage (initial 40M next 5M pctincrease 0);

--建回滚表空间2

create tablespace ROLLBACK_DATA2 datafile

'e:\oradata\roll2_1.ora' size 512M,

'e:\oradata\roll2_2.ora' size 512M

AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

default storage (initial 40M next 5M pctincrease 0);

--建临时表空间1

create tablespace TEMPORARY_DATA1 datafile

'f:\oradata\temp1_1.ora' size 512M

default storage (initial 10M next 3M pctincrease 0);

--建临时表空间2

create tablespace TEMPORARY_DATA2 datafile

'g:\oradata\temp2_1.ora' size 512M

--使其真正成为临时的

alter tablespace TEMPORARY_DATA1 temporary;

alter tablespace TEMPORARY_DATA2 temporary;

--建立新的回滚段，每个都一样大，不同大小的回滚段没有什么意义，系统是随机选择的。--建多少个，根据并发访问用户的多少，

--如果你们公司每天有50-100 个人员使用Oracle系统开发的管理软件，应该20 个以上create public rollback segment rb01 tablespace rollback_data1;

create public rollback segment rb02 tablespace rollback_data1;

create public rollback segment rb03 tablespace rollback_data1;

create public rollback segment rb04 tablespace rollback_data1;

create public rollback segment rb05 tablespace rollback_data1;

create public rollback segment rb06 tablespace rollback_data1;

create public rollback segment rb07 tablespace rollback_data1;

create public rollback segment rb08 tablespace rollback_data1;

create public rollback segment rb09 tablespace rollback_data2;

create public rollback segment rb10 tablespace rollback_data2;

--前8 个建在回滚表空间1 中，后8 个在回滚表空间2

create public rollback segment rb11 tablespace rollback_data2;

create public rollback segment rb12 tablespace rollback_data2;

create public rollback segment rb13 tablespace rollback_data2;

create public rollback segment rb14 tablespace rollback_data2;

create public rollback segment rb15 tablespace rollback_data2;

create public rollback segment rb16 tablespace rollback_data2;

create public rollback segment rb17 tablespace rollback_data2;

create public rollback segment rb18 tablespace rollback_data2;

create public rollback segment rb19 tablespace rollback_data2;

create public rollback segment rb20 tablespace rollback_data2;

--使回滚段online，即有效

alter rollback segment rb01 online;

alter rollback segment rb02 online;

alter rollback segment rb03 online;

alter rollback segment rb04 online;

alter rollback segment rb05 online;

alter rollback segment rb06 online;

alter rollback segment rb07 online;

alter rollback segment rb08 online;

alter rollback segment rb09 online;

alter rollback segment rb10 online;

alter rollback segment rb11 online;

alter rollback segment rb12 online;

alter rollback segment rb13 online;

alter rollback segment rb14 online;

alter rollback segment rb15 online;

alter rollback segment rb16 online;

alter rollback segment rb17 online;

alter rollback segment rb18 online;

alter rollback segment rb19 online;

alter rollback segment rb20 online;

--查看现有回滚段及其状态

col segment format a30

SELECT

SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;

--查看数据文件及其所在表空间、大小、状态

col file_name format a40

col tablespace_name format a20

select file_name,file_id,tablespace_name,bytes,status from dba_data_files;

至此，表空间重新规划完毕，这里讲的比较通俗，还有好多参数值得设置，能够把Oracle 设置到最优的

境界，

表空间设置完了，下面，就该好好的整理一下Oracle 的内存区了，

Oracle 很有意思，内存越大，效果越明显，所以有必要好好调整一下SGA 区，也就是主要配置ininorcl.ora

参数文件。

查看回滚段名称及大小

COLUMN roll_name FORMAT a13 HEADING 'Rollback Name'

COLUMN tablespace FORMAT a11 HEADING 'Tablspace'

COLUMN in_extents FORMAT a20 HEADING 'Init/Next Extents'

COLUMN m_extents FORMAT a10 HEADING 'Min/Max Extents'

COLUMN status FORMAT a8 HEADING 'Status'

COLUMN wraps FORMAT 999 HEADING 'Wraps'

COLUMN shrinks FORMAT 999 HEADING 'Shrinks'

COLUMN opt FORMAT 999,999,999 HEADING 'Opt. Size'

COLUMN bytes FORMAT 999,999,999 HEADING 'Bytes'

COLUMN extents FORMAT 999 HEADING 'Extents'

SELECT

a.owner || '.' || a.segment_name roll_name

, a.tablespace_name tablespace

, TO_CHAR(a.initial_extent) || ' / ' ||

TO_CHAR(a.next_extent) in_extents

, TO_CHAR(a.min_extents) || ' / ' ||

TO_CHAR(a.max_extents) m_extents

, a.status status

, b.bytes bytes

, b.extents extents

, d.shrinks shrinks

, d.wraps wraps

, d.optsize opt

FROM

dba_rollback_segs a

, dba_segments b

, v$rollname c

, v$rollstat d

WHERE

a.segment_name =

b.segment_name

AND a.segment_name = https://www.doczj.com/doc/0d15308006.html, (+)

AND https://www.doczj.com/doc/0d15308006.html,n = https://www.doczj.com/doc/0d15308006.html,n (+)

ORDER BY a.segment_name;

PL/SQL 入门教程

1.1 PL/SQL 简介

PL/SQL是ORACLE的过程化语言，包括一整套的数据类型、条件结构、循环结构和异常处理结构，PL/SQL

可以执行SQL 语句，SQL 语句中也可以使用PL/SQL 函数。

1.2 创建PL/SQL 程序块

DECLARE

…

BEGIN

…

EXCEPTION

END;

1.3 PL/SQL 数据类型

名称

类型

说明

NUMBER

数字型

能存放整数值和实数值，并且可以定义精度和取值范围

BINARY_INTEGER

数字型

可存储带符号整数，为整数计算优化性能

DEC

数字型

NUMBER 的子类型，小数

DOUBLE PRECISION

数字型

NUMBER 的子类型，高精度实数

INTEGER

数字型

NUMBER 的子类型，整数

INT

数字型

NUMBER 的子类型，整数

NUMERIC

数字型

NUMBER 的子类型，与NUMBER 等价REAL

数字型

NUMBER 的子类型，与NUMBER 等价SMALLINT

数字型

NUMBER 的子类型，取值范围比INTEGER 小VARCHAR2

字符型

存放可变长字符串，有最大长度

CHAR

字符型

定长字符串

LONG

字符型

变长字符串，最大长度可达32,767 DATE

日期型

以数据库相同的格式存放日期值BOOLEAN

布尔型

TRUE OR FALSE

ROWID

存放数据库的行号

例_____子：

DECLARE

ORDER_NO NUMBER(3);

CUST_NAME VARCHAR2(20); ORDER_DATE DATE;

EMP_NO INTEGER:=25;

PI CONSTANT NUMBER:=3.1416; BEGIN

NULL;

END;

1.4 处理PL/SQL 的异常

1.4.1 PL/SQL 的异常

例如：

DECLARE

X NUMBER;

BEGIN

如何书写优雅漂亮的SQL脚本

本篇来聊聊如何书写漂亮、整洁、优雅的SQL脚本，下面这些是我个人总结、整理出来的。姑且做个抛砖引玉吧，呵呵，欢迎大家一起来讨论。我们首先来看看一段创建数据表的脚本(如下所示)，你是否觉得有什么不妥或是不足呢？如果是你，你怎样书写呢? CREATE TABLE[dbo].[TableDataDictionary]( [TableID][int]IDENTITY(1,1) NOT NULL, [IpAddress][nvarchar](15) NOT NULL, [DataBaseName][nvarchar](35) NOT NULL, [TableName][nvarchar](35) NOT NULL, [Description][nvarchar](150) NULL, CONSTRAINT[PK_TableDataDictionary]PRIMARY KEY([Tableid]) ) 可能你也没有觉得它有什么不妥，因为你一直都是这样书写哦。而且更混乱、更杂的的脚本你也见过，也可能习惯了；那么来看看下面的脚本， USE[Test]; GO IF OBJECT_ID(N'TableDataDictionary') IS NULL CREATE TABLE[dbo].[TableDataDictionary] ( [TableID]INT IDENTITY(1,1) NOT NULL, [IpAddress]NVARCHAR(15) NOT NULL, [DataBaseNam e]NVARCHAR(35) NOT NULL, [TableName]NVARCHAR(35) NOT NULL, [Description]NVARCHAR(150) NULL, CONSTRAINT[PK_TableDataDictionary]PRIMARY KEY([Tableid]) ); ELSE PRINT'This table have been exist in database'; GO 上面两段脚本比起来，你是否觉得下面的更美观、优雅呢？接下来我们来看看四段申明变量的脚本,自己可以对比

SQL脚本编码规范

SQL脚本编码规范 V1.0 1、SQL脚本注释规范： -- ============================================= -- Author: js -- Create date: 2010-01-14 -- Description: 针对采购单（新）的入库审核及取消入库审核进行库存的减少与增加。 -- 视图主键名: gwbh【仅对视图有意义】 -- Modified by JS, 2011-01-05: 修改了XXXXX。 -- Modified by JS, 2011-05-05: 修改了XXXXX，更正了XX处错误。 -- ============================================= 2、视图命名规范：形如：cggl_cgdd_v，“cggl”表明视图所属的功能是“采购管理类”、“cgdd”表明视图对应的具体业务是“采购订单”业务。 3、函数命名规范：形如：f_getCgdj，见明知义——本函数返回值是一个产品的成本单价（f_getCgdjOfCpbh则更明确）。 4、触发器命名规范：形如：t_saleOrders_U、t_ saleOrders_I、t_ saleOrders_D，分别表示的是：在表saleOrders被“更新（update）”、“插入（insert）”、“删除（delete）”时被触发（分别明确对应于触发器的实际定义类型）。 5、存储过程命名规范：形如：p_OpenfireRosterMgr，见名知义——表明本存储过程实现的处理是完成Openfire（Openfire是一款基于XMPP协议的服务端程序名称）的用户列表（Roster）管理功能。附录：式例如下

从+Oracle数据库中导出SQL脚本

从Oracle数据库中导出SQL脚本基本上用到的语法如下： a. 获取单个的建表和建索引的语法 set heading off; set echo off; Set pages 999; set long 90000; spool DEPT.sql select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual; select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual; spool off; b.获取一个SCHEMA下的所有建表和建索引的语法，以scott为例： set pagesize 0 set long 90000 set feedback off set echo off spool scott_schema.sql connect scott/tiger; SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u; SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u; spool off; c.获取某个SCHEMA的建全部存储过程的语法 connect brucelau /brucelau; spool procedures.sql select DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name) from user_objects u where object_type = 'PROCEDURE'; spool off; 另： dbms_metadata.get_ddl('TABLE','TAB1','USER1')

数据库sql脚本

//多次回滚 select * from emp; update emp set sal=1where ename='SMITH'; commit; savepoint a; update emp set sal=2where ename='SMITH'; commit; savepoint b; update emp set sal=3where ename='SMITH'; commit; savepoint c; update emp set sal=400where ename='SMITH'; rollback to b;

PL/SQL -----例1 declare pl_grade number(5); begin select grade into pl_grade from Enrollment where cno='c2'; if pl_grade>=90then insert into Students values('888888','better','M',20,'Art'); end if; commit; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('出错'||SQLERRM); END; ----例2（if判断语句） declare pl_grade number(5); comment1 constant varchar(30):='你的分数是'; comment2 constant varchar(30):=',还需要努力'; begin select grade into pl_grade from Enrollment where cno='c3'; /*大于90输出成绩*/ if pl_grade>=90then Dbms_Output.put_line(pl_grade); /*小于70输出仍需努力*/ elsif pl_grade<70then Dbms_Output.put_line(comment1||pl_grade||comment2); end if; commit; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('出错'||SQLERRM);

常用SQL脚本

常用SQL脚本例28.1 查看数据库所在机器操作系统参数（xp_msver）查看数据库所在机器操作系统参数的存储过程如下 EXEC master..xp_msver 例28.2 查看当前数据库服务器名（xp_getnetname、HOST_NAME()）EXEC master..xp_getnetname 或者： SELECT HOST_NAME() 例28.3 查看服务器上所有Windows本地组（xp_enumgroups）查看服务器上所有Windows本地组的存储过程如下 EXEC master..xp_enumgroups 例28.4 查看服务器上安装的所有代码页（xp_enumcodepages）查看服务器上安装的所有代码页的存储过程如下 EXEC master..xp_enumcodepages 嘿嘿,是不是报没开启错误啊~~没错误略过先执行下面语句 USE master; GO EXEC sp_configure 'show advanced option', '1'; RECONFIGURE EXEC sp_configure 'Web Assistant Procedures',1 RECONFIGURE EXEC sp_configure 例28.5 查看指定目录的所有下一级子目录（xp_subdirs）查看C:\WINDOWS\Drivers目录所有下一级子目录的存储过程如下EXEC [master].[dbo].[xp_subdirs] 'C:\WINDOWS\Drivers'

查看磁盘信息的存储过程如下 EXEC [master].[dbo].[xp_availablemedia] 或者采用下列语句 EXEC master..xp_fixeddrives 请注意:其中，low free为以字节为单位的空闲空间，media type为驱动器类型，软驱为1，硬盘为2，CD-ROM为8。例28.8 查看硬盘文件信息（xp_dirtree）查看C盘文件信息的存储过程如下 EXEC master..xp_dirtree 'c:' 查看C盘深度为1的文件信息的存储过程如下 EXEC master..xp_dirtree 'c:',1 查看C盘深度为1、是否为的信息的存储过程如下 EXEC master..xp_dirtree 'c:',1,1 查看“C:\WINDOWS\Web”目录中的文件信息的存储过程如下 EXEC master..xp_dirtree 'C:\WINDOWS\Web' 例28.9 查看服务器提供的OLEDB程序（xp_enum_oledb_providers）查看服务器提供的OLEDB程序的存储过程如下 EXEC master..xp_enum_oledb_providers 例28.10 查看数据库服务名（@@SERVERNAME） print 'SQL Server数据库服务名: ' + convert(varchar(30),@@SERVERNAME) 查看结果如下： SQL Server数据库服务名: SZX\SQLS2008

SQL脚本移植到ORACLE的一些方法总结

SQL脚本移植到ORACLE的一些方法总结一、表、索引、约束脚本转换 1、把SQL SERVER脚本中的‘[’和‘]’去掉，（ORACLE中不能有此符号）；同时把‘GO’去掉，在每一段程序后面加上‘/’ 2、数据类型之间的转化：目前我们程序中需要改动的就以上几种。 3、自增长字段的处理：由于oracle 没有自动增长字段，对于在sql server中的自增长定义全部去掉，用oracle 的sequence替代，具体步骤如下： 1、查找sql server 脚本中的IDENTITY(1,1) 字符串，将它去掉； 2、在去掉的同时，建立一个sequence ： create sequence 表名_字段名// sequence 的名称由表名_字段名组成

start with 1 increment by 1 nomaxvalue nocycle 3、在建立了sequence 后，马上建立一个触发器，这个触发器的作用是在往原来的自增长字段表中插入一个记录前，将由sequence取到的下一个值自动加入新纪录的自增长字段： create or replace trigger 表名_trigger // 触发器的名称由表名_trigger 组成 before insert on 表名 for each row begin select 表名_字段名.nextval into :new.id from dual; end ; / 在实现了上述3个步骤后，不用更改任何sql脚本和程序代码，就能实现自增长字段同样的功能举例： CREATE TABLE VotingViewer ( id integer NOT NULL PRIMARY KEY, votingid integer NULL, sharetype integer NULL, resourceid integer NULL, subcompanyid integer NULL, departmentid integer NULL, roleid integer NULL,

一些常用sql脚本整理

一些常用脚本 nc产品支持王静一些实施和维护人员经常遇到的问题，在这稍做整理，希望对大家有所帮助。注：执行这些脚本前，一定要备份数据。在测试环境中测试没有问题后，方可使用。 1.预算中将系统预制用户tbadmin删除，如何恢复？首先，建立前台建立用户tbadmin，后执行以下脚本 update sm_user set cuserid= 'TB_NEW100000000004OP' where user_code='tbadmin' commit 2.5x版本固定资产做变动或者减少的时候，经常参照不到，如何处理？问题原因：已经对该卡片做过变更操作，但是没有确认完成，就有可能造成单据丢失解决sql: 通过公司编码查出公司pk select pk_corp from bd_corp where unitcode='3103' 1082 通过卡片编码和公司pk.查出卡片pk和变动单pk select pk_card, bill_code from fa_card where card_code='0000000001' and pk_corp='1082' 查出bill_code 为1082V510000000000HR0 通过变动单pk，查询变动单号 select bill_code from fa_log where code='1082V510000000000HR0' 查出bill_code 为0_HG_BD0806180001 根据以上查询出结果，进行修改，删除变动单垃圾数据，删除前要查询唯一性，及备份相应的表 select * from fa_altersheet where altersheet_code='HG_BD0806180001' and pk_corp='1082' update fa_altersheet set dr=1 where altersheet_code='HG_BD0806180001' and pk_corp='1082'; select * from fa_bill where billcode='HG_BD0806180001' and pk_corp='1082' update fa_bill set dr=1 where billcode='HG_BD0806180001' and pk_corp='1082'; select * from fa_card where bill_code='0_HG_BD0806180001' update fa_card set bill_code='' where bill_code='0_HG_BD0806180001'; select * from fa_log where bill_code='0_HG_BD0806180001' update fa_log set bill_code='' where bill_code='0_HG_BD0806180001' 3.删除非现金科目的现金流量辅助信息执行以下sql,注意替换相应的账簿pk

数据库的SQL脚本代码

PowerDesigner设计的PDM模型生成数据库的SQL脚本（数据库名称为testDB），代码如下 /*==============================================================*/ /* Database name: 流动人口管理系统PDM */ /* DBMS name: Microsoft SQL Server 2000 */ /* Created on: 2008-1-4 10:51:46 */ /*==============================================================*/ if exists (select * from odb.sysdatabases where name='TestDB') drop database TestDB go create database TestDB go use TestDB go if exists (select 1 from sysindexes where id = object_id('TenancyBargin') and name = 'HouseHost_Contract_FK' and indid > 0 and indid < 255) drop index TenancyBargin.HouseHost_Contract_FK go if exists (select 1 from sysindexes where id = object_id('TenancyBargin') and name = 'Person_Contract_FK' and indid > 0 and indid < 255) drop index TenancyBargin.Person_Contract_FK go if exists (select 1 from sysindexes where id = object_id('TenancyHouse') and name = 'C_Tenancy_FK' and indid > 0 and indid < 255) drop index TenancyHouse.C_Tenancy_FK go if exists (select 1 from sysobjects where id = object_id('HouseHost') and type = 'U') drop table HouseHost go if exists (select 1

员工管理Sql脚本

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'YGGL') DROP DATABASE YGGL GO CREATE DATABASE YGGL go use yggl go --创建表departments CREATE TABLE [dbo].[DepartMents]( [DepartmentID] [char](3) NOT NULL, [Departmentname] [char](20) NOT NULL, [Note] [varbinary](100) NULL, CONSTRAINT [PK_DepartMents] PRIMARY KEY CLUSTERED ( [DepartmentID] ASC ) ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门编号，主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DepartMents', @level2type=N'COLUMN',@level2name=N'DepartmentID' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DepartMents', @level2type=N'COLUMN',@level2name=N'Departmentname' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DepartMents', @level2type=N'COLUMN',@level2name=N'Note' GO --创建表salary CREATE TABLE [dbo].[Salary]( [EmployeeID] [char](6) NOT NULL, [InCome] [float] NOT NULL, [OutCome] [float] NOT NULL, CONSTRAINT [PK_Salary] PRIMARY KEY CLUSTERED

SQL 脚本优化方案

数据库性能优化方案脚本优化优化前SQL SQL脚本

执行效率，逻辑读分析，问题描述 (1)优化前SQL逻辑读性能截图 (2)逻辑读分析：该语句在数据库中执行后的逻辑读数据行为rows processed：42，消耗逻辑读为：consistent gets：11150，优化前的每行逻辑读占用为：consistent gets/rows processed，通过该公式获得每行逻辑读占用为265，该效率不满足正常性能需要。 (3)问题描述：该查询产生大量的逻辑读的原因是由于SQL语句中有过多而复杂的子查询；在开发库上的数据量仅为50左右，所以无法测试该问题。该SQL是电销系统系统上线以来一直存在，随着数量的增大，消耗数据读也会随之增加，性能会相应下降。测试环境说明测试数据库：数据库用户名 SQL涉及的相关表及数据量： tb_task_base表数据量为：138636 tb_task_assign表数据量为：53 tb_operate_del表数据量为：76 tb_call_history_record表数据量为：0 sys_duty表数据量为：4 sys_user表数据量为：51 sys_r_duty_agency表数据量为：4

sys_r_user_duty_agency表数据量为：69 相关表的索引字段： Tb_task_base表：索引名：PK_T_CRT_NME 列名：C_CRT_NM Tb_task_assign表：索引名：INDEX_ASSIGN_C_TASK_APPOINT_N 列名：C_TASK_APPOINT_NME 索引名：INDEX_ASSIGN_C_TASK_CDE 列名：C_TASK_CDE 索引名：C_TASK_FOUND 列名：C_TASK_FOUND Tb_operate_del表：索引名：INDEX_OPERATE_C_CRT_NME列名：C_CRT_NME 索引名：INDEX_OPERATE_C_FK_TASK_ID列名：C_FK_TASK_ID 索引名：INDEX_OPERATE_C_MOBILE列名：C_MOBILE 索引名：INDEX_OPERATE_C_OPERATE_MRK列名：C_OPERATE_MRK 索引名：INDEX_OPERATE_C_TASK_CDE列名：C_TASK_CDE 索引名：INDEX_OPERATE_T_CRT_TM列名：T_CRT_TM 索引名：INDEX_OPERATE_T_NXT_TRCT_TM列名：T_NXT_TRCT_TM 所属业务模块及场景模块：任务管理→任务回收→查询(条件：呼出次数为0，任务跟踪次数为0，其它不填查询) 优化方案脚本优化方案 1)sql本身语句优化：

sql脚本

考核脚本 SQL语句 --1、全乡镇街道人口数 select count(*) from w01 --2、七岁以上无身份证号码 select rtrim(hgb103) as 单位地址,rtrim(gb001) as 姓名,rtrim(hgb002) as 性别, gb012 as 出生日期,hgb015 as 户口性质,gb011 as 公民身份证号,hgb003 as 现居地地址,hgb005 as 户籍地地址 from w01 where gb011 is null and datediff(year,gb012,getdate())>=7 and gs911<4 --3、七岁以上没姓名或编报姓名 select rtrim(hgb103) as 单位地址,rtrim(gb001) as 姓名,rtrim(hgb002) as 性别, gb012 as 出生日期,hgb015 as 户口性质,gb011 as 公民身份证号,hgb003 as 现居地地址,hgb005 as 户籍地地址from w01 where (PATINDEX('%[^吖-做]%',rtrim(GB001)) > 0 and PATINDEX('%[^a-Z]%',rtrim(GB001)) > 0 or gb001 is null or len(rtrim(gb001))=1 or GB001 like '%孩') and datediff(year,gb012,getdate())>=7 and gs911<4 --4、户籍性质为空 select rtrim(hgb103) as 单位地址,rtrim(gb001) as 姓名,rtrim(hgb002) as 性别, gb012 as 出生日期,hgb015 as 户口性质,gb011 as 公民身份证号,hgb003 as 现居地地址,hgb005 as 户籍地地址 from w01 where gb015 is null --5、单位代码未录到末端 select rtrim(hgb103) as 单位地址,rtrim(gb001) as 姓名,rtrim(hgb002) as 性别, gb012 as 出生日期,hgb015 as 户口性质,gb011 as 公民身份证号,hgb003 as 现居地地址,hgb005 as 户籍地地址 from w01 where len(rtrim(GB103)) not in ('12','15') --6、户籍地未空 select rtrim(hgb103) as 单位地址,rtrim(gb001) as 姓名,rtrim(hgb002) as 性别, gb012 as 出生日期,hgb015 as 户口性质,gb011 as 公民身份证号,hgb003 as 现居地地址,hgb005 as 户籍地地址 from w01 where gb005 is null

vbs做的sql脚本执行工具

vbs 做的sql 脚本执行工具 vbs 做的sql 脚本执行工具 ################## 执行多目录下的所有sql 脚本的工具 #################################'#可以方便的执行指定的目录或文件到多个服务器多个数据库'#只会执行以 “.sql”后缀的脚本文件'#执行返回结果会生成报… ##################执行多目录下的所有sql 脚本的工具 ################################# '#可以方便的执行指定的目录或文件到多个服务器多个数据库 '#只会执行以“ .sql”后缀的脚本文件 '#执行返回结果会生成报告以文本文件打开 '【请在此过程中填写运行参数】 Sub todo Dim execTool Set execTool=new MySqlScriptExecTool '使用步骤： '一、指定需要执行脚本的服务器，分别为：目标服务器的ip、数据库名、数据库用户名、数据库密码execTool.addDatabaseLink "192.168.1.116","dbname","username","pwd"

'如果需要添加多个不同的服务器或者数据库，可以模仿如下方法继续添加 'execTool.addDatabaseLink "192.168.1.116","dbname2","username","pwd" '二、指定需要执行的脚本所在的目录：这样在最终执行前会搜索出具体的需要执行的文件，可以写多条类似语句添加多个目录 execTool.addFold "F:\exec\sql" 'execTool.addFold "D:\drp\DEV\db\SQLServer\report" '三、如果同时需要执行目录下面有子目录的脚本则需要去掉下面这句话前面的“ '” 'execTool.searchChildFolds()' 迭代找出所有子目录'找出指定目录里面的sql 脚本execTool.searchFiles() '四、也可以单独需要执行的文件 'execTool.addFile("D:\drp\DEV\db\SQLServer\common\init.sql ") '开始执行脚本到服务器execTool.execSqlScript() End Sub Class MySqlScriptExecTool '属性 Public folds()'sql 语句所在目录 Public files()' 文件

SQL脚本编程规范

文档信息

修改历史

-- ============================================= -- SQL 脚本规范示例 -- 建立日期：2008-01-14 -- 编写者：唐根平 -- 版本: V1.0 -- ============================================= /* 在每个存储过程、函数、触发器、视图前均需以下这段注释，每个完整的脚本文件头也需要如上所示的注释*/ -- ============================================= -- 程序编写：<你的中文姓名> -- 建立日期： -- 功能说明：<说明本模块或本脚本的功能> -- 备注：<可选，如有其它要特别强调或说明的请加此行；如没有则可省略此行> -- 调用示例：<如果是存储过程、函数则需要此行> -- 程序修改：<可选> -- 修改日期：<可选> -- 修改说明：<可选，每修改一次，请重复本行及上述二行一次> -- 程序修改：<可选> -- 修改日期：<可选> -- 修改说明：<可选，每修改一次，请重复本行及上述二行一次> -- ============================================= --**********************************************-- PRINT '描述接下来的若干段语句的功用，例：开始…………' --**********************************************-- IF EXISTS(某判断) BEGIN --执行操作1 --执行操作2 END IF NOT EXISTS(某判断) BEGIN --执行操作3 --执行操作4 END --执行操作5 GO

oracle dba常用sql脚本分类文档

Oracle 正常SQL 监控SQL 1.监控事例的等待： select event,sum(decode(wait_time,0,0,1)) prev, sum(decode(wait_time,0,1,0)) curr,count(*) from v$session_wait group by event order by 4; 2.回滚段的争用情况： select name,waits,gets,waits/gets ratio from v$rollstat a,v$rollname b where https://www.doczj.com/doc/0d15308006.html,n=https://www.doczj.com/doc/0d15308006.html,n; 3.监控表空间的I/O 比例： select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw from v$filestat f,dba_data_files df where f.file#=df.file_id 4.监空文件系统的I/O 比例： select substr(a.file#,1,2) "#",substr(https://www.doczj.com/doc/0d15308006.html,,1,30) "name",a.status,a.bytes, b.phyrds,b.phywrts from v$datafile a,v$filestat b where a.file#=b.file# 5.在某个用户下找所有的索引： select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position; 6.进程监控： select distinct p.spid unix_process, s.terminal,