oracle常见维护
空间维护
扩表空间
视实际需要，对于系统建设初期或出于业务成长期的系统，该操作比较频繁。
对于业务平稳的系统，较少采用。
普通表改分区表
移动表到另一表空间
SQL>Alter table <tablename> move tablespace <spacename>
命令执行期间会锁表
优点，会修改高水位线，释放空间
SQL>Alter index t_index rebuild
数据清理
- 删除分区
Alter table <tablename> Drop partition <partitionname>
- 删除无用数据
Alarmsummary ,failurelist表数据
- Blob字段清理
select l.table_name,l.column_name,round(s.bytes/1024/1024) me1
from user_segments s,user_lobs l
where s.segment_name=l.segment_name
and l.table_name='RCHECKRESCHECKLOG';
select l.table_name,l.column_name,round(s.bytes/1024/1024) me1
from user_segments s,user_lobs l
where s.segment_name=l.index_name
and l.table_name='RCHECKRESCHECKLOG'
清理CLOB空间
--删除过期历史数据
delete from push.push_sendstatus where createtime<trunc(sysdate)-10;
--先挪到其他表空间
ALTER TABLE push.push_sendstatus MOVE TABLESPACE ACTIVEMQ_TS;
ALTER TABLE push.push_sendstatus MOVE LOB(CONTENT) STORE AS SECUREFILE (TABLESPACE ACTIVEMQ_TS);
--此时已经很小了，再挪回原来的表空间，方便维护
ALTER TABLE push.push_sendstatus MOVE TABLESPACE push_ts;
ALTER TABLE push.push_sendstatus MOVE LOB(CONTENT) STORE AS SECUREFILE (TABLESPACE push_ts);
--重建索引
ALTER INDEX push.IDX_PUSH_STATUSID REBUILD ONLINE PARALLEL 4;
ALTER INDEX push.PK_PUSH_SENDSTATUS REBUILD ONLINE PARALLEL 4;
其中第一次move lob是最花时间的。之后都很快完成。
回滚表空间占用率过高：
UNDO表空间分配机制；
1. 分配当前extent的空闲块
2. 下一个extent过期，wrap in下一 extent进行分配
3. If the next extent is not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the undo segment and return the first block in the new extent.
4. If there is no free extent available, then steal expired extents from offline undo segments. De-allocate the expired extent from the offline undo segment and add it to the undo segment. Return the first free block of the extent.
5. If no expired extents are available in offline undo segments, then steal from online undo segments and add the new extents to the current undo segment. Return the first free block of the extent.
6. Extend the file in the UNDO tablespace. If the file can be extended then add an extent to the current undo segment and then return the block.
7. Tune down retention in decrements of 10% and steal extents that are now unexpired with respect to the lower retention value.
8. Steal unexpired extents from any offline undo segments.
9. Try to reuse unexpired extents from own undo segment. If all extents are currently busy (they contains uncommitted information) go to the step 10. Otherwise, wrap into the next extent.
10. Try to steal unexpired extents from any online undo segment.
11. If all the above fails then return ORA-30036 unable to extend segment by %s in undo tablespace ‘%s’
SQL> show parameters undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL> select status,round(sum(bytes) / 1024 / 1024 / 1024, 2) from dba_undo_extents e where tablespace_name='UNDOTBS2' group by status order by 1;
STATUS ROUND(SUM(BYTES)/1024/1024/1024,2)
--------- ----------------------------------
ACTIVE .06
EXPIRED 1.59
UNEXPIRED 18.87
查看undo表空间实际占用的sql：
select round(
(
(select round(sum(bytes) / 1024 / 1024 / 1024, 2)
from dba_undo_extents where tablespace_name='UNDOTBS1' and status='EXPIRED' group by tablespace_name
)
+
nvl((select round(sum(bytes)/1024/1024/1024,2)
from dba_free_space where tablespace_name='UNDOTBS1' group by tablespace_name
),0)
)
*100/
(select round(sum(bytes)/1024/1024/1024,2)
from dba_data_files where tablespace_name='UNDOTBS1' group by tablespace_name
),2 ) as "UNDOTBS AVAILABLE %" from dual
UNDO表空间更换
1、建立新的表空间UNDOTBS2
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oradata1/dbnms_emos/undotbs00.dbf' size 1500M reuse;
表空间已创建。
2、切换到新建的UNOD表空间上来,操作如下
SQL> alter system set undo_tablespace=UNDOTBS2 scope=both;
系统已更改。
3、将原来的UNDO表空间,置为脱机:
SQL> alter tablespace UNDOTBS1 offline;
表空间已更改。
4、删除原来的UNDO表空间:
SQL> drop tablespace UNDOTBS1 including contents AND DATAFILES CASCADE CONSTRAINTS ;
临时表空间
空间查询
SELECT temp_used.tablespace_name,
total - used as "Free",
total as "Total",
round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
FROM GV_$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name
临时表空间创建
1.创建中转临时表空间
create temporary tablespace TEMP1 tempfile '/dev/md/vg_yx_ora1/rdsk/d1017' SIZE 4089M;
2.改变缺省临时表空间 为刚刚创建的新临时表空间temp1
alter database default temporary tablespace temp1;
3.删除原来临时表空间
drop tablespace temp including contents and datafiles;
4.重新创建临时表空间
create temporary tablespace TEMP tempfile '/dev/md/vg_yx_ora1/rdsk/d1016' SIZE 4089M;
5.重置缺省临时表空间为新建的temp表空间
alter database default temporary tablespace temp;
6.删除中转用临时表空间
drop tablespace temp1 including contents and datafiles;
索引维护（分区索引和重建）
河南索引表空间块损坏
查询在损坏索引表空间上的索引名称
SQL> SELECT INDEX_NAME,STATUS FROM ALL_INDEXES WHERE TABLESPACE_NAME='INDX' ORDER BY INDEX_NAME;
创建新的索引表空间
SQL>CREATE TABLESPACE "INDX2" DATAFILE 'C:ORADATAAIDUINDX02.DBF' SIZE 20M REUSE LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
将旧索引表空间上的所有索引都置为UNUSABLE
这个操作比较关键，如果缺少这一步，在后面REBULID过程中将会报错失败。
SQL> ALTER INDEX AIDU.PK_TEST_FOR_INDEX UNUSABLE;
alter system set SKIP_UNUSABLE_INDEXES = TRUE;
重建索引：
SQL> ALTER INDEX AIDU.PK_TEST_FOR_INDEX REBUILD TABLESPACE INDX2;
索引重建
这几天发现对一个表shonsruserdatad插入数据的时候报错，如下所示是报错的信息：
DBD::Oracle::st execute failed: ORA-01502: 索引 'SLVIEW.PK_SHONSRUSERDATAD' 或这类索引的分区处于不可用状态 (DBD ERROR: OCIStmtExecute) [for Statement "insert into SHONSrUserDataD (ADCODE, HostNum, StatsTime,BADCODE,ILLEGALCOUNT,VENDOR) values(?,?,?,?,?,?)" with ParamValues: :p5='2-1', :p3='27-11月-11', :p6='华为', :p1='18017212673', :p4='18017212673', :p2='2'] at /slview/shapp/wj/etn/bin/SHONSrUserDataSum.pl line 252.
排查发现是这个表的索引PK_SHONSRUSERDATAD失效了，于是昨天重建这个索引，插入数据就正常了。
今天再运行程序插入数据，发现还是报上面那个错，查看这个索引发现又失效了，索引的状态变为了UNUSABLE 。
查看数据库的日志发现从昨天到今天有这样一条信息，如下所示：
Tue Nov 29 02:00:09 2011
Some indexes or index [sub]partitions of table SLVIEW.SHONSRUSERDATAD have been marked unusable
Tue Nov 29 02:00:09 2011
Some indexes or index [sub]partitions of table SLVIEW.SHONSRUSERDATAR have been marked unusable
Tue Nov 29 02:23:29 2011
Thread 1 advanced to log sequence 166923
reply:
SQL>select segment_name,partition_name from user_segments where segment_name='PK_SHONSRUSERDATAD';
SQL>select ui.index_name,ui.index_type,status,partitioned,uic.column_name from user_indexes ui,user_ind_columns uic
where ui.index_name=uic.index_name and ui.table_name='SHONSRUSERDATAD'
INDEX_NAME INDEX_TYPE STATUS PARTITION COLUMN_NAME
---------------------------------------- --------------------------- -------- --- --------------------------------------------------
INDEX_SHONSRUSERDATAD_BADCODE NORMAL N/A YES BADCODE
PK_SHONSRUSERDATAD NORMAL VALID NO VENDOR
PK_SHONSRUSERDATAD NORMAL VALID NO STATSTIME
PK_SHONSRUSERDATAD NORMAL VALID NO ADCODE
可以看出，这个表的索引PK_SHONSRUSERDATAD不是分区索引，虽然表是分区表，但索引不是分区管理，可能是引起失效的原因。
请重新创建一下，
创建索引：
CREATE INDEX PK_SHONSRUSERDATAD ON SHONSRUSERDATAD (ADCODE, STATSTIME, VENDOR) LOCAL TABLESPACE INDEXLIST;
Redolog 增长和切换过快
select snap_id,obj#,db_block_changes_total from dba_hist_seg_stat order by
db_block_changes_total;
SNAP_ID OBJ# DB_BLOCK_CHANGES_TOTAL
---------- ---------- ----------------------
38348 215659 418154976
38171 12650 445211616
select owner,object_name,object_type,object_id from dba_objects where object_id=obj#;
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> select p.spid,object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s,v$process p
where l.object_id = o.object_id and l.session_id=s.sid
and s.paddr=p.addr order by 2;
2.下面的语句用来杀死一个进程：
SQL> alter system kill session '24,111'; (其中24,111分别是上面查询出的sid,serial#)
或者杀死spid的操作系统进程
