工作随记：oracle重建一张1T数据量的大表

---

一、删除测试表

#xshell登录用户hthis用户连接登录处理：
sqlplus ht/"123456"
sqlplus ht/"123456"@10.8.5.23/htdb
drop table rcd_record_data_26 purge;
#创建新表的操作：脚本参考如下，注意检查where条件是否满足要求。
cd /u01/app/oracle/product/19,0.0,0/dbhome 1/OPatch/
sqlplus ht/"123456"
#c.sql是建表语句create as where
@c.sql
#创建完成后，注释可能乱码，PL/SQL工具核查一下字段默认值和注释：
#处理办法：打开command窗口执行如下：
comment on table RCD_RECORD_DATA_26 is 'xx数据';
comment on column RCD_RECORD_DATA_26.id is 'ID';
comment on column RCD_RECORD_DATA_26.data is 'xx数据';

#创建时注意表空间占用：
alter tablespace  TBS52023  add datafile '+DATAC1' size 20480m autoextend on next 10m;
alter tablespace  TBS52022  add datafile '+DATAC1' size 20480m autoextend on next 10m;
alter tablespace  TBS52021  add datafile '+DATAC1' size 20480m autoextend on next 10m;
alter tablespace  TBS52020  add datafile '+DATAC1' size 20480m autoextend on next 10m;
alter tablespace  TBS52019  add datafile '+DATAC1' size 20480m autoextend on next 10m;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

二、重命名旧表：

#重命名前检查：
select * from dba_dependencies where REFERENCED_NAME='RCD_RECORD_DATA';
#解锁防止占用：


select d.spid, c.object_name,a.USERNAME,a.OSUSER,a.MACHINE, a.STATUS,'ALTER SYSTEM KILL SESSION '''||a.sid||','||a.SERIAL#||''';'
from gv$session a ,gv$locked_object b,dba_objects c,gv$process d
where a.SID = b.SESSION_ID
and b.OBJECT_ID = c.object_id
and a.paddr=d.addr and c.object_name='RCD_RECORD_DATA';

#重命名：
alter table RCD_RECORD_DATA rename to RCD_RECORD_DATA_27;
#如果报错失败，需先处理锁表问题，再重命名。
#检查确认备份表数据正常：select count(*) from RCD_RECORD_DATA_27;
#处理完成后再把新表重命名为生产表：
alter table RCD_RECORD_DATA_26 rename to RCD_RECORD_DATA;
#检查确认生产表数据正常：select count(*) from RCD_RECORD_DATA;

#检查表空间大小：
@/home/oracle/tbs

#编译失效对象：
sqlplus / as sysdba
@?/rdbms/admin/utrlp.sql;

#补充缺失数据：
insert into RCD_RECORD_DATA(ID,DATA,CREATE_TIEM) select ID,DATA,CREATE_TIEM from RCD_RECORD_DATA_27 where id > '39716984';


insert into RCD_RECORD_DATA_26(ID,DATA,CREATE_TIEM) select ID,DATA,CREATE_TIEM from RCD_RECORD_DATA where id > '39716984';


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

三、验证：

#查询lob字段占用大小：
select * from dba_tables where owner ='HTHIS' and table_name='RCD_RECORD_DATA';

select e.segment_name,e.segment_type,sum(e.BYTES)/1024/1024/1024 as Gb from dba_extents e where e.segment_name='SYS_LOB0000629267C00002$$' 
group by e.segment_name,e.segment_type;
1
2
3
4
5

四、检查alert日志和昨天到今天的统计信息任务收集是否正常

#检查节点1，2的alert日志看是否还有异常报错？
#检查统计信息是否正常了？
select * from (select ACTUAL_START_DATE,RUN_DURATION,STATUS FROM dba_scheduler_job_run_details
where job_name like 'ORA$AT_OS_OPT%'
order by actual_start_date desc) where rownum < 30;

陈哈哈:
select d.spid, c.object_name,a.USERNAME,a.OSUSER,a.MACHINE, a.STATUS,'ALTER SYSTEM KILL SESSION '''||a.sid||','||a.SERIAL#||''';'
from v$session a ,v$locked_object b,dba_objects c,v$process d
where a.SID = b.SESSION_ID
and b.OBJECT_ID = c.object_id
and a.paddr=d.addr and c.object_name=upper('rcd_record_data');

陈哈哈:
-- tablespace usage
select  a.tablespace_name,
       round(a.bytes_alloc / 1024 / 1024) megs_alloc,
       round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
       round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
       round(maxbytes/1048576) Max
from  ( select  f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
        from dba_data_files f
        group by tablespace_name) a,
      ( select  f.tablespace_name,
               sum(f.bytes)  bytes_free
        from dba_free_space f
        group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
union all
select h.tablespace_name,
       round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,
       round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
       100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
       round(sum(f.maxbytes) / 1048576) max
from   sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where  p.file_id(+) = h.file_id
and    p.tablespace_name(+) = h.tablespace_name
and    f.file_id = h.file_id
and    f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY pct_used desc ;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49