20210525-PLSQL安装使用与Oracle数据导入

程序员文章站 2022-07-15 15:37:19

...

文章目录

Oracle11g安装

docker版安装：https://hub.docker.com/r/rohitbasu77/oracle11g
ssh连接oracle11g容器

ssh [email protected]_machine_ip -p 40022
password: admin

PLSQL安装步骤

安装oraclePLSQL：https://www.jb51.net/softjc/567468.html
安装 oracle client：https://www.jb51.net/article/170088.htm
oracle client：https://www.jb51.net/database/576109.html
配置plSql
设置oracle client的环境变量

PLSQL如何连接ORACLE

plsql如何连接Oracle：https://ask.csdn.net/questions/382560

测试oracle数据导入

创建表test

create table test
(id number primary key,
name varchar2(20));

使用sqlldr导入oracle

步骤

准备txt数据
编辑ctl文件
执行sqlldr命令

步骤解析

txt数据略
ctl文件编写

load data
CHARACTERSET AL32UTF8
infile '/oracle/IP_trial_single_WGS84.txt'   
append into table IP_BASIC_SINGLE_WGS84
fields terminated by '\t'
optionally enclosed by '"'
(ID,MINIP,MAXIP,CONTINENT,AREACODE,ADCODE,COUNTRY,PROVINCE,CITY,DISTRICT,LNGWGS,LATWGS,RADIUS,ACCURACY,OWNER,ISP,ASNUMBER,SOURCE,ZIPCODE,TIMEZONE)

sqlldr执行命令

/u01/app/oracle/product/11.2.0/xe/bin/sqlldr userid='system/[email protected]' skip=1  control='/oracle/ipv4.ctl' log='/oracle/log.log'

创建索引

使用命令行方式创建索引

#create index 索引名 on 表名 (字段名)
create index idx_minip_maxip on IP_basic_single_WGS84(minip,maxip);

oracle清空表

清空表命令

delete from t
# 执行效率更高
truncate table t

遇到的坑

ctl文件编写，存在需求：1、跳过第一行，2、根据制表符分割字段3、中文字符集不能有乱码4、去掉txt中字段的引号

20210525-PLSQL安装使用与Oracle数据导入

# 解决字符类型
CHARACTERSET AL32UTF8
# 解决以制表符为分隔标志
fields terminated by '\t'
# 去掉txt中的"号
optionally enclosed by '"'
# 跳过第一行
skip=1

服务端中的oracle客户端的具体位置:(oracle11g镜像为例)

/u01/app/oracle/product/11.2.0/xe/network/admin

执行导入命令
- 其中skip=1表示跳过第一行

/u01/app/oracle/product/11.2.0/xe/bin/sqlldr userid='system/[email protected]' skip=1  control='/oracle/ipv4.ctl' log='/oracle/log.log'

表空间不够报错

 Non-data dependent ORACLE error occurred -- load discontinued

解决方案给表空间扩容：https://jingyan.baidu.com/article/414eccf6530b826b431f0ae0.html

ctl文档，包含如何导入的,间隔是什么？字符类型等

  load   data   
  infile   'C:\winssage_deploy\ccp\ch_inf_subscriber\ch_rec_substracnt.txt'   
  append   into   table  CH_REC_SUBSTRACNT
  fields terminated by "|"
  (REGION,IMEI,MAIN_SERVNUMBER1,MAIN_SERVNUMBER1_ARPU,MAIN_SERVNUMBER1_4GFLOW,MAIN_SERVNUMBER1_TOTALFLOW, MAIN_SERVNUMBER1_23GFLOW,MAIN_SERVNUMBER2,MAIN_SERVNUMBER2_ARPU,MAIN_SERVNUMBER2_4GFLOW,MAIN_SERVNUMBER2_TOTALFLOW,MAIN_SERVNUMBER2_23GFLOW,RECMONTH,LOADTIME DATE 'yyyy-mm-dd HH24:mi:ss')

使用sqldr命令进行执行命令
参考文档：https://blog.csdn.net/lld2002/article/details/41014931
sqlldr参考文档以及下载：https://www.jb51.net/database/577355.html#downintro2

Oracle表扩容

oracle表空间查询

SELECT UPPER(F.TABLESPACE_NAME) "表空间名", D.TOT_GROOTTE_MB "表空间大小(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比", F.TOTAL_BYTES "空闲空间(M)", F.MAX_BYTES "最大块(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC;

表空间路径

/u01/app/oracle/oradata/XE

使用ll -h命令可以查看到该目录下有很多.dbf文件

使用sqlplus进入oracle

sqlplus system/oracle

执行扩容命令

SQL> ALTER TABLESPACE system ADD DATAFILE '/u01/app/oracle/oradata/XE/system02.dbf' size 10G;

即在system表空间增加了一个10G大小的数据空间。

Oracle中配置IP转数值类型

oracle中配置IP转数值类型函数，直接将下面代码在PL/SQL中运行----ipv6算法

create or replace function bitmoveleft(a in number, b in number) return number is
  Result number;
begin
   Result := a*power(2,b);
  return Result;
end bitmoveleft;
/

create or replace function bitorzero(a in number,b in number) return number is
  Result number;
begin
  Result:=  a+b;
  return Result;
end bitorzero;
/

CREATE OR REPLACE PACKAGE parse AS
  /*
  || Package of utility procedures for parsing delimited or fixed position strings into tables
  || of individual values, and vice versa.
  */
  TYPE varchar2_table IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
  PROCEDURE delimstring_to_table
    ( p_delimstring IN VARCHAR2
    , p_table OUT varchar2_table
    , p_nfields OUT INTEGER
    , p_delim IN VARCHAR2 DEFAULT ':'
    );
  PROCEDURE table_to_delimstring
    ( p_table IN varchar2_table
    , p_delimstring OUT VARCHAR2
    , p_delim IN VARCHAR2 DEFAULT ':'
    );
END parse;
/


CREATE OR REPLACE PACKAGE BODY parse AS
  PROCEDURE delimstring_to_table
    ( p_delimstring IN VARCHAR2
    , p_table OUT varchar2_table
    , p_nfields OUT INTEGER
    , p_delim IN VARCHAR2 DEFAULT ':'
    )
  IS
    v_string VARCHAR2(32767) := p_delimstring;
    v_nfields PLS_INTEGER := 1;
    v_table varchar2_table;
    v_delimpos PLS_INTEGER := INSTR(p_delimstring, p_delim);
    v_delimlen PLS_INTEGER := LENGTH(p_delim);
  BEGIN
    WHILE v_delimpos > 0
    LOOP
      v_table(v_nfields) := SUBSTR(v_string,1,v_delimpos-1);
      v_string := SUBSTR(v_string,v_delimpos+v_delimlen);
      v_nfields := v_nfields+1;
      v_delimpos := INSTR(v_string, p_delim);
    END LOOP;
    v_table(v_nfields) := v_string;
    p_table := v_table;
    p_nfields := v_nfields;
  END delimstring_to_table;
  PROCEDURE table_to_delimstring
    ( p_table IN varchar2_table
    , p_delimstring OUT VARCHAR2
    , p_delim IN VARCHAR2 DEFAULT ':'
    )
  IS
    v_nfields PLS_INTEGER := p_table.COUNT;
    v_string VARCHAR2(32767);
  BEGIN
    FOR i IN 1..v_nfields
    LOOP
      v_string := v_string || p_table(i);
      IF i != v_nfields THEN
        v_string := v_string || p_delim;
      END IF;
    END LOOP;
    p_delimstring := v_string;
  END table_to_delimstring;
END parse;
/
CREATE OR REPLACE FUNCTION INET_ATON (IP IN VARCHAR2) RETURN INTEGER DETERMINISTIC IS
    DecimalIp INTEGER;
BEGIN
    SELECT SUM(REGEXP_SUBSTR(IP, '\d+', 1, LEVEL) * POWER(256, 4-LEVEL))
    INTO DecimalIp
    FROM dual
    CONNECT BY LEVEL <= 4;
    RETURN DecimalIp;
END INET_ATON;
/
CREATE OR REPLACE FUNCTION INET_NTOA(IpDecimal IN INTEGER) RETURN VARCHAR2 DETERMINISTIC IS

    NUMERIC_OVERFLOW EXCEPTION;
    PRAGMA EXCEPTION_INIT(NUMERIC_OVERFLOW, -1426);

    IP VARCHAR2(16);
    Octet INTEGER;
    v_IpDecimal INTEGER := IpDecimal;
BEGIN
    IF IpDecimal > 2**32 -1 THEN
        RAISE NUMERIC_OVERFLOW;
    END IF;
    FOR i IN 1..4 LOOP
        Octet := TRUNC(v_IpDecimal/256**(4-i));
        v_IpDecimal := v_IpDecimal - Octet * 256**(4-i);
        IP := IP ||'.'||Octet;
    END LOOP;
    RETURN SUBSTR(IP, 2);
END INET_NTOA;
/
create or replace function inet6_aton(ipv6_str_param in varchar2) return NUMBER is

  ip_str        varchar2(40);
  skip_index    integer;
  parts_hi      integer;
  parts_lo      integer;
  parts_skipped integer;
  ip_int        number;

  parts parse.varchar2_table;
  v_nfields integer;
begin
  
  ip_str := ipv6_str_param;
  parse.delimstring_to_table (ip_str, parts, v_nfields);
    
    
    
  skip_index := null;
  for i in 2 .. (parts.count - 1) loop
    if parts(i) is null then
      skip_index := i;
    end if;
  end loop;
  if nvl(skip_index, 0) != 0 then
    parts_hi := skip_index - 1;
    parts_lo := parts.count - skip_index - 1 + 1;
    /*冒号在两头特殊对待*/
    if parts(1) is null then
      parts_hi := parts_hi - 1;
    end if;
    if parts(parts.count) is null then
      parts_lo := parts_lo - 1;
    end if;
    parts_skipped := 8 - (parts_hi + parts_lo);
  else
    parts_hi      := parts.count;
    parts_lo      := 0;
    parts_skipped := 0;
  end if;

  ip_int := 0;
  for i in 1 .. (parts_hi) loop
    ip_int := bitmoveleft(ip_int, 16);
    ip_int := bitorzero(TO_NUMBER(parts(i), LPAD( 'x', lengthb(parts(i)), 'x' )), ip_int);
  end loop;

  ip_int := bitmoveleft(ip_int, 16 * parts_skipped);

  for i in (parts.count - parts_lo + 1) .. (parts.count) loop
    ip_int := bitmoveleft(ip_int, 16);
    ip_int := bitorzero(TO_NUMBER(parts(i), LPAD( 'x', lengthb(parts(i)), 'x' )), ip_int);
  end loop;
  
  return ip_int;
END;
/

ipv4算法

CREATE OR REPLACE FUNCTION INET_NTOA(IpDecimal IN INTEGER) RETURN VARCHAR2 DETERMINISTIC IS

    NUMERIC_OVERFLOW EXCEPTION;
    PRAGMA EXCEPTION_INIT(NUMERIC_OVERFLOW, -1426);

    IP VARCHAR2(16);
    Octet INTEGER;
    v_IpDecimal INTEGER := IpDecimal;
BEGIN
    IF IpDecimal > 2**32 -1 THEN
        RAISE NUMERIC_OVERFLOW;
    END IF;
    FOR i IN 1..4 LOOP
        Octet := TRUNC(v_IpDecimal/256**(4-i));
        v_IpDecimal := v_IpDecimal - Octet * 256**(4-i);
        IP := IP ||'.'||Octet;
    END LOOP;
    RETURN SUBSTR(IP, 2);
END INET_NTOA;
/
CREATE OR REPLACE FUNCTION INET_ATON (IP IN VARCHAR2) RETURN INTEGER DETERMINISTIC IS
    DecimalIp INTEGER;
BEGIN
    SELECT SUM(REGEXP_SUBSTR(IP, '\d+', 1, LEVEL) * POWER(256, 4-LEVEL))
    INTO DecimalIp
    FROM dual
    CONNECT BY LEVEL <= 4;
    RETURN DecimalIp;
END INET_ATON;
/

原理：根据python代码转换过来的

参考文档

sqlldr导入txt处理制表符问题：https://www.cnblogs.com/xuzhong86/p/7636113.html
sqlldr中rows和skip的含义：https://blog.csdn.net/lwei_998/article/details/7730025
sqlldr中时间参数问题：https://blog.csdn.net/weipanp/article/details/42105155
sqlldr用法参考文档：https://blog.csdn.net/lld2002/article/details/41014931
oracle清空表sql语句：https://www.cnblogs.com/jameshappy/archive/2012/09/26/2703412.html
oracle的number类型：https://www.cnblogs.com/kerrycode/p/6957574.html
oracle扩充表空间：https://jingyan.baidu.com/article/414eccf6530b826b431f0ae0.html
sqlldr使用规则：https://www.cnblogs.com/xwb583312435/p/9039931.html
oracle创建索引，删除索引：https://www.cnblogs.com/superming/p/10978639.html

相关标签：日常开发笔记 PLSQL Oracle数据导入

上一篇： Ajax提交数据后回调函数不执行并且Spring mvc控制器无法解析视图解决办法

下一篇： Python基础 - 20210525 -基础12 类和继承

20210525-PLSQL安装使用与Oracle数据导入

文章目录

Oracle11g安装

PLSQL安装步骤

PLSQL如何连接ORACLE

测试oracle数据导入

使用sqlldr导入oracle

步骤

步骤解析

创建索引

oracle清空表

遇到的坑

Oracle表扩容

Oracle中配置IP转数值类型

参考文档

Oracle数据库密码文件的使用与维护

oracle10g 数据备份与导入

在代码生成工具Database2Sharp中使用ODP.NET（Oracle.ManagedDataAccess.dll）访问Oracle数据库，实现免安装Oracle客户端，兼容32位64位Oracle驱动

使用工具 plsqldev将Excel导入Oracle数据库

Oracle 11g数据库安装与卸载的方法图解

oracle安装与备份导入

Oracle数据库备份---导出与导入

Oracle数据的导入与导出

Oracle 使用TOAD实现导入导出Excel数据

Oracle导出表数据与导入表数据dmp