网页设计和网站开发的区别,百度竞价的定义,网站建设优化需要懂那些知识,广州建设公共资源交易中心文章目录 一、删除测试表二、重命名旧表#xff1a;三、验证#xff1a;四、检查alert日志和昨天到今天的统计信息任务收集是否正常 一、删除测试表
#xshell登录用户hthis用户连接登录处理#xff1a;
sqlplus ht/123456
sqlplus ht/12345610.8.5.… 文章目录 一、删除测试表二、重命名旧表三、验证四、检查alert日志和昨天到今天的统计信息任务收集是否正常 一、删除测试表
#xshell登录用户hthis用户连接登录处理
sqlplus ht/123456
sqlplus ht/12345610.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;
二、重命名旧表
#重命名前检查
select * from dba_dependencies where REFERENCED_NAMERCD_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.paddrd.addr and c.object_nameRCD_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;
三、验证
#查询lob字段占用大小
select * from dba_tables where owner HTHIS and table_nameRCD_RECORD_DATA;select e.segment_name,e.segment_type,sum(e.BYTES)/1024/1024/1024 as Gb from dba_extents e where e.segment_nameSYS_LOB0000629267C00002$$
group by e.segment_name,e.segment_type;四、检查alert日志和昨天到今天的统计信息任务收集是否正常
#检查节点12的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.paddrd.addr and c.object_nameupper(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)) maxbytesfrom dba_data_files fgroup by tablespace_name) a,( select f.tablespace_name,sum(f.bytes) bytes_freefrom dba_free_space fgroup 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 ;
