ORACLE DBA常用脚本

ORACLE DBA常用脚本及命令(一)

1、查看表空间的名称及大小

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size

from dba_tablespaces t, dba_data_files d

where t.tablespace_name = d.tablespace_name

group by t.tablespace_name;

2、查看表空间物理文件的名称及大小

select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name;

3、查看回滚段名称及大小

select segment_name, tablespace_name, r.status,

(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,

max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = https://www.360docs.net/doc/b17992603.html,n(+)

order by segment_name ;

4、查看控制文件

select name from v$controlfile;

5、查看日志文件

select member from v$logfile;

6、查看表空间的使用情况

select sum(bytes)/(1024*1024) as free_space,tablespace_name

from dba_free_space

group by tablespace_name;

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

7、查看数据库库对象

select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

8、查看数据库的版本

Select version FROM Product_component_version

Where SUBSTR(PRODUCT,1,6)='Oracle';

9、查看数据库的创建日期和归档方式

Select Created, Log_Mode, Log_Mode From V$Database;

10、查看当前所有对象

SQL> select * from tab;

11、建一个和a表结构一样的空表

SQL> create table b as select * from a where 1=2;

SQL> create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;

12、察看数据库的大小，和空间使用情况

SQL> col tablespace format a20

SQL> select b.file_id 文件ID,

b.tablespace_name 表空间,

b.file_name 物理文件名,

b.bytes 总字节数,

(b.bytes-sum(nvl(a.bytes,0))) 已使用,

sum(nvl(a.bytes,0)) 剩余,

sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id

group by b.tablespace_name,b.file_name,b.file_id,b.bytes

order by b.tablespace_name

/

dba_free_space --表空间剩余空间状况

dba_data_files --数据文件空间占用情况

13、查看现有回滚段及其状态

SQL> col segment format a30

SQL> SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;

14、查看数据文件放置的路径

SQL> col file_name format a50

SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id; 15、显示当前连接用户

SQL> show user

16、把SQL*Plus当计算器

SQL> select 100*20 from dual;

17、连接字符串

SQL> select 列1||列2 from 表1;

SQL> select concat(列1,列2) from 表1;

18、查询当前日期

SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;

19、用户间复制数据

SQL> copy from user1 to user2 create table2 using select * from table1;

20、视图中不能使用order by，但可用group by代替来达到排序目的

SQL> create view a as select b1,b2 from b group by b1,b2;

21、通过授权的方式来创建用户

SQL> grant connect,resource to test identified by test;

SQL> conn test/test

一、ORACLE的表的分类：

1、REGULAR TABLE：普通表，ORACLE推荐的表，使用很方便，人为控制少。

2、PARTITIONED TABLE：分区表，人为控制记录的分布，将表的存储空间分为若干独立的分区，记录按一定的规则存储在分区里。适用于大型的表。

二、建表

1 CREATE TABLE 表名（EMPNO NUMBER（2），NAME VARCHAR2（20）） PCTFREE 20 PCTUSED 50

STORAGE （INITIAL 200K NEXT 200K MAXEXTENTS 200 PCTINCREASE 0） TABLESPACE 表空间名称

[LOGGING|NOLOGGING]所有的对表的操作都要记入REDOLOG，ORACLE建议使用NOLOGGING；

[CACHE|NOCACHE]：是否将数据按照一定的算法写入内存。

2、关于PCTFREE 和PCTUSED

A、行迁移和行链接

B、PCTFREE：制止INSERT，为 UPDATE留FREE 空间

C、PCTUSED：为恢复INSERT操作，而设定的。

三、拷贝一个已经存在的表：

CREATE TABLE 新表名 STORAGE（。。） TABLESPACE 表空间

AS SELECT * FROM 老表名；

当老表存在约束，触发的时候，不会拷过去。

四、修改表的参数

ALTER TABLE 名称 PCTFREE 20 PCTUSED 50 STOAGE（MAXEXTENTS 1000）；

五、手工分配空间：

ALTER TABLE 名称 ALLOCATE EXTENT（SIZE 500K DATAFILE '。。'）；

1、SIZE选项，按照NEXT分配

2、表所在表空间与所分配的数据文件所在的表空间必须一样。

六、水线

1、水线定义了表的数据在一个BLOCK中所达到的最高的位置。

2、当有新的记录插入，水线增高

3、当删除记录时，水线不回落

4、减少查询量

七、如何回收空间：

ALTER TABLE 名称 DEALLOCATE UNUSED [KEEP 4[M|K]]

1、当空间分配过大时，可以使用本命令

2、如果没有加KEEP，回收到水线

3、如果水线《MINEXTENTS的大小回收到MINEXTENTS所指定的大小

八、TRUNCATE 一个表

TRUNCATE TABLE 表名，表空间截取MINEXTENT，同时水线重置。

九、DROP 一个表

DROP TABLE 表名 [CASCADE CONSTRAINTS]

当一个表含有外键的时候，是不可以直接DROP的，加CASCADE CONSRIANTS将外键等约束一并删掉。

十、信息获取

1、dba_object

2 dba_tables:建表的参数

3 DBA_SEGMENTS:

组合查询的连接字段：DBA_TABLES的table_name+dba_ojbect的object_name+dba_segments的SEGMENT_NAME

用户的管理

一、ORACLE的安全域

1、TABLESPACE QUOTAS：表空间的使用定额

2、DEFAULT TABLESPACE：默认表空间

3、TEMPORARY TABLESPACE：指定临时表空间。

4、ACCOUNT LOCKING：用户锁

5、RESOURCE LIMITE：资源限制

6、DIRECT PRIVILEGES：直接授权

7、ROLE PRIVILEGES：角色授权先将应用中的用户划为不同的角色，

二、创建用户时的清单：

1、选择一个用户名称和检验机制：A，看到用户名，实际操作者是谁，业务中角色。

2、选择合适的表空间：

3、决定定额：

4、口令的选择：

5、临时表空间的选择：先建立一个临时表空间，然后在分配。不分配，使用SYSTEM表空间

6、CREATE USER

7、授权：A，用户的工作职能

B，用户的级别

三、用户的创建：

1、命令：

CREATE USER 名称 IDENTIFIED BY 口令 DEFAULT TABLESPACE 默认表空间名 TEMPOARAY

TABLESPACE 临时表空间名

QUOTA 15M ON 表空间名

[PASSWORD EXPIRE]：当用户第一次登陆到ORACLE，创建时所指定的口令过期失效，强迫用户自己定义一个新口令。[ACCOUNT LOCK]：加用户锁

QUOTA UNLIMITED ON TABLESPACE：不限制，有多少有多少。

[PROFILE 名称]：受PROFILE文件的限制。

四、如何控制用户口令和用户锁

1、强迫用户修改口令：ALTER USER 名称 IDENTIFIED BY 新口令 PASSWORD EXPIRE；

2、给用户加锁：ALTER USER 名称 ACCOUNT [LOCK|UNLOCK]

3、注意事项：

A、所有操作对当前连接无效

B、1的操作适用于当用户忘记口令时。

五、更改定额

1、命令：ALTER USER 名称 QUOTA 0 ON 表空间名

ALTER USER 名字 QUOTA （数值）K|M|UNLIMITED ON 表空间名；

2、使用方法：

A、控制用户数据增长

B、当用户拥有一定的数据，而管理员不想让他在增加新的数据的时候。

C、当将用户定额设为零的时候，用户不能创建新的数据，但原有数据仍可访问。

六、DROP一个USER

1、DROP USER 名称

适合于删除一个新的用户

2、DROP USER 名称 CASCADE：删除一个用户，将用户的表，索引等都删除。

3、对连接中的用户不好用。

七、信息获取：

1、DBA_USERS:用户名，状态，加锁日期，默认表空间，临时表空间

2、DBA_TS_QUOTAS:用户名，表空间名，定额。

两个表的连接字段：USERNAME

GRANT CREATE SESSION TO 用户名

PROFILE的管理（资源

文件）

一、PROFILE的管理内容：

1、CPU的时间

2、I/O的使用

3、IDLE TIME（空闲时间）

4、CONNECT TIME（连接时间）

5、并发会话数量

6、口令机制：

二、DEFAULT PROFILE：

1、所有的用户创建时都会被指定这个PROFILE

2、DEFAULT PROFILE的内容为空，无限制

三、PROFILE的划分：

1、CALL级LIMITE：

对象是语句：

当该语句资源使用溢出时：

A、该语句终止

B、事物回退

C、SESSION连接保持

2、SESSION级LIMITE：

对象是：整个会话过程

溢出时：连接终止

四、如何管理一个PROFILE

1、CREATE PROFILE

2、分配给一个用户

3、象开关一样打开限制。

五、如何创建一个PROFILE：

1、命令：CREATE PROFILE 名称

LIMIT

SESSION_PER_USER 2

CPU_PER_SESSION 1000

IDLE_TIME 60

CONNECT_TIME 480

六、限制参数：

1、SESSION级LIMITE：

CPU_PER_SESSION:定义了每个SESSION占用的CPU的时间：（1/100 秒）

2、SESSION_PER_USER:每个用户的并发连接数

3、CONNECT_TIME:一个连接的最长连接时间（分钟）

4、LOGICAL_READS_PER_SESSION: 一次读写的逻辑块的数量

5、CALL级LIMITE

CPU_PER_CALL:每个语句占用的CPU时间

LOGICAL_READS_PER_CALL:

七、分配给一个用户：

CREATE USER 名称。。。。。。

PROFILE 名称

ALTER USER 名称 PROFILE 名称

八、打开资源限制：

1、RESOURCE_LIMT:资源文件中含有

2、ALTER SYSTEM SET RESOURCE_LIMIT=TRUE；

3、默认不打开

九、修改PROFIE的内容：

1、ALTER PROFILE 名称参数新值

2、对于当前连接修改不生效。

DROP一个PROFILE

1、DROP PROFILE 名称

删除一个新的尚未分配给用户的PROFILE，

2、DROP PROFILE 名称 CASCADE

3、注意事项

A、一旦PROFILE被删除，用户被自动加载DEFAULT PROFILE

B、对于当前连接无影响

C、DEFAULT PROFILE不可以被删除

信息获取：

1、DBA_USERS:

用户名，PROFILE

2、DBA_PROFILES:

PROFILE及各种限制参数的值

每个用户的限制：PROFILE（关键字段）

PROFILE的口令机制限制

1、限制内容

A、限制连续多少次登录失败，用户被加锁

B、限制口令的生命周期

C、限制口令的使用间隔

2、限制生效的前提：

A、RESOURCE_LIMIT:=TRUE

B ORACLE\RDBMS\ADMIN\UTLPWDMG.SQL

3、如何创建口令机制：

CREATE PROFILE 名称

SESSIONS_PER_USER

.....

password_life_time 30

failed_log_attempts 3

password_reuse_time 3

4、参数的含义：

A FAILED_LOGIN_ATTEMPTS:

当连续登陆失败次数达到该参数指定值时，用户加锁

B PASSWORD_LOCK_TIME:加锁天数

C PASSWORD_LIFE_TIME:口令的有效期（天）

D PASSWORD_GRACE_TIME:口令修改的间隔期（天）

E PASSWORD_REUSE_TIME:口令被修改后原有口令隔多少天被重新使用。

F PASSWORD_REUSE_MAX:口令被修改后原有口令被修改多少次被重新使用。

rem 这需要统计某个具体用户的"Table,index,column,constraits"

rem================================================================

rem 全部表-列定义 table_cols.txt

set lin 110 pages 3000

column table_name format a30

column data_type format a12

column data_default format a8

column column_name format a22

column Cid format 999

column Len format 9999

column Prec format 99

column Scale format 99

select TABLE_NAME, COLUMN_ID "Cid", COLUMN_NAME, DATA_TYPE, DATA_LENGTH "Len", nvl(DATA_PRECISION,'-1') "Prec", nvl(DATA_SCALE,'-1') "Scale",

NULLABLE, DATA_DEFAULT

from USER_TAB_COLUMNS ;

rem ======== TAB =============

select * from tab;

spool off

spool user_indexes.txt

rem================================================================

rem 全部索引定义user_index.txt

column table_name format a22

column index_name format a28

column index_type format a7

column column_name format a18

column # format 99

column Init format 999999;

select a.table_name, t.cache "C",a.index_name,

column_position "#",column_name,

UNIQUENESS,a.INITIAL_EXTENT/1024 "Init"

from user_indexes a, user_ind_columns c,user_tables t

where c.INDEX_NAME =a.INDEX_NAME

and a.table_name= t.table_name

order by a.table_name,a.index_name,column_position;

spool off

spool user_Obj_Table_Index.txt

rem================================================================

rem 用户对象，表和索引userObj_Table_Index.txt

set lin 111 pages 333

column table_name format a24

column index_name format a32

column tablespace_name a12

column Init format 999999;

rem 由于用户要关心的是我自己的详细数据的存放位置，下面分别得出index,tables select tablespace_name,table_name,cache,initial_extent/1024 "Init"

from user_tables order by tablespace_name,table_name;

select tablespace_name,table_name,index_name,initial_extent/1024 "Init"

from user_indexes order by tablespace_name,table_name,index_name; spool off

spool user_constraints.txt

rem================================================================

rem 全部表-列约束_user_constraints.txt

column CONSTRAINT_NAME format a30

column TABLE_NAME format a30

column r_CONSTRAINT_NAME format a20

select CONSTRAINT_NAME,

CONSTRAINT_TYPE, TABLE_NAME, R_CONSTRAINT_NAME, DELETE_RULE

from user_constraints

order by CONSTRAINT_TYPE,TABLE_NAME;

spool off

spool c:\user_index1rebld.sql

rem================================================================

rem 重建全部索引

rem select 'alter index '||index_name||' rebuild;' from user_indexes

rem where table_name = 'GWNEWS';

select 'alter index '||INDEX_NAME||' rebuild tablespace indexes;' from user_indexes; spool off

rem @index1rebld.sql

spool c:\user_sources.sql

rem================================================================

rem 全部代码，主要是procedure

column name format a22 ;

column text format a77;

break on name;

select text,name from user_source;

spool off

rem =============== End of File ==================

*************查看当前用户使用的操作****************************

SELECT a.machine,a.terminal,a.osuser, https://www.360docs.net/doc/b17992603.html,ername, b.sql_text

from v$session a, v$sqlarea b

where a.sql_address =b.address order by b.address

***************************************************

1. 监控事例的等待

select event,sum(decode(wait_Time,0,0,1)) "Prev",

sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"

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.360docs.net/doc/b17992603.html,n = https://www.360docs.net/doc/b17992603.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

order by df.tablespace_name;

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

select substr(a.file#,1,2) "#", substr(https://www.360docs.net/doc/b17992603.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. 监控 SGA 的命中率

select a.value + b.value "logical_reads", c.value "phys_reads",

round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"

from v$sysstat a, v$sysstat b, v$sysstat c

where a.statistic# = 38 and b.statistic# = 39

and c.statistic# = 40;

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时，参数需加大

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;

19、查看Lock

SELECT https://www.360docs.net/doc/b17992603.html,ername, m.sid, m.type,

DECODE(m.lmode, 0, 'None',

1, 'Null',

2, 'Row Share',

3, 'Row Excl.',

5, 'S/Row Excl.',

6, 'Exclusive',

lmode, ltrim(to_char(lmode,'990'))) lmode, DECODE(m.request,0, 'None',

1, 'Null',

2, 'Row Share',

3, 'Row Excl.',

4, 'Share',

5, 'S/Row Excl.',

6, 'Exclusive',

request, ltrim(to_char(m.request,

'990'))) request,

m.id1, m.id2

FROM v$session sn, v$lock m

WHERE (sn.sid = m.sid AND m.request != 0)

OR (sn.sid = m.sid

AND m.request = 0 AND lmode != 4

AND (id1, id2) IN (SELECT s.id1, s.id2

FROM v$lock s

WHERE request != 0

AND s.id1 = m.id1

AND s.id2 = m.id2)

)

ORDER BY id1, id2, m.request;

select l.sid,s.serial#,https://www.360docs.net/doc/b17992603.html,ername,s.terminal, decode(l.type,'RW','RW - Row Wait Enqueue', 'TM','TM - DML Enqueue',

'TX','TX - Trans Enqueue',

'UL','UL - User',l.type||'System') res, substr(https://www.360docs.net/doc/b17992603.html,,1,10) tab,https://www.360docs.net/doc/b17992603.html, owner,

l.id1,l.id2,

decode(l.lmode,1,'No Lock',

2,'Row Share',

3,'Row Exclusive',

4,'Share',

5,'Shr Row Excl',

6,'Exclusive',null) lmode,

decode(l.request,1,'No Lock',

2,'Row Share',

3,'Row Excl',

4,'Share',

5,'Shr Row Excl',

6,'Exclusive',null) request

from v$lock l, v$session s,

https://www.360docs.net/doc/b17992603.html,er$ u,sys.obj$ t

where l.sid = s.sid

and s.type != 'BACKGROUND'

and t.obj# = l.id1

and https://www.360docs.net/doc/b17992603.html,er# = t.owner#

--第二条语句比较有效。

20、显示表空间的使用情况：

col tsname format a16 justify c heading 'Tablespace'

col nfrags format 999,990 justify c heading 'Free|Frags'

col mxfrag format 999,999,990 justify c heading 'Largest|Frag (KB)' col totsiz format 999,999,990 justify c heading 'Total|(KB)'

col avasiz format 999,999,990 justify c heading 'Available|(KB)' col pctusd format 990 justify c heading 'Pct|Used'

select

total.tablespace_name tsname,

count(free.bytes) nfrags,

nvl(max(free.bytes)/1024,0) mxfrag,

total.bytes/1024 totsiz,

nvl(sum(free.bytes)/1024,0) avasiz,

(1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd

from

dba_data_files total,

dba_free_space free

where

total.tablespace_name = free.tablespace_name(+)

group by

total.tablespace_name,

total.bytes;