sql关于表的一些处理方式

查询那些采样表未建
select distinct(history_table_name) from svr_yc_sample_define where history_table_name not in (select lower(table_name) from user_tables where table_name like ‘YC_HS%’) order by history_table_name;

处理占用表空间最大的表
查出占用表空间最大的前 30 名的段名字（显示的占用大小单位是 M）：
with t as(
select segment_name, segment_type, tablespace_name, bytes / 1024 /
1024 bytes
from dba_segments
where owner =‘EMS’ order by bytes desc
) select * from t where rownum<30;
对于大字段表，通过段名查出表名：
select table_name,column_name from dba_lobs where segment_name =
‘SYS_LOB0000102419C00003$$’;
查出哪张表名后，作后续处理。一般 pas_operation_mode_data
pas_operation_mode_info scada_operation_mode_data
scada_operation_mode_info 存放方式数据的表，III 区可以 drop。其他的表
要酌情而论，有的遥测越限等表还要征得用户许可。
truncate table SCADA_OPERATION_MODE_DATA;
truncate table pas_operation_mode_data

查看数据库各表空间情况的语句：
select a.tablespace_name,a.bytes/1024/1024 “Sum MB”,(a.bytes-b.bytes)/1024/1024 “used MB”,b.bytes/1024/1024 “free MB”,round(((a.bytes-b.bytes)/a.bytes)*100,2) “percent_used” from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name order by ((a.bytes-b.bytes)/a.bytes) desc;

查看逻辑卷大小lvdisplay open_sample01 （hpux） lslv open_sample01 (AIX)
查看已有表空间裸设备总大小
select tablespace_name,file_id,file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;

扩表空间语句
阵列
alter tablespace open_sample add datafile ‘/dev/ropen_sam_ind12’ size 2048m; （ropen_sam_ind12已存在/dev下）
单机库
alter tablespace open_sample add datafile ‘/oradata/o2000/open_sample101.dbf’ size 2048m;
imp ems/naritech@webdb2 fromuser=ems touser=ems tables=tmp_yc_hs_6829 file=tmp_yc_hs_6829.dmp ignore=y;

sqlplus sys/platform@o20001 as sysdba
清理SYSAUX表空间语句
SQL> conn sys/platform as sysdba
SQL> truncate table SYS.WRI${}_{O}PTSTA{T}_{H}ISTGR{M}_{H}ISTORY;SQL>truncatetableSYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

JOB常识以及月、年曲线问题解决
Open3000系统月、年曲线来源于统计表，统计表的结构和采样表类似，如果一个采样是在yc_hs_0001号表的cur_001域存储（该信息可以在query_sample或sample_define界面里查到），那么该采样的统计则会放到yc_stat_0001号表的cur_001，每天会自动统计出该采样的最大值（发生时间）、最小值（发生时间）、平均值、负荷率以及积分电量。而统计本身由ORACLE数据库的一个存储过程来完成，该存储过程由ORACLE的JOB进行调度，一般情况下设置在每天的0:30运行。注意这种统计是各个ORACLE实例自己完成的，多个库之间并没有同步复制操作，因此可能会出现主库正常而WEB库不正常等类似的现象。
该存储过程封装在new_statistics_sample_pkg包（package）里，在sqlplus里执行该存储过程可以有两种方式
1、exec new_statistics_sample_pkg.day_statistics
2、exec new_statistics_sample_pkg.day_statistics(to_date(‘2019-07-02 0:0:0’,’YYYY-MM-DD HH24:MI:SS’)
第1种方式执行结果为统计昨天的所有采样；（例子为形成昨天统计）
第2种方式执行结果为统计指定时间的所有采样；（例子为形成2006-03-20统计）
如果月、年曲线没有，首先应该检查ORACLE实例的 job_queue_processes系统参数，例如：

SQL>show parameter job;
NAME TYPE VALUE

---

job_queue_processes integer 10

job_queue_processes参数应该等于（或大于）10，若为0则ORACLE数据库一旦重新启动，原先设置的所有JOB都会失效（new_statistics_sample_pkg.day_statistics当然也不会每天都执行）。
修改该参数可以用sqlplus，但注意必须以sysdba角色登录到指定数据库，例如o20001
必须rlogin到o20001运行的实际机器上并su到oracle用户下，然后输入sqlplus /nolog即可进入
SQL>conn sys/platform as sysdba;
SQL>alter system set job_queue_processes=10 scope=both;
成功后系统会显示参数已修改。
注意：如果oracle库不是以默认spfile启动的。
一般情况下我们安装的数据库都是以spfile启动的，可以通过如下脚本确定数据库以何种方式启动：
SQL> show parameter spfile

NAME TYPE VALUE

---

spfile string ?/dbs/spfile@.ora
如果VALUE显示一字符串，则该库以spfile启动，否则为pfile启动。如果是pfile启动，则需要确定pfile的路径，默认情况下pfile存放在$ORACLE_HOME/dbs目录下，并以“init”＋“实例名”＋“.ora”的方式命名，例如库实例为o20001，则pfile文件为：inito20001.ora。实际情况中也可以不使用默认路径，那么必须在startdb脚本的中startup后指定具体pfile，例如startdb脚本如下：
lsnrctl stop
lsnrctl start
sqlplus /nolog << EOF
connect sys/platform as sysdba
startup pfile=/users/oracle/admin/o2000/pfile/init.ora
EOF
即表示该库是以pfile启动，pfile文件为/users/oracle/admin/o2000/pfile/init.ora，
修改实际的pfile，手工在实际pfile末尾加上一行：
*.job_queue_processes=10（非阵列库直接写job_queue_processes=10）
然后以sysdba角色登录到指定数据库，例如o20001
必须rlogin到o20001运行的实际机器上并su到oracle用户下，然后输入sqlplus /nolog即可进入
SQL>conn sys/platform as sysdba;
SQL>alter system set job_queue_processes=10 scope=memory
另外如果可以启动图形界面，也使用Oracle图形界面修改该参数，例如：
1、启动Oracle图形界面（unix下的oemapp或PC下的Oracle Enterprise Manager Console）,连接到指定的数据库，例如o20001;
2、打开o20001->例程->配置->一般信息->所有初始化参数，找到job_queue_processes参数，其值应该为10，若不是，要改为10，并“应用”。
若该参数原先不正确，通过修改并在ORACLE重新启动后理论上所有的JOB应该可以自动恢复，可以通过查询dba_jobs系统视图查看所有JOB调度情况，例如：
SQL>select job ,what,last_date,next_date from dba_jobs;

JOB WHAT LAST_DATE NEXT_DATE

---

82 COMPILE_INVALID_OBJECTS 2006-03-22 16:12:38 2006-03-22 16:13:08
102 DELETE_YC_RECALL_DATA 2006-03-22 16:13:03 2006-03-22 16:15:03
122 DELETE_MSG_REAL_DATA 2006-03-22 16:13:03 2006-03-22 16:15:03
162 NEW_STATISTICS_SAMPLE_PKG.DAY_STATISTICS 2006-03-22 00:30:57 2006-03-23 00:30:00
….
在上面的例子中JOB＝162即是JOB中日统计运行的信息，该信息表明日统计最后运行时间为2006-03-22 00:30:57，而下次时间为2006-03-23 00:30:00。信息中如果下次时间不正常，例如是个非常不合理的时间，就需要重新建立该JOB。
在SQLPLUS中执行以下语句，删除失效的JOB：
SQL> EXECUTE DBMS_JOB.REMOVE(作业号);
SQL> commit;
其中“作业号”为dba_jobs中“JOB”域的值，对于上例的日统计，JOB号为162，因此如果要删除，应该：
SQL> EXECUTE DBMS_JOB.REMOVE(162);
SQL> commit;
执行完后检查JOB调度情况是否已删除。注意在不同系统中JOB号可能不一样，这是因为在建JOB时ORACLE不允许自行指定JOB号。
重新创建Job：下面的SQL语句(蓝色部分)可以在SQLPLUS中执行
? 自动执行 new_statistics_sample_pkg.day_statistics 存储过程，每天0:30做一次
? 如果要在1：30做，可以将1/48改为3/48
variable v_JobNum number
begin
dbms_job.submit(:v_JobNum, ‘NEW_STATISTICS_SAMPLE_PKG.DAY_STATISTICS;’, trunc(SYSDATE) + 1/48,‘trunc(SYSDATE + 1) + 1/48’);
dbms_job.run(:v_JobNum);
END;
/
这一操作的过程可能要等待较长时间，最好不要随意中断。执行完检查dba_jobs中的纪录是否已重新创建，LAST_DATE域和NEXT_DATE域里的日期是否是一个正常值。

注意日统计恢复后，系统并不会自动补以前的统计值，需要在ORACLE SQLPLUS中手工恢复，即采用第二种方式执行存储过程。例如：
SQL>exec new_statistics_sample_pkg.day_statistics(to_date(‘2006-03-01 0:0:0’,’YYYY-MM-DD HH24:MI:SS’)
上例是生成2006-03-01统计值。如果有大批量统计需要运行，可以通过下面的PLSQL产生一系列的exec语句（20006年3月1号到3月30号），第1行为允许dbms_output输出，第2行为将行显示加大，第3行为加大dbms_output输出容量，该容量默认为2000，可以根据实际需要增减，第7行start_time的初值以及第8行的循环次数根据实际进行设置。
set serveroutput on
set linesize 1000;
exec dbms_output.enable(5000);
begin
declare start_time date;
begin
start_time := to_date(‘2006-03-01 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);
for i in 1…30 loop
dbms_output.put_line(‘exec new_statistics_sample_pkg.day_statistics(to_date(’‘’ || start_time || ‘’‘, ‘‘YYYY-MM-DD HH24:MI:SS’’));’);
start_time := start_time + 1;
end loop;
end;
end;
/
输出结果为：
exec new_statistics_sample_pkg.day_statistics(to_date(‘2006-03-01 0:0:0’,’YYYY-MM-DD HH24:MI:SS’);
exec new_statistics_sample_pkg.day_statistics(to_date(‘2006-03-02 0:0:0’,’YYYY-MM-DD HH24:MI:SS’);
exec new_statistics_sample_pkg.day_statistics(to_date(‘2006-03-03 0:0:0’,’YYYY-MM-DD HH24:MI:SS’);
exec new_statistics_sample_pkg.day_statistics(to_date(‘2006-03-04 0:0:0’,’YYYY-MM-DD HH24:MI:SS’);
…
exec new_statistics_sample_pkg.day_statistics(to_date(‘2006-03-30 0:0:0’,’YYYY-MM-DD HH24:MI:SS’);
可以利用输出的结果恢复该段时间的统计，可以使用SQLPLUS的spool方法将该结果输出到指定文件，例如：
SQL>spool stat.sql
SQL>…
SQL>spool off
上面的SQL语句可以把省略号部分的所有SQL输出结果定向到stat.sql文件中。
如果要执行stat.sql内的全部内容，在SQLPLUS中输入
SQL>@spool stat.sql
即可。

附录
最后请大家注意如果其他JOB也不正常，请按照本文方法进行恢复，所有JOB的创建文档详见下面：
– 自动执行 compile_invalid_objects 存储过程，每30秒一次

variable v_JobNum number
begin
dbms_job.submit(:v_JobNum, ‘COMPILE_INVALID_OBJECTS;’, SYSDATE,
‘SYSDATE+(30/(246060))’);
dbms_job.run(:v_JobNum);
END;
/
– 自动执行 delete_yc_recall_data 存储过程，每120秒一次

variable v_JobNum number
–v_JobNum := 223;
begin
dbms_job.submit(:v_JobNum, ‘DELETE_YC_RECALL_DATA;’, SYSDATE,
‘SYSDATE+(120/(246060))’);
dbms_job.run(:v_JobNum);
END;
/
– 自动执行 delete_msg_real_data 存储过程，每120秒一次

variable v_JobNum number
–v_JobNum := 224;
begin
dbms_job.submit(:v_JobNum, ‘DELETE_MSG_REAL_DATA;’, SYSDATE,
‘SYSDATE+(120/(246060))’);
dbms_job.run(:v_JobNum);
END;
/
– 自动执行 new_statistics_sample_pkg.day_statistics 存储过程，每天0:30做一次

variable v_JobNum number
–v_JobNum := 225;
begin
dbms_job.submit(:v_JobNum, ‘NEW_STATISTICS_SAMPLE_PKG.DAY_STATISTICS;’, trunc(SYSDATE) + 1/48,
‘trunc(SYSDATE + 1) + 1/48’);
dbms_job.run(:v_JobNum);
END;
/
– 自动执行 delete_warn_data 存储过程，每天2:30做一次

variable v_JobNum number
–v_JobNum := 226;
begin
dbms_job.submit(:v_JobNum, ‘DELETE_WARN_DATA;’, trunc(SYSDATE) + 5/48,
‘trunc(SYSDATE + 1) + 5/48’);
dbms_job.run(:v_JobNum);
END;
/

如何检查哪张采样表或统计表未建
现场工程经常会遇到统计功能失效的情况，一般只能是将采样定义表中的历史采样表名和数据字典中的表名进行对比，人工找出具体是哪张表没建，这样既费时又容易忽略，现在可以通过以下两条语句分别查出哪张采样表或统计表未建：

1. 查采样表没建：
   select distinct(history_table_name) from svr_yc_sample_define where history_table_name not in(select lower(table_name) from user_tables where table_name like ‘YC_HS%’) order by history_table_name;
2. 查统计表未建
   select distinct(replace(history_table_name,‘hs’,‘stat’)) from svr_yc_sample_define where replace(history_table_name,‘hs’,‘stat’) not in (select lower(table_name) from user_tables where table_name like ‘YC_STAT%’) order by replace(history_table_name,‘hs’,‘stat’);

重建表，以yc_hs_0100为例：
SQL> create table yc_hs_0100 as select * from yc_hs_000001 where 0=1;
SQL> create table yc_stat_0100 as select * from yc_stat_000001 where 0=1;

2. 创建主键：
   SQL> alter table yc_hs_0100 add constraint pk_yc_hs_0100 primary key(occur_time) using index tablespace open_sample_index;
   SQL> alter table yc_stat_0100 add constraint pk_yc_stat_0100 primary key(statistics_date,statistics_flag) using index tablespace open_sample_index;

解锁oracle用户

sqlplus sys/platform@o20001 as sysdba
select username,account_status from dba_users where username=‘ems’;
alter user ems account unlock;