Oracle笔记
1.oracle安装配置
单机安装
windows
略
创建空实例：oradim -new -sid orcl
linux
oracle for linux_末点的博客-CSDN博客#groupadd oinstall#groupadd dba#useradd -g oinstall -G dba oracle#passwd oracle#mkdir -p /home/oracle/u01/app/oracle#chown -R oracle:oinstall /home/oracle/u01/#chmod -R 775 /home/oracl
https://blog.csdn.net/MFW333/article/details/52768053
集群安装
windows
略
linux
rhel6.5安装oracle RAC 4节点安装记录_末点的博客-CSDN博客########1.3.2设置ip地址###########################vi /etc/sysconfig/network-scripts/ifcfg-eth0vi /etc/sysconfig/network-scripts/ifcfg-eth1vi /etc/sysconfig/network################################################vi /etc/hosts10.10.10.11 racdb110.10.10.1https://blog.csdn.net/MFW333/article/details/125219896
安装参数
Linux多路径理解
问题
INS-40931
[INS-40931] The following nodes do not have interfaces configured on a subnet matching the SCAN VIP subnet: [node1.domain, node2.domain]
A:Public IP and SCAN VIP should be in same subnet
B:check the /etc/hosts ip and hostname settings.
Oracle 11g RAC 安装数据库软件找不到节点的解决
RHEL7.6安装oracle巨坑记录
CentOS 7 安装oracle 11.2.0.4 Error in invoking target 'agent nmhs' of makefile
CentOS 7 安装oracle 11.2.0.4 Error in invoking target 'agent nmhs' of makefile_末点的博客-CSDN博客%86时出现报错 Error in invoking target 'agent nmhs' of makefile解决方案在makefile中添加链接libnnz11库的参数修改$ORACLE_HOME/sysman/lib/ins_emagent.mk，将$(MK_EMAGENT_NMECTL)修改为：$(MK_EMAGENT_NMECTL) -lnnz11建议修改前备份原始文件[orac...https://blog.csdn.net/MFW333/article/details/80176695
oracle卸载
第一种
# cd /u01/app/oracle/product/11.2.0/client_1/deinstall/
# ./deinstall
# rm -rf /u01/app/oracle
# rm -rf /etc/oratab
# rm -rf /etc/oraInst.loc
第二种
1. 运行 $ORACLE_HOME/bin/localconfig delete
2. rm -rf $ORACLE_BASE/*
3. rm -f /etc/oraInst.loc /etc/oratab
4. rm -rf /etc/oracle
5. rm -f /etc/inittab.cssd
6. rm -f /usr/local/bin/coraenv /usr/local/bin/dbhome /usr/local/bin/oraenv
7. rm –rf /opt/ORCLfmap
第三种
1.删除$ORACLE_BASE/product/oraInventory目录；
2.删除$ORACLE_BASE/product目录；
3.删除/etc/oratab文件；
4.删除/tmp/目录下与"ora"关键字相关的文件；
5.删除/opt/目录下与Oracle相关的内容；
6./usr/local/bin/下的几个文件可以暂不删除。注意在下次安装Oracle运行root.sh脚本提示覆盖文件时选择"y";
7.重新启动操作系统，完成卸载。
第四种
Use DBCA to remove the databases.
Use OUI to remove the installation
Physically remove all the instation from your $ORACLE_HOME and later $ORACLE_HOME itself.
You may want to edit oratab and remove the entries too.
2.数据库管理
启动
windows:
设置实例同windows服务启动
oradim -EDIT -SID 实例名 -STARTMODE auto -SRVCSTART system
linux:
#!/bin/bash
echo 'current user:'$(whoami)
if [ $(whoami) = "oracle" ] ; then
sqlplus / as sysdba <<AAA
startup
exit
AAA
lsnrctl start
elif [ $(whoami) = "root" ];then
echo 'switch to ORACLE user.'
su - oracle <<CCC
sqlplus / as sysdba <<DDD
startup
exit
DDD
lsnrctl start
CCC
fi停止
linux:
#!/bin/bash
echo 'current user:'$(whoami)
if [ $(whoami) = "oracle" ] ; then
sqlplus / as sysdba <<AAA
shutdown immediate
exit
AAA
lsnrctl stop
elif [ $(whoami) = "root" ];then
echo 'switch to ORACLE user.'
su - oracle <<CCC
sqlplus / as sysdba <<DDD
shutdown immediate
exit
DDD
lsnrctl stop
CCC
fi参数设定调整
文件、表空间
系统表空间 SYSTEM迁移
sqlplus "as sysdba" 要系统管理员权限
1.1 查看表空间信息
select TABLESPACE_NAME,FILE_NAME from dba_data_files;
1.2 关闭数据库
SQL> shutdown immediate;
1.3 复制system表空间对应数据文件去新路径(建议使用oracle用户,复制的文件不会有权限问题)
!cp /opt/oracle/oradata/orcl/system01.dbf /data/tb_oracle/system01.dbf
1.4 给新复制的文件修改为原文件所属用户和用户组
!chown chown oracle.oinstall system01.dbf
1.5 以mount启动数据库
SQL> startup mount
1.6 修改system表空间对应数据文件去新路径
SQL> alter database rename file '/opt/oracle/oradata/orcl/system01.dbf' to '/data/tb_oracle/system01.dbf';
1.7 启动数据库
SQL> alter database open;
1.8 确认修改完成
select TABLESPACE_NAME,FILE_NAME from dba_data_files;
UNDO表空间迁移
1.查看undo空间；
show parameter undo_tablespace;
2.查看表空间和文件的对应关系 UNDOTBS1是ONLINE
select file_name, tablespace_name, online_status from dba_data_files where tablespace_name like '%UNDOTBS%';
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS02.DBF UNDOTBS2 ONLINE
3.查询当前回退表空间状态
select tablespace_name, status from dba_rollback_segs;
4.undo_tablespace 是一个必须一直存在的表空间，要想删除当前的，我们必须设置一个临时空间供undo_tablespace 使用；
create undo tablespace UNDOTBS1 datafile 'E:\oradata\UNDOTBS01.DBF' size 100M;
alter system set undo_tablespace=UNDOTBS1;
5.重新查询当前回退表空间状态 UNDOTBS1已经变成OFFLINE
select tablespace_name, status from dba_rollback_segs;
6.删除回退表空间UNDOTBS1
drop tablespace UNDOTBS2 including contents and datafiles;
临时表空间管理
查询默认临时表空间：
SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
查询临时表空间状态:
SQL> select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
扩展临时表空间：
方法一、增大临时文件大小：
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize100m;
Database altered.
方法二、将临时数据文件设为自动扩展：
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' autoextend on next 5m maxsize unlimited;
创建临时表空间：
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 8G AUTOEXTEND ON MAXSIZE 32G;
修改临时表空间大小、自增长等：
ALTER TABLESPACE TEMP1 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 4G AUTOEXTEND ON NEXT 128M MAXSIZE 6G;
设置默认临时表空间：
alter database default temporary tablespace temp2;
删除临时表空间的一个数据文件：
方法1：
SQL> ALTER TABLESPACE TEMP DROP TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf';
注意：这种删除临时表空间的写法会将对应的物理文件删除。
方法2：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ drop;
删除临时表空间(彻底删除)：
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf' DROP INCLUDING DATAFILES;
注意：删除临时表空间的临时数据文件时，不需要指定INCLUDING DATAFILES 选项也会真正删除物理文件，否则需要手工删除物理文件
方法三：
SQL> drop tablespace temp1 including contents and datafiles cascade constraints;
更改用户数据文件位置
1、offline表空间：alter tablespace tablespace_name offline；
2、复制数据文件到新的目录；
3、rename修改表空间，并修改控制文件；
4、online表空间；
操作示例：
1、offline表空间tbs1
select name from v$datafile
alter tablespace tbs1 offline;
2、复制数据文件到新的目录
复制数据文件D:\app\Administrator\oradata\orcl\TBS1.DBF到E:\oradata\TBS1.DBF；
3、rename修改表空间数据文件为新的位置，并修改控制文件
alter tablespace tbs1 rename datafile 'D:\app\Administrator\oradata\orcl\TBS1.DBF' to 'E:\oradata\TBS1.DBF';
4、online表空间
alter tablespace tbs1 online；
select name from v$datafile；
REDO文件管理
一、创建日志文件
1.创建文件组
alter database add logfile group 4('D:\APP\ADMINISTRATOR\ORADATA\redo04.log') size 50M blocksize 512;
2.添加成员
①通过组的成员
alter database add logfile member 'D:\APP\ADMINISTRATOR\ORADATA\redo04a.log' to ('D:\APP\ADMINISTRATOR\ORADATA\redo04.log');
②通过组名
alter database add logfile member 'D:\APP\ADMINISTRATOR\ORADATA\redo04b.log' to group 4;
select group#,member from v$logfile;
二、移动、重命名日志文件
1.关闭数据库
shutdown
2.装载数据库
startup mount
3.移动日志文件
mv D:\APP\ADMINISTRATOR\ORADATA\redo04a.log E:\oradata\redo04a_new.log
4.重命名日志文件
SYS@cdb1> alter database rename file 'D:\APP\ADMINISTRATOR\ORADATA\redo04a.log' to 'E:\oradata\redo04a_new.log';
5.打开数据库
SYS@cdb1> alter database open;
三、删除日志文件
删除日志文件前提：
①一个实例至少需要两组重做日志文件，而不考虑组中的成员数。
②只有当重做日志组处于非活动状态时，才能删除它。如果必须删除当前组，则首先强制进行日志切换。
③在删除重做日志组之前，请确保该组已存档（如果已启用存档）。
查看日志文件状态：
alter system switch logfile;
select * from v$log;
1.删除成员
alter database drop logfile member 'E:\oradata\redo04a_new.log';
select group#,member from v$logfile;
2.删除组
alter database drop logfile group 4;
select * from v$log;
存储管理
表分区移动
ALTER TABLE MINDETAILMOVE PARTITION SYS_P7784 TABLESPACE ANAL ;
查看用户MFW333所用的表空间及其容量：
select owner,tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_segments t where owner='MFW333' group by owner,tablespace_name
查看用户MFW333下所有存储对象及其存储空间大小：
select OWNER,SEGMENT_NAME,t.tablespace_name,t.segment_type,sum(t.BYTES)/1024/1024/1024 GB
from dba_segments t WHERE OWNER='MFW333' GROUP BY OWNER,SEGMENT_NAME ,t.tablespace_name,t.segment_type ORDER BY 2 DESC;
查看表STOCKHIS的所有存储对象和存储空间，主要为数据和索引：
select t.owner,t.segment_name,t.segment_type,T.partition_name,t.tablespace_name,bytes
from dba_segments t LEFT JOIN DBA_INDEXES I ON I.INDEX_NAME=T.segment_name AND T.owner=I.OWNER
WHERE T.segment_name='STOCKHIS' OR I.TABLE_NAME='STOCKHIS';
查看表STOCKHIS所占用的总存储空间大小：
select 'STOCKHIS',sum(bytes)/1024/1024/1024 GB
from dba_segments t LEFT JOIN DBA_INDEXES I ON I.INDEX_NAME=T.segment_name AND T.owner=I.OWNER
WHERE T.segment_name='STOCKHIS' OR I.TABLE_NAME='STOCKHIS';
查看表STOCKHIS存储对象所在的表空间及数据文件：
select t.owner,t.segment_name,t.partition_name,t.segment_type,t.segment_subtype,t.tablespace_name,t.header_file,t.bytes,t.blocks,t.extents,
d.FILE_NAME,d.FILE_ID,d.TABLESPACE_NAME,d.bytes,d.blocks,d.STATUS
from dba_segments t
left join DBA_INDEXES I ON I.INDEX_NAME=T.segment_name --AND t.owner=I.owner
left join DBA_DATA_FILES d on d.FILE_ID=t.header_file
WHERE t.owner=I.owner and T.segment_name='STOCKHIS' OR I.TABLE_NAME='STOCKHIS';
归档
归档设置
查看数据库归档情况：
archive log list
设置归档模式：
关闭数据库：shutdown immediate
启动数据库：startup mount
开启归档模式：alter database archivelog;
设置归档日志大小： system set DB_RECOVERY_FILE_DEST_SIZE= 100G;
打开数据库：alter database open;
查看归档模式情况：archive log list;
归档日志查询：select * from v$recovery_file_dest;
归档日志占比查询：select * from v$flash_recovery_area_usage;归档日志占比查询
详细设置资料：
1. 设置归档日志存储路径
设置归档日志存储路径有两种办法，使用LOG_ARCHIVE_DEST_N或者FRA
1.1使用LOG_ARCHIVE_DEST_N
假定使用了spfile(如果没使用，需要手动配置init<SID>.ora文件), 在SQLPlus里使用alter system命令：
SQL> alter system set log_archive_dest_1='location=/home/oracle/archlog/orcl' scope=both;
SQL> alter system set log_archive_format='orcl_%t_%s_%r.arc' scope=spfile;
SQL> show parameter log_archive_dest
NAME TYPE VALUE
---------------------- ---------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=/home/oracle/archlog/orcl
log_archive_dest_10 string
...
上面命令中的location就表示归档日志的位置。log_archive_format指定了日志格式，%t是线程号，%s是日志序列号，%r是Resetlogs ID，可以自定义格式。
1.2使用FRA
FRA是磁盘上设置的一块区域，不仅可以存储归档日志，还可以存放RMAN备份文件等。
SQL> alter system set db_recovery_file_dest_size=20G scope=both;
SQL> alter system set db_recovery_file_dest='/home/oracle/fra' scope=both;
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
-------------------------- ----------- -----------------
db_recovery_file_dest string /home/oracle/fra
db_recovery_file_dest_size big integer 20G
上面的命令将FRA路径设置为/home/oracle/fra，总大小最多20G
Tip1:如果两者（LOG_ARCHIVE_DEST_N和FRA）都设置了，会归档到哪里？
那么日志只会归档到LOG_ARCHIVE_DEST_N指定的目录里，而不会归档到FRA目录里，如果想要两个地方都归档，可以如下设置
SQL> alter system set log_archive_dest_1='location=/home/oracle/archlog/orcl' scope=both;
SQL> alter system set log_archive_dest_2='location=USE_DB_RECOVERY_FILE_DEST';
Tip2: 已经设置了FRA的情况下，如何取消FRA？
SQL> alter system reset db_recovery_file_dest;
SQL> alter system reset db_recovery_file_dest_size;
2. 查看归档模式
archive log list命令可以看到，使用的是非归档模式：
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/oracle/archlog/orcl
Oldest online log sequence 1
Current log sequence 3
或者使用select log_mode from v$database：
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
3. 启用归档模式
启用归档模式需要把数据库启动到mount状态，然后使用alter database archivelog命令开启：
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
3.1 如果采用的是LOG_ARCHIVE_DEST_N，结果如下:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archlog/orcl
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
在使用了一些日志之后，在/home/oracle/archlog/orcl目录下生成了两个归档文件，名为orcl_1_3_968797779.arc和orcl_1_4_968797779.arc
3.2 如果采用的是FRA，结果如下(另一个系统)：
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
在使用了一些日志之后，生成了一个归档文件，全名为 /home/oracle/fra/ORCL/archivelog/2018_02_23/o1_mf_1_2_f906v40g_.arc
=======
当然，也可以不设置LOG_ARCHIVE_DEST_N或者FRA, 直接开启归档模式，它有默认的目录。
————————————————
***原文链接：https://blog.csdn.net/qingsong3333/article/details/79357377
归档管理
查询Oracle归档空间使用情况: select * from V$FLASH_RECOVERY_AREA_USAGE;
进入rman: rman target /
查看所有日志情况: list archivelog all;
检查一些无用的archivelog: crosscheck archivelog all;
删除截止到前一天的所有archivelog: DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';
删除过期的归档: delete expired archivelog all;
select name from v$archived_log;
list backupset 20;
delete noprompt backuppiece 'D:\ORACLE\RMANBAK\CHIC_30_20111003_3.DBF';
delete backupset 12;
backup device type disk format='/u01/fra/bak/%d_%s_%T_%p' database ;
backup device type disk format='/u01/fra/bak/%d_%s_%T_%p' incremental level 0 database ;
backup device type disk format='/u01/fra/bak/%d_%s_%T_%p' incremental level 1 database ;
backup format='/u01/fra/bak/%d_%s_%T_%p.dbf' incremental level 1 database plus archivelog format='/u01/fra/bak/arc_%d_%s_%T_%p';
backup device type disk format='/u01/fra/bak/%d_%s_%T_%p.dbf' incremental level 1 database plus archivelog format='/u01/fra/bak/arc_%d_%s_%T_%p';
backup device type disk format='/u01/fra/bak/%d_%s_%T_%p.dbf' incremental level 1 database plus archivelog format='/u01/fra/bak/arc_%d_%s_%T_%p' delete input;
3.SQL常用语句
表语句
SELECT
FROM
WHERE
GROUP BY
ORDER BY
表管理
表分析
analyze table DAPAN_2023_11_20 compute statistics for table;
analyze index idx_name compute statistics;
表分区
表迁移表空间
ALTER TABLE tb MOVE TABLESPACE tbs;用户语句
系统语句
编程语句
常用语法
case .. when
for
FOR loop_counter IN [REVERSE] low_value..high_value LOOP
-- statements to be executed
END LOOP;
--简单理解v：
for 变量 in [reverse]小值..大值 loop --（两值之间要有两个点，不能多不能少）
要执行的语句;
[exit when 中途退出的条件;]
end loop;
·加上reverse是从大值循环到小值示例：1
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;示例：2
declare
v2 int:=0;
begin
for a in 1..9 loop
v2:=v2+a;
dbms_output.put(a||'+');
end loop;
dbms_output.put_line('10='||(v2+10));
end;示例：3
declare
cursor c_stk is
select code from stockhis t group by t.code order by t.code asc;
stk c_stk%rowtype;
begin
for stk in c_stk loop
update stockhisavg t set t.avg250=fun_get_stockavgprice(t.code,250,t.day) where t.avg250 is null and t.code=stk.code;
dbms_output.put_line(SQL%ROWCOUNT);
commit;
end loop;
end;函数
例1
create or replace function fun_numberstr(numbers number) return varchar2 is
FunctionResult varchar2(200) :='';
numberstr number;
--根据数值转换为便于理解的亿万千单位
begin
numberstr :=numbers;
if numberstr = null then return '' ; end if;
if numberstr < 0
then
FunctionResult := '负';
numberstr := -numberstr;
end if;
if numberstr > 1000000000000 --//万亿
then
FunctionResult := FunctionResult || Floor(numberstr / 1000000000000) || '万亿';
numberstr := MOD(numberstr,1000000000000);
end if;
if numberstr > 100000000 --//亿
then
FunctionResult :=FunctionResult || Floor(numberstr / 100000000) || '亿';
numberstr := MOD(numberstr,100000000);
end if;
if numberstr > 10000 --//万
then
FunctionResult := FunctionResult || Floor(numberstr / 10000) || '万';
numberstr := MOD(numberstr,10000);
end if;
if numberstr = 0 then
return FunctionResult;
else
return FunctionResult || numberstr;
end if;
return(FunctionResult);
end fun_numberstr;存储过程
例1，无返回值
create or replace procedure pro_macd(s_code VARCHAR2) authid current_user is
str_lastdate varchar2(10) :='';-- :=to_char(sysdate,'yyyy_mm_dd');
Val_last_ema12 number;
Val_last_ema26 number;
Val_last_dea number;
Val_ema12 number;
Val_ema26 number;
Val_dif number;
Val_dea number;
Val_macd number;
Val_price number;
cursor c_stk is
select t.code,t.name,t.day from his t
--where t.code='000025' and (t.code,t.day) not in(select code,day from MACD);
where t.code=s_code and (t.code,t.day) not in(select code,day from MACD) and t.day>'2020-01-01'
order by day asc;
stk c_stk%rowtype;
/**
1、计算移动平均值(EMA)
12日EMA的算式为:EMA(12)=前一日EMA(12)×11/13＋今日收盘价×2/13
26日EMA的算式为:EMA(26)=前一日EMA(26)×25/27＋今日收盘价×2/27
2、计算离差值(DIF)
DIF=今日EMA(12)-今日EMA(26)
3、计算DIF的9日EMA
根据离差值计算其9日的EMA，即离差平均值，是所求的MACD值。为了不与指标原名相混淆，此值又名DEA或DEM。
今日DEA(MACD)=前一日DEA×8/10＋今日DIF×2/10
关键的一点是：新股上市首日，其DIFF,DEA以及MACD都为0，因为当日不存在前一日，无法做迭代
**/
begin
dbms_output.put_line('start:'||s_code||' '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
--s_date :='2015-01-01';
for stk in c_stk loop
--dbms_output.put_line(stk.code||stk.name);
if str_lastdate = '' then
select day into str_lastdate from (select * from macd t where t.day<stk.day order by day desc) r where rownum=1;
end if;
--dbms_output.put_line('str_lastdate:'||str_lastdate);
select t.price,nvl(m.ema12,0),nvl(m.ema26,0),nvl(m.dea,0) into Val_price, Val_last_ema12, Val_last_ema26,Val_last_dea
from his t left join MACD m on t.code=m.code and m.day=str_lastdate--(to_date(m.day,'yyyy-mm-dd hh24:mi:ss')+1)
where t.code=stk.code and t.day=stk.day;
if str_lastdate is null or str_lastdate = '' then
dbms_output.put_line('first day:'||stk.code||stk.day);
--关键的一点是：新股上市首日，其DIFF,DEA以及MACD都为0，因为当日不存在前一日，无法做迭代
Val_ema12 :=0*11/13 + Val_price*2/13;
Val_ema26 :=0*25/27 + Val_price*2/27;
insert into MACD values(stk.code, stk.day,0,0,0,Val_price,Val_ema12,Val_ema26,0,0,0);
commit;
else
Val_ema12 :=Val_last_ema12*11/13 + Val_price*2/13;
Val_ema26 :=Val_last_ema26*25/27 + Val_price*2/27;
Val_dif :=Val_ema12 - Val_ema26;
Val_dea :=Val_last_dea*8/10 + Val_dif*2/10;
Val_macd :=(Val_dif - Val_dea)*2;
insert into MACD values(stk.code, stk.day, Val_last_ema12, Val_last_ema26, Val_last_dea,Val_price,Val_ema12, Val_ema26, Val_dif, Val_dea,Val_macd);
commit;
end if;
str_lastdate :=stk.day;
end loop;
EXCEPTION
when others then
--dbms_output.put_line(sqlcode||':'||SQLERRM);
dbms_output.put_line(SQLERRM);
end pro_macd;
例2，有返回值
create or replace procedure pro_macd_out(s_code VARCHAR2,res out number) authid current_user is
str_lastdate varchar2(10) :='';-- :=to_char(sysdate,'yyyy_mm_dd');
Val_last_ema12 number;
Val_last_ema26 number;
Val_last_dea number;
Val_ema12 number;
Val_ema26 number;
Val_dif number;
Val_dea number;
Val_macd number;
Val_price number;
cursor c_stk is
select t.code,t.name,t.day from his t
--where t.code='000025' and (t.code,t.day) not in(select code,day from MACD);
where t.code like '%'||s_code||'%'
and (t.code,t.day) not in(select code,day from MACD)
--and t.day>'2019-01-01'
order by day asc;
stk c_stk%rowtype;
/**
1、计算移动平均值(EMA)
12日EMA的算式为:EMA(12)=前一日EMA(12)×11/13＋今日收盘价×2/13
26日EMA的算式为:EMA(26)=前一日EMA(26)×25/27＋今日收盘价×2/27
2、计算离差值(DIF)
DIF=今日EMA(12)-今日EMA(26)
3、计算DIF的9日EMA
根据离差值计算其9日的EMA，即离差平均值，是所求的MACD值。为了不与指标原名相混淆，此值又名DEA或DEM。
今日DEA(MACD)=前一日DEA×8/10＋今日DIF×2/10
关键的一点是：新股上市首日，其DIFF,DEA以及MACD都为0，因为当日不存在前一日，无法做迭代
**/
begin
dbms_output.put_line('start:'||s_code||':::'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
--s_date :='2015-01-01';
res :=0;
for stk in c_stk loop
--dbms_output.put_line(stk.code||stk.name);
if str_lastdate = '' then
select day into str_lastdate from (select * from macd t where t.day<stk.day order by day desc) r where rownum=1;
end if;
--dbms_output.put_line('str_lastdate:'||str_lastdate);
select t.price,nvl(m.ema12,0),nvl(m.ema26,0),nvl(m.dea,0) into Val_price, Val_last_ema12, Val_last_ema26,Val_last_dea
from his t left join MACD m on t.code=m.code and m.day=str_lastdate--(to_date(m.day,'yyyy-mm-dd hh24:mi:ss')+1)
where t.code=stk.code and t.day=stk.day;
if str_lastdate is null or str_lastdate = '' then
dbms_output.put_line('first day:'||stk.code||stk.day);
--关键的一点是：新股上市首日，其DIFF,DEA以及MACD都为0，因为当日不存在前一日，无法做迭代
Val_ema12 :=0*11/13 + Val_price*2/13;
Val_ema26 :=0*25/27 + Val_price*2/27;
insert into MACD values(stk.code, stk.day,0,0,0,Val_price,Val_ema12,Val_ema26,0,0,0);
res := res+SQL%ROWCOUNT;
commit;
else
Val_ema12 :=Val_last_ema12*11/13 + Val_price*2/13;
Val_ema26 :=Val_last_ema26*25/27 + Val_price*2/27;
Val_dif :=Val_ema12 - Val_ema26;
Val_dea :=Val_last_dea*8/10 + Val_dif*2/10;
Val_macd :=(Val_dif - Val_dea)*2;
insert into MACD values(stk.code, stk.day, Val_last_ema12, Val_last_ema26, Val_last_dea,Val_price,Val_ema12, Val_ema26, Val_dif, Val_dea,Val_macd);
res := res+SQL%ROWCOUNT;
commit;
end if;
str_lastdate :=stk.day;
end loop;
dbms_output.put_line('totle:'||to_char(res));
EXCEPTION
when others then
--dbms_output.put_line(sqlcode||':'||SQLERRM);
dbms_output.put_line(SQLERRM);
end pro_macd_out;
触发器
数据处理
字符数据
换行符替换
replace(replace(t.code,CHR(10),''),CHR(13),'')
时间数据
去重
SDE数据
shape转坐标串
sde.st_astext(t.shape)
SELECT to_char(substr(sde.st_astext(t.shape),1,20)),t.shape.numpts,sde.st_astext(t.shape),t.* FROM pt T
shape转clob坐标串逗号分隔
ALTER TABLE pt ADD ZBSTR CLOB;
UPDATE pt SET ZBSTR=sde.st_astext(shape);
UPDATE pt SET ZBSTR=REPLACE(ZBSTR,'POLYGON (( ','');
UPDATE pt SET ZBSTR=REPLACE(ZBSTR,'))','');
UPDATE pt SET ZBSTR=REPLACE(ZBSTR, ', ', ',');
UPDATE pt SET ZBSTR=REPLACE(ZBSTR, ' ', ',');
commit;
触发器清理坐标字段内特殊字符
CREATE OR REPLACE TRIGGER tig_dataclean_xx
before update or insert on xx_pg for each row
begin
:new.zbstr := replace(replace(replace(replace(replace(:new.zbstr,CHR(10),''),CHR(13),''),'|',';'),'；',';'),' ','');
end tig_dataclean_xx;存储过程拆分clob字段内包含多个坐标串数据
create or replace procedure pro_SplitZbstr authid current_user is
zbstr1 clob :='';
zbstr2 varchar2(32765) :='';
pos number;
--pos number;
cursor c_row is
select * from pt t;
begin
--create table tmp_zbstr(id nvarchar2(36),mc nvarchar2(200),zbstr clob);
for r in c_row loop
if r.zbstr like '%;%' then
zbstr1:=r.zbstr;
loop
pos:=instr(zbstr1,';');
if pos=0 and length(zbstr1)>0 then
zbstr2:=zbstr1;
else
zbstr2:=substr(zbstr1,1,pos-1);
end if;
zbstr1:=substr(zbstr1,pos+1);
if length(zbstr2)>0 then
insert into tmp_zbstr values(r.id,r.mc,zbstr2);commit;
end if;
dbms_output.put_line(pos);
dbms_output.put_line(zbstr1);
commit;
exit when pos =0;
end loop;
else
insert into tmp_zbstr values(r.id,r.mc,r.zbstr);commit;
commit;
end if;
end loop;
--删除少于３个坐标点的数据
--delete from tmp_zbstr where length(trim(translate(zbstr,' ,0123456789',' ')))<6;
--delete from tmp_zbstr where length(trim(translate(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(zbstr,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','') ,' ,0123456789',' ')))<6;
delete from tmp_zbstr where length(trim(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(zbstr,',',''),'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','')))<6;
commit;
EXCEPTION
when others then
--dbms_output.put_line(sqlcode||':'||SQLERRM);
dbms_output.put_line(SQLERRM);
end pro_SplitZbstr;超长文本格式坐标串数据空间化入库
【项目：st_geometry对象入库】
场景为字符串长度超过4000，无法在数据库内单独处理，需结合编程工具如C#编程读取文本/EXCEL数据，用于将格式如下的数据做空间化入库。
1:POLYGON((12.0041696,22.4982501,12.7017899999999,22.0981097,12.9995904,22.49800979999997,......))以下是数据库端存储过程：
CREATE OR REPLACE PROCEDURE PROC_UPDATE_POLYGON(OBJECTID IN INTEGER,POLYTEXT IN CLOB,res out number)
IS
GEOM SDE.ST_GEOMETRY;
BEGIN
res :=0;
--GEOM:=SDE.ST_POLYFROMTEXT(POLYTEXT,2);
GEOM:=SDE.st_geomfromtext (POLYTEXT,2);
--INSERT INTO GRID (OBJECTID,SHAPE) VALUES(OBJECTID,GEOM);
update GRID t set t.shape=GEOM WHERE id=OBJECTID AND SHAPE IS NULL;
res := SQL%ROWCOUNT;
END PROC_UPDATE_POLYGON;以下是文本格式数据处理的C#主要代码：
private void UPDATE_POLYGON()
{
string filePath = textBox1.Text;
try
{
string[] lines = File.ReadAllLines(filePath);
richTextBox1.AppendText("总行数：" + lines.Length.ToString() + Environment.NewLine);
btnstart.Enabled = btnopenfile.Enabled = false;
oracle ora = new oracle();
int line_total = lines.Length;
int line_id = 1;
int success = 0;
int error = 0;
foreach (string line in lines)
{
//Console.WriteLine(line);
// 在此处可以对每一行的内容进行处理
string[] colsval = line.Split(':'); int id = int.Parse(colsval[3]);
string text1 = colsval[0];
string text2 = colsval[1];
string procname = "PROC_UPDATE_POLYGON";
OracleParameter[] pars = {
new OracleParameter("OBJECTID", OracleDbType.Int32),
new OracleParameter("POLYTEXT", OracleDbType.Clob),
new OracleParameter("res", OracleDbType.Int32),
};
pars[0].Value = id;
pars[0].Direction = ParameterDirection.Input;
pars[1].Value = text1;
pars[1].Direction = ParameterDirection.Input;
pars[2].Value = 0;
pars[2].Direction = ParameterDirection.Output;
int res1=ora.ExecProc(procname, pars);
int res = Int32.Parse(pars[4].Value.ToString());
if (res1 == -2)
{
error++;
Console.WriteLine(line);
string[] zbstr = text1.Split(',');
string[] zbstrnew = new string[zbstr.Length];
for (int i = 0;i<zbstr.Length;i++)
{
string[] z = zbstr[i].Split(' ');
for (int j = 0;j<z.Length;j++)
{
if (!z[j].Contains(".")) continue;
int pos1 = z[j].IndexOf(".");
int pos2 = z[j].Substring(z[j].IndexOf(".")).Length;
if (pos2 >= 8)
z[j] = z[j].Substring(0, pos1 + 8);
else
;
}
zbstrnew[i] = string.Join(" ", z);
}
pars[1].Value = string.Join(",", zbstrnew);
res1 = ora.ExecProc(procname, pars);
res = Int32.Parse(pars[4].Value.ToString());
}
else if (res > 0)
success++;
richTextBox1.AppendText(line_id.ToString() + "/" + line_total + ":::ID:" + id.ToString() + ":::RES:" + res + Environment.NewLine);
Thread.Sleep(10);
line_id++;
}
richTextBox1.AppendText("total:"+ line_total + ":::success:" + success+ ":::error:" + error + Environment.NewLine);
}
catch (Exception ex)
{
Console.WriteLine("文件读取出错：" + ex.Message);
}
btnstart.Enabled = btnopenfile.Enabled = true;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procname">存储过程名称</param>
/// <param name="parameters">存储过程参数</param>
/// <returns></returns>
public int ExecProc(string procname, OracleParameter[] parameters)
{
OracleConnection conn = OpenConn();
if (conn == null) { return -1; }
try
{
var cmd = conn.CreateCommand();
if (parameters != null)
{
// 添加参数
cmd.Parameters.AddRange(parameters);
}
conn.Open();
cmd.CommandText = procname;
cmd.CommandType = CommandType.StoredProcedure;
return cmd.ExecuteNonQuery();
}
catch (Exception e)
{
;
//throw new Exception(e.Message);
//MessageBox.Show(e.Message);
Console.WriteLine(e.Message);
Console.WriteLine("ERROR:" + parameters[0].Value);
return -2;
}
finally { CloseConn(conn); }
}excel格式数据的处理流程相同，只是读取数据的步骤超有差异。
4.备份恢复
EXPDP
EXCLUDE排除表
（windows环境）
expdp ***/***@pdb directory= EXPDP file=expdp.dmp log=expdp.log exclude=TABLE:\"like 'DETAIL_202%'\"
expdp ***/***@pdb directory= EXPDP file=expdp.dmp log=expdp.log exclude=TABLE:\"in (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'ANALYSE_20%' OR TABLE_NAME LIKE 'RES20%' OR TABLE_NAME LIKE 'DETAIL_202%' )\"
IMPDP
EXP
IMP
RMAN
5.工具
1.sqlldr
运行命令：
D:\app\client\Administrator\product\12.2.0\client_1\bin\sqlldr orc/orc@orcl control=I:\stock\files\20231110.ctl bad=I:\stock\files\20231110.bad log=I:\stock\files\20231110.log skip=0 errors=9999 rows=500000 direct=true
20231110.ctl控制文件：
load data
infile 'I:\stock\files\202312157173785.csv'
APPEND
into table STOCKTRADEMINDETAIL2
fields terminated by "," optionally enclosed by ' ' TRAILING NULLCOLS
(code, name, day date "yyyymmdd", time, price, turnover, num, col1, tradetype, mess)202312157173785.csv 数据文件内容：
| 600694 | 大商股份 | 20231215 | 9:15:09 | 17.3 | 15570 | 9 | 0 | 竞价申报 |
| 600698 | 湖南天雁 | 20231215 | 9:15:05 | 5.8 | 4060 | 7 | 0 | 竞价申报 |
| 600696 | 岩石股份 | 20231215 | 9:15:01 | 17.6 | 216480 | 123 | 0 | 竞价申报 |
| 600692 | 亚通股份 | 20231215 | 9:15:06 | 6.85 | 33565 | 49 | 0 | 竞价申报 |
| 600697 | 欧亚集团 | 20231215 | 9:24:02 | 13.39 | 22763 | 17 | 0 | 竞价申报 |
| 600693 | 东百集团 | 20231215 | 9:16:58 | 4.25 | 7225 | 17 | 0 | 竞价申报 |
| 600697 | 欧亚集团 | 20231215 | 9:24:20 | 13.39 | 22763 | 17 | 0 | 竞价申报 |
| 600694 | 大商股份 | 20231215 | 9:15:12 | 17.3 | 15570 | 9 | 0 | 竞价申报 |
| 600696 | 岩石股份 | 20231215 | 9:15:04 | 17.6 | 828960 | 471 | 0 | 竞价申报 |
| 600697 | 欧亚集团 | 20231215 | 9:24:29 | 13.42 | 22814 | 17 | 0 | 竞价申报 |
| 600698 | 湖南天雁 | 20231215 | 9:15:08 | 5.82 | 7566 | 13 | 0 | 竞价申报 |
| 600692 | 亚通股份 | 20231215 | 9:15:09 | 6.91 | 270872 | 392 | 0 | 竞价申报 |
| 600693 | 东百集团 | 20231215 | 9:20:25 | 4.33 | 8660 | 20 | 0 | 竞价申报 |
| 600692 | 亚通股份 | 20231215 | 9:15:12 | 6.93 | 428274 | 618 | 0 | 竞价申报 |
| 600694 | 大商股份 | 20231215 | 9:20:03 | 17.3 | 17300 | 10 | 0 | 竞价申报 |
| 600698 | 湖南天雁 | 20231215 | 9:15:11 | 5.8 | 17400 | 30 | 0 | 竞价申报 |
| 600697 | 欧亚集团 | 20231215 | 9:24:38 | 13.45 | 44385 | 33 | 0 | 竞价申报 |
日志文件结果：
SQL*Loader: Release 12.2.0.1.0 - Production on 星期五 12月 15 23:15:49 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
控制文件: I:\stock\files\20231215.ctl
数据文件: I:\stock\files\202312157173785.csv
错误文件: I:\stock\files\20231215.bad
废弃文件: 未作指定
(可废弃所有记录)
要加载的数: ALL
要跳过的数: 0
允许的错误: 9999
继续: 未作指定
所用路径: 直接
表 STOCKTRADEMINDETAIL2,已加载从每个逻辑记录
插入选项对此表 APPEND 生效
TRAILING NULLCOLS 选项生效
列名 位置 长度 中止 包装 数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
CODE FIRST * , O ( ) CHARACTER
NAME NEXT * , O ( ) CHARACTER
DAY NEXT * , O ( ) DATE yyyymmdd
TIME NEXT * , O ( ) CHARACTER
PRICE NEXT * , O ( ) CHARACTER
TURNOVER NEXT * , O ( ) CHARACTER
NUM NEXT * , O ( ) CHARACTER
COL1 NEXT * , O ( ) CHARACTER
TRADETYPE NEXT * , O ( ) CHARACTER
MESS NEXT * , O ( ) CHARACTER
表 STOCKTRADEMINDETAIL2:
已成功载入 10907479 行。
由于数据错误, 0 行 没有加载。
由于所有 WHEN 子句失败, 0 行 没有加载。
由于所有字段都为空值, 0 行 没有加载。
日期高速缓存:
最大大小: 1000
条目数: 1
命中数 : 10907478
未命中数 : 0
在直接路径中没有使用绑定数组大小。
列数组 行数: 5000
流缓冲区字节数: 256000
读取 缓冲区字节数: 1048576
跳过的逻辑记录总数: 0
读取的逻辑记录总数: 10907479
拒绝的逻辑记录总数: 0
废弃的逻辑记录总数: 0
由 SQL*Loader 主线程加载的流缓冲区总数: 2497
由 SQL*Loader 加载线程加载的流缓冲区总数: 1867
从 星期五 12月 15 23:15:49 2023 开始运行
在 星期五 12月 15 23:16:15 2023 处运行结束
经过时间为: 00: 00: 25.84
CPU 时间为: 00: 00: 12.07
有“AI”的1024 = 2048，欢迎大家加入2048 AI社区
更多推荐
0
0- 0
已为社区贡献2条内容
所有评论(0)