oracle扩展

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.ID1
      and b.sid = c.sid
      and c.username is not null
       AND 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 >7200
  and 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. 外键的屏蔽

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

declare
  cursor C_FK is
     select 'alter table '||table_name||' disable constraint '||constraint_name SQLSTR
     from user_constraints
     where constraint_type ='R'
     ;
begin
  for REC in C_FK loop
     EXECUTE IMMEDIATE REC.SQLSTR;
  end loop;
end;

8. Oracle手册和PLSQL Dev的使用