SQL LOADER 导入含有timestamp的数据

数据中的timestamp字段类似:
Tue Apr 09 18:24:26 CST 2016

LOAD DATA
INFILE ‘infile.dat’
TRUNCATE
INTO TABLE test01 FIELDS TERMINATED BY ‘,’
TRAILING NULLCOLS
(
“DOC_DATE_ADDED” TIMESTAMP WITH TIME ZONE “DY MON DD HH24:MI:SS TZR YYYY”,
)

OSX上安装cx_Oracle

OSX 上安装cx_Oracle

一、下载cx_Oracle 安装包和Oracle Instant Client + SDK

1、cx_Oracle — Source Code Only Option — (http://cx-oracle.sourceforge.net/)
2、Oracle Instant Client Basic 64-bit (http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html)
3、Oracle Instant Client SDK 64-bit (http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html)

二、安装

1、安装环境准备

sudo su
mkdir /Users/Terry/oracle
mv /Users/Terry/Downloads/instantclient-* /Users/Terry/oracle
cd /Users/Terry/oracle
unzip instantclient-basic-macos.x64-11.2.0.3.0.zip
unzip instantclient-sdk-macos.x64-11.2.0.3.0.zip
cd instantclient_11_2/sdk
unzip ottclasses.zip
cd ..
cp -R ./sdk/* .
cp -R ./sdk/include/* .
ln -s libclntsh.dylib.11.1 libclntsh.dylib
ln -s libocci.dylib.11.1 libocci.dylib
vim ~/.bash_profile

.bash_profile中增加如下内容 阅读详细 »

用datapump api来导出表名和字段名区分大小写的数据

由于导出的数据中表名区分大小,需要query的字段包含大小,写expdp的par文件很困难,顺便就学习了一下dbms_datapump包导出数据。
写了一个存储过程,然后用job每天定时导出数据:

create or replace procedure expdp_data
is
h1 number;
v_date varchar2(200);
begin
select to_char(sysdate-1,’YYYYMMDD-HH24:MI’) into v_date from dual;
h1 := dbms_datapump.open(‘EXPORT’,'TABLE’);
sys.dbms_datapump.add_file(h1,v_date||’_%U.dmp’,'EXP_DIR’,NULL,DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE,1);
sys.dbms_datapump.add_file(h1,v_date,’EXP_DIR’,NULL,DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
sys.dbms_datapump.metadata_filter(handle => h1,name => ‘NAME_EXPR’,value => ‘IN (”Oracle_Tablespaces_64”,”Oracle_Tablespaces_64_2”)’,object_type => ‘TABLE’);
sys.dbms_datapump.data_filter(handle => h1,name => ‘SUBQUERY’,value => ‘WHERE substr(“Hub_Timestamp”,2,6)=to_char(sysdate-2,”YYMMDD”)’,table_name=>’Oracle_Tablespaces_64′);
sys.dbms_datapump.data_filter(handle => h1,name => ‘SUBQUERY’,value => ‘WHERE substr(“Hub_Timestamp”,2,6)=to_char(sysdate-3,”YYMMDD”)’,table_name=>’Oracle_Tablespaces_64_2′);
sys.dbms_datapump.set_parameter(h1,’CLIENT_COMMAND’,'EXP Oracle_Tablespaces_64 data’);
sys.dbms_datapump.set_parallel(h1,2);
sys.dbms_datapump.start_job(h1);
sys.dbms_datapump.detach(h1);
DBMS_OUTPUT.PUT_LINE(v_date||”’s date has exported’);
end;
/

阅读详细 »

DB2 Transaction log for the database is full 解决

报错信息:

SQL0964C The transaction log for the database is full

原因分析:

主要问题是数据库的Log file 不够大,可以通过调整LOGFILSIZ、LOGPRIMARY和LOGSECOND的大小解决,但是要注意调整的大小不要超过磁盘空间
计算公式:
磁盘空间的需求=LOGFILSIZ*(LOGPRIMARY+LOGSECOND)*page_size(4k)

具体实施步骤:

阅读详细 »

Linux 下使用Hugepages

为什么要使用HugePages:

1、减少内存页的数量,可以减少内存和CPU资源的使用

2、HugePages的内存,不会被交换出物理内存

Linux X86-64系统下如果配置:

阅读详细 »

Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration

用来计算hugepages的大小的脚本

 MOS ID:ID 401749.1

具体脚本如下:

阅读详细 »

如何在Oracle安装完成后修改oracle_base环境变量

安装完Oracle 软件后 修改数据库ORACLE_BASE环境变量

1、查看当前的ORACLE_BASE

 $ORACLE_HOME/bin/orabase

2、修改ORACLE_BASE变量

 $ORACLE_HOME/bin/orabase -s /oracle

3、查看修改后的ORACLE_BASE

 $ORACLE_HOME/bin/orabase

4、修改oracle用户的profile

重建AWR Repository

由于某个生产库上面awr的信息没有清理掉,导致sysaux表空间占用非常多

于是有了下面这个重建AWR的操作了

1、备份数据库、备份spfile

2、修改spfile的参数,停止awr统计信息的收集

alter system set shared_pool_size = 200m scope = spfile;
alter system set db_cache_size = 300m scope = spfile;
alter system set java_pool_size = 100 scope = spfile;
alter system set large_pool_size = 50 scope = spfile;
alter system set sga_target=0 scope= spfile;
alter system set memory_target=0 scope= spfile;
alter system set statistics_level=basic scope=spfile;

3、关闭数据库并启动到restrict模式
sqlplus /nolog
connect / as sysdba
shutdown immediate
startup restrict

阅读详细 »

Linux Address Space Layout Randomization (ASLR)引起的Oracle的Bug

Linux Address Space Layout Randomization (ASLR)引起的Oracle的Bug

 

报错:Waited for process W001 to initialize for 60 seconds

 

MOS上的信息:

Applies to:

Oracle Database – Enterprise Edition – Version 11.2.0.1 to 12.1.0.1 [Release 11.2 to 12.1]

CRM On Demand – Version N/A to N/A

IBM: Linux on System z

Linux x86-64

Linux x86

Symptoms

Errors are seen in the alert log relating to spawning of processes such as:

@ Checked for relevance on 17th Jan 2012

ORA-00445: background process “m001″ did not start after 120 seconds

Incident details in: /opt/u01/app/oracle/diag/rdbms/incident/incdir_3721/db1_mmon_7417_i3721.trc

ERROR: Unable to normalize symbol name for the following short stack (at offset 2):

Tue Jun 21 03:03:06 2011

ORA-00445: background process “J003″ did not start after 120 seconds

 
 

or

Waited for process W002 to initialize for 60 seconds

 
 

 
阅读详细 »

Flashback Transaction in Oracle 11g

Flashback transaction 可以回退已经提交的事务,并且可以选择是否回退和该事务有关联的数据

启用flashback transaction需要开启数据库的supplemental log(minimal 和 primary)

Sqlplus / as sysdba

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

 

创建测试表

CONN test /test

CREATE TABLE test_tab (

id NUMBER,

description VARCHAR2(50),

CONSTRAINT test_tab_pk PRIMARY KEY (id)

);

 

INSERT INTO test_tab (id, description) VALUES (1, ‘One’);

COMMIT;

INSERT INTO test_tab (id, description) VALUES (2, ‘Two’);

COMMIT;

UPDATE test_tab SET description = ‘One2Three’ WHERE id = 1;

COMMIT;

UPDATE test_tab SET description = ‘Tow2Four’ WHERE id = 2;

COMMIT;

DELETE FROM test_tab WHERE id = 1;

COMMIT;

 

select * from test_tab

    ID DESCRIPTION

———- ——————–

     2 Field

阅读详细 »