createtable test
(id number primarykey,
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)
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>ALTERTABLESPACE 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;foriin2..(parts.count - 1) loop
if parts(i) is null then
skip_index := i;
end if;
end loop;if nvl(skip_index, 0)!=0then
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;foriin1..(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);foriin(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;
/