Oracle相关经验整理

imp/exp 示例

exp user/password@ip:1521/sid file=d:\exportname.dmp tables=tablename query=\"where rq=date'2021-12-08'\"
imp user/password@ip:1521/sid file=d:\exportname.dmp fromuser=username tablespaces=tablespaces ignore=y buffer=102400000

按日期循环代码块

DECLARE
  V_DATE DATE;
  I_DATE VARCHAR2(8);
BEGIN
  V_DATE := DATE'2019-01-31';
  WHILE V_DATE <= DATE'2020-01-11'
  LOOP
    I_DATE := TO_CHAR(V_DATE,'YYYYMMDD');
    DBMS_OUTPUT.put_line('I_DATE IS :'||I_DATE);
    --V_DATE := V_DATE +1;  --增加1天
    V_DATE := ADD_MONTHS(V_DATE,1);  --下月的本日，如果循环前是一个月的最后一天，那增加后是下月的最后一天
  END LOOP;
END;

查询数据库的字符集

SELECT USERENV('language') FROM DUAL; 

查询DIRECTORY对应目录

select * from  all_directories;

create or replace directory DOWN_BLOB_DIR as 'G:\test';
grant all on directory DOWN_BLOB_DIR to username;

--select 
select * from user_tab_privs t
where t.table_name = 'DOWN_BLOB_DIR' and t.grantee = 'username'; 

字符串变成列

DECLARE
BEGIN
  FOR REC IN(
    SELECT REGEXP_SUBSTR(
    'NAME1,NAME2,NAME3,NAME4',
    '[^,]+',  --以逗号分割
    1,
    ROWNUM) AS libname  --libname 为后面游标取数时的字段
    from dual 
    connect by rownum <=4  --要根据上面字段个数修改此值
  )
  LOOP
    DBMS_OUTPUT.put_line('name is:'||REC.LIBNAME);
  END LOOP;
END;

显示结果：

name is:NAME1
name is:NAME2
name is:NAME3
name is:NAME4

批量删除及重新创建表分区脚本

--批量删除及重新创建表分区脚本,前提条件表是 按日分区
--大体框架没问题，实际使用时需修改参数后调试
DECLARE
  V_SQL CLOB; --动态sql
  V_CURSOR ALL_TAB_PARTITIONS.PARTITION_NAME%TYPE;
  V_DATE DATE;
  I_DATE VARCHAR2(8);
BEGIN
  --外层循环：遍历表名
  FOR REC IN(
    SELECT REGEXP_SUBSTR(
    'NAME1,NAME2,NAME3,NAME4', --表名
    '[^,]+',  --以逗号分割
    1,
    ROWNUM) AS libname  --libname 为后面游标取数时的字段
    from dual 
    connect by rownum <=4  --要根据上面字段个数修改此值
  )
  LOOP
    DBMS_OUTPUT.put_line('name is:'||REC.LIBNAME);
    --内层循环1：删除分区
    FOR CURSOR_V_PARTITION_NAMES2 IN (
      SELECT T.PARTITION_NAME
         FROM ALL_TAB_PARTITIONS T
         WHERE T.TABLE_OWNER = 'OWNER' --用户名
           AND T.TABLE_NAME = UPPER(REC.LIBNAME)  --表名
           AND (TO_DATE(SUBSTR(T.PARTITION_NAME,6,8),'YYYY-MM-DD') BETWEEN --时间条件，要根据分区名实际调整
               DATE'2021-01-01' AND DATE'2021-02-01')
    )
    LOOP
      V_CURSOR :='';
      V_SQL:='';
      V_CURSOR :=CURSOR_V_PARTITION_NAMES2.PARTITION_NAME; -- 分区名，其实变量可以不用，直接在下面使用游标
      V_SQL :=' ALTER TABLE USER.'||UPPER(REC.LIBNAME)||
              ' DROP PARTITION ' ||V_CURSOR ||
              ' UPDATE GLOBAL INDEXES';
      DBMS_OUTPUT.put_line('V_SQL is:'||V_SQL);     --打印脚本，以便验证无误再执行
      --EXECUTE IMMEDIATE V_SQL; --执行，先注释掉，上面的语句没问题了，再放开   
    END LOOP;  ----内层循环1结束
    
    
    V_DATE:=DATE'2021-01-01';
    I_DATE:='';
    --内层循环2：创建分区
    WHILE V_DATE <= DATE'2021-02-01'
    LOOP
      I_DATE:=TO_CHAR(V_DATE,'YYYYMMDD');
      V_SQL:='ALTER TABLE USER.' || UPPER(REC.LIBNAME)||  --表名
          ' ADD PARTITION DATE_'|| I_DATE ||' VALUES(TO_DATE(''' ||
          I_DATE || ''',''YYYYMMDD''))';
      DBMS_OUTPUT.put_line('V_SQL is:'||V_SQL);     --打印脚本，以便验证无误再执行
      --EXECUTE IMMEDIATE V_SQL; --执行，先注释掉，上面的语句没问题了，再放开   
      
      V_DATE := V_DATE + 1; --重要步骤，否则跳不出循环了
      
    END LOOP; --内层循环2结束
    
  END LOOP;--外层循环结束
END;

并发执行存储过程（非原创）

declare
  I_DATE VARCHAR2(8);--调用存过的入参
begin
  I_DATE :='2021-12-08';
  --p1,p2 为存过过程名, p1有入参和出参的情况
  dbms_scheduler.create_job(job_name   => dbms_scheduler.generate_job_name,
                            job_type   => 'PLSQL_BLOCK',
                            job_action => 'declare o_ret integer; begin p1('''||I_DATE||''',o_ret); end;',
                            start_date => sysdate,
                            enabled    => true,
                            comments   => 'temporary job,will be delete soon after complete!',
                            auto_drop  => true
                            );
  dbms_scheduler.create_job(job_name   => dbms_scheduler.generate_job_name,
                            job_type   => 'PLSQL_BLOCK',
                            job_action => 'begin p2; end;',
                            start_date => sysdate,
                            enabled    => true,
                            comments   => 'temporary job,will be delete soon after complete!',
                            auto_drop  => true
                            );
end;

原版链接：(24条消息) Oracle11gR2 并行执行存储过程_SeanData的博客-CSDN博客_oracle并行执行存储过程

存储过程执行中sleep

dbms_lock.sleep(20); --sleep 20 秒

shell调用存储过程示例

source ~/.bash_profile
export LANG="en_US.UTF-8"
export ORACLE_SID=SID
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

INDATE=$1
echo '传入日期为:'${INDATE}

#判断日期是否符合规范
if [ ${#INDATE} -ne 8 ]; then
	echo "输入的参数不和规范，请输入诸如yyyymmdd的格式"
	exit 1
fi
if [[ ! ${INDATE} =~ ^2[0-9]+$ ]]; then
	echo "输入的参数不和规范，请输入诸如yyyymmdd的格式"
	exit 1
fi

#存过入参为传入参数的下一天

EXECDATE=`date -d"tomorrow ${INDATE}" +%Y%m%d`

echo 'execdate:'${EXECDATE}

#调用数据库存过
sqlplus -S /nolog<<!
conn ${user}/${pwd}@${sid}
SET heading off
SET feedback off
SET pagesize 0
SET verify off
SET echo off
SET linesize 4000
exec proname(${EXECDATE});
exit;
!

if [ $? -ne 0 ];then
  echo "调用存过err"
  exit 2
fi

#用一个变量承接返回值
OKCOUNT=`sqlplus -S /nolog<<!
conn ${user}/${pwd}@${sid}
SET heading off
SET feedback off
SET pagesize 0
SET verify off
SET echo off
SET linesize 4000
SELECT COUNT(*) FROM TABLE T WHERE T.SEE='0';
exit;
!`

echo 'OKCOUNT IS:'${OKCOUNT}

shell查询数据保存到本地文件中

#shell 调用sql 返回值到本地文件
#例如：spool ${dir}/filename.txt 注意要有 spool off
sqlplus -S /nolog<<!
conn ${user}/${pwd}@${sid}
SET heading off
SET feedback off
SET pagesize 0
SET verify off
SET echo off
SET linesize 4000
spool ${dir}/filename.txt
select col1||'|'||col2||'|'||col3 from table1 where rq = to_date('${INPUTDATE}','YYYY-MM-DD') ORDER BY 1;
spool off
exit;
!