公司网站制作深圳/网站推广优化排名教程

Oracle维护操作简介

1. 数据库结构

表空间是Oracle数据库存放Table的存储空间，它由一个或多个操作系统上的文件（或者裸设备）组成。

一个文件加入到表空间后，无法把它单独撤离，除非删除整个表空间。

1.1. 表空间的创建语句

create tablespace TBS_BACKUP datafile '/oradata/bill/backup0001_8g.dbf' size 8192M extent management local segment space management auto;

1.2. 表空间的扩展语句

alter tablespace TBS_BACKUP add datafile '/oradata/bill/backup0002_8g.dbf ' size 8192M ;

1.3. 表空间的删除语句

drop tablespace TBS_BACKUP;

删除表空间之前要先删除存放在该表空间上的所有表，可通过如下语句来查询

SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME=' TBS_BACKUP ';

1.4. 表空间使用情况的查询语句

SELECT A.TABLESPACE_NAME "表空间名",A.TOTAL_SPACE "总空间(1M)",NVL(B.FREE_SPACE,0) "剩余空间(1M)",NVL(B.FREE_SPACE8,0) "剩余空间(8M)", ROUND(NVL(B.FREE_SPACE,0)/A.TOTAL_SPACE*100,2) "百分比%"
FROM
(
SELECT TABLESPACE_NAME ,SUM(BYTES)/1024/1024 TOTAL_SPACE
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE '%%'
GROUP BY TABLESPACE_NAME
) A,
(
SELECT TABLESPACE_NAME,SUM(TRUNC(BYTES/1024/1024,0)) FREE_SPACE ,SUM(TRUNC(BYTES/1024/1024/8,0))*8 FREE_SPACE8
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME LIKE '%'
GROUP BY TABLESPACE_NAME
) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
ORDER BY 5

注：Oracle9i的表空间是自管理型，剩余空间看字段"剩余空间(1M)"就可以了，"剩余空间(8M)"字段可以忽略。

2. 用户和角色

Oracle系统的权限分两类，一类是系统预定义的权限点，如”create table”, “create view” ，这是权限控制的基础，比较细。一类是对表的查询、修改等权限，表的所有权归表的属主，由属主赋予其他用户查询修改录入的权限。

日常维护常用的几个系统权限如下：

? select any dictionary ：赋予用户查看系统字典的权限

? select any table ：赋予用户查看任何表的权限

? unlimited tablespace ：赋予用户对表空间的完全使用权限

为了使用方便，有了角色的概念，角色就是一堆权限的集合。系统有预定义了几个角色，与我们密切相关的角色如下：

? CONNECT： 有了该角色，用户才可以连接数据库

? RESOURCE： 有了该角色，用户建表、建视图等常用操作都可以进行了

? PUBLIC：给予了该角色什么权限，该数据库中所有用户都获得了该权限

? DBA：顾名思义，数据库管理员角色。

2.1. 创建用户的语句

create user AUSERNAME

identified by APASSWORD

default tablespace XXXXX

temporary tablespace TEMP

profile DEFAULT;

2.2. 删除用户的语句

删除了用户，该用户的所有对象也一同被删除，因此在删除用户前，要确保该用户的所有对象都是没有用的。

drop user AUSERNAME;

如果该用户下还有对象，上述语句执行失败，如果确认用户及其对象都是可以删除的，可以使用如下语句：drop user AUSERNAME cascade;

2.3. 赋权的语句

赋予权限用 grant； 收回权限用 revoke， 例如：

grant connect to AUSERNAME;

grant resource to AUSERNAME;

grant select on ATABLENAME to AUSERNAME;

2.4. profile

每个用户都有一个Profile ，不指定就使用默认的profile。

Profile 创建语句如下:
create profile QUERY_PROFILE limit
sessions_per_user unlimited
cpu_per_session unlimited
cpu_per_call unlimited
connect_time unlimited
idle_time unlimited
logical_reads_per_session unlimited
logical_reads_per_call unlimited
composite_limit unlimited
private_sga unlimited
failed_login_attempts 20
password_life_time unlimited
password_reuse_time 1800
password_reuse_max unlimited
password_lock_time .0005
password_grace_time 5
password_verify_function VERIFY_FUNCTION;
修改一个用户的profile的语句如下：
alter user AUSERNAME profile QUERY_PROFILE;

3. 数据字典

Oracle数据库的系统信息大都可以从数据字典中查得。

常用的数据字典可以从如下SQL中查到select * from dictionary

DBA_ 开头的字典，保存的都是系统的静态信息

USER_ 开头的字典，任何用户都可以查询，查到的是本用户相关的信息

V$ 开头的字典，是系统运行时的动态信息，从中可以看出系统的执行情况。

4. 杂项

数据库链： user_db_links

同义词：user_synonyms

序列号：user_sequences （.nextval）

函数、存储过程、包 ：dba_source

alter table ATABLENAME move tablespace ATABLESPACE;

4.1. 执行SQL的Shell脚本

$ cat runsql.sql

#!/bin/sh

sqlplus /nologin <<EEOOFF

connect username/passwd@dbserv

select * from dual;

EEOOFF

nohup sh runsql.sql >sql.log &

5. 分区

详见Oracle的手册。

分区和表的概念相当，分区的索引有Local型和全局型两种。Local型是按分区建索引，表有几个分区索引就有几个分区，分区名也相同。全局型索引忽略表的分区情况，按普通表来建分区，此时索引可以不分区，也可以按照用户指定的方式分区，与表分区没有任何联系。

当分区有调整时，相应分区的Local索引必须重建：

alter index IDX_SENDACCTITEMLOG rebuild partition P_200607;

任何分区有调整，全局索引都必须重建。

5.1. Range分区方式的切割分区语句

alter table send_acct_item_log split partition P_MAX at ( TO_DATE('2006-07-01', 'YYYY-MM-DD') ) into
( partition P_200606 tablespace acctdata,partition P_Max tablespace acctdata);

5.2. List分区方式的新增分区语句

alter table ticket_info_station_591 add Partition PART_OTHER values (default) tablespace JFTEST2

5.3. 分区移动表空间语句

ALTER TABLE parts MOVE PARTITION depot2  TABLESPACE ts094 NOLOGGING;ALTER TABLE ATABLENAME TRUNCATE PARTITION APARTITIONNAME ;

6. SESSION

当一个用户通过SQLPLUS或者PL/SQL Developer，或者其他任何方式连接到数据库时，数据库自动创建一个进程来接待该用户，这就是一个Session，在PL/SQL中开多个窗口，实际上将产生多个Session。

6.1. Session的信息查询

Session的信息可以通过查询数据字典 V$SESSION 来获得，点PL/SQL软件的菜单Tools->Sessions 也可以查看。

相关的几个常用数据字典：

v$session_wait 从中可以看出session目前正在等待的事件，分析这张表可以大致知道Session的执行情况

v$process 从中可以查询Oracle自动创建的接待进程的信息。

v$open_cursor 顾名思义，这里保存Session当前打开的游标的信息。

6.1.1. 查某个Session正在执行的SQL

select * from v$sqltext_with_newlines
where (address,hash_value) in
(
select sql_address,sql_hash_value from v$session
where       SID= &SID
)
order by ADDRESS,HASH_VALUE,piece

6.2. 锁

在执行update 和 delete 语句时，Oracle数据锁住正在操作的数据行，避免其他用户同时对这些记录做update或delete，直到用户执行了commit或者rollback，锁才会释放。

有些用户执行了语句后忘了提交，或其他异常原因导致锁长期未被释放，影响了其他用户的操作，这时需要查出这些锁，以及持有锁的Session，必要时可以强行杀死这些Session来释放锁。

6.2.1. 锁的查询

select b.sid, c.username, c.osuser, c.terminal, c.PROGRAM,C.PROCESS,decode(b.id2, 0, a.object_name, 'Trans-'||to_char(b.id1)) object_name,b.type,decode(b.lmode,0,'--Waiting--',1,'NULL',2,'Row Share',3,'Row Excl',4,'Share',5,'Sha Row Exc',6,'Exclusive','Other') "Lock Mode",decode(B.request,0,' ',1,'NULL',2,'Row Share',3,'Row Excl',4,'Share',5,'Sha Row Exc',6,'Exclusive','Other') "Req Mode",'ALTER SYSTEM KILL SESSION '''||TO_CHAR(C.SID)||','||TO_CHAR(C.SERIAL#)||''';',C.MACHINE,'kill -9 '||TO_CHAR(P.SPID)                    
from DBA_OBJECTS A, V$LOCK B, V$SESSION C, V$PROCESS P
where a.object_id(+) = b.ID1and b.sid = c.sidand c.username is not nullAND C.PADDR=P.ADDR
order by b.sid,b.id2

6.3. 长时间连接Session的查询

有些Session因为比较特殊的原因而挂住，不工作了，通过锁无法查出。下面的语句查询出所有长时间连在数据库上的Session ，对结果需要人工确认，找出异常的Session

select w.sid, s.SERIAL#, trunc(w.seconds_in_wait/3600/24,2) Days_in_Wait,s.USERNAME,s.PROGRAM,s.MACHINE,s.OSUSER, w.*, s.*,p.*,'ALTER SYSTEM KILL SESSION '''||TO_CHAR(S.SID)||','||TO_CHAR(S.SERIAL#)||''';',S.MACHINE,S.PROGRAM, 'kill -9 '||TO_CHAR(P.SPID) 
from v$session_wait w, v$session s, v$process p
where s.sid=w.sid  and s.PADDR=p.ADDR and w.seconds_in_wait >7200and w.EVENT not like '_mon%'
order by w.seconds_in_wait desc

6.4. 杀Session

系统提供的杀Session命令是：alter system kill session ‘sid, serial#’;

用该命令来杀进程是比较安全的，但在很多情况下，该命令执行后Session依然不死，这时可以通过关联V$PROCESS 查出Oracle的对应后台进程的进程号（SPID），通过操作系统的 “kill -9” 命令强制结束后台进程，达到杀Session的目的，执行前先确认该进程不是Oracle的系统进程。

上两节的查锁的SQL中，同时也提供了这两种杀Session的语句，只需复制出来执行即可。

7. 导入导出

exp和imp 是Oracle提供的导入导出工具，在数据库服务器上执行

命令的基本格式是 exp keyword1=value1 keyword2=value2 ……..

exp help=y 或者 imp help=y 可以看到他们的帮助信息。

导出有：导出全库数据、导出指定用户的数据、导出指定表的数据 三种模式，第一种模式目前我们不考虑。

导出的几个重要keyword 说明如下；

BUFFER 指定导出时使用的缓冲区的大小，为加快速度，可以设成10M

FILE 文件名，数据导出到该文件中

INDEXES 当索引不需要导出时，设置该参数为N，导出大量数据时建议不导索引，在导入后重建索引，速度更快

ROWS 决定了是否导出表中的数据，如果”=N”，只导出建表语句，不导出数据

LOG 文件名，导出的日志存放到该文件中，导出完毕后最好看看日志，确认导出是否成功

DIRECT 导出大量数据时，设置该参数＝Y，可以大幅度提高导出速度

OWNER 指定导出用户名，此时按用户导出数据

TABLES 指定导出的表名，多个表名时用逗号分割

导出的简单例子如下：

nohup exp system/passwd@ibsjf file=exp.dmp owner=bill_t2 direct=y buffer=10240000 indexs=n log=exp_bill_t2.log &

导入的几个常用keyword如下：

IGNORE 在导入出现创建对象失败时，继续后续导入流程，比如一个表已经存在了，希望从文件中导入数据，就需要设置该参数为Y

FROMUSER、TOUSER 默认导出时的用户时A，导入时也是A，如果要把数据导入到B用户中，就需要使用这两个参数

导入范例如下：

nohup imp system/passwd@IBSCX ignore=n file=exp.dmp fromuser=user_a touser=user_b buffer=10240000 log=imp.log &

提醒：1. 如果exp时连接的用户具有DBA的权限，imp时也必须连有DBA权限的用户才能导入。

2. 按表导出时，如果表间通过外键关联，要注意导出的次序。

7.1. 字符集

数据库的字符集不匹配可能导致导入不成功。

查询字符集语句如下：select userenv('language') from dual;

结果形式如下： SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280最后部分是字符集，是关键部分。

GB231280涵盖了US7ASCII，GBK又涵盖了GB231280，从子集到全集的转换是可以支持的。作法是导出时，客户端的字符集设置为目标数据库的字符集即可，如set nls_lang=AMERICAN_AMERICA.ZHS16GBK；

Unicode是不同的编码方式，GB到Unicode是无法自动转换的。

7.2. 外键的屏蔽

导入数据时经常因为外键的关系导致导入失败，可暂时屏蔽外键，导入成功后再启用。

declarecursor C_FK isselect 'alter table '||table_name||' disable constraint '||constraint_name SQLSTRfrom user_constraintswhere constraint_type ='R';
beginfor REC in C_FK loopEXECUTE IMMEDIATE REC.SQLSTR;end loop;
end;

8. Oracle手册和PLSQL Dev的使用