Friday Jan 10, 2014

US Census CSV data to DB12c SQL*Loader

Do you know that DB12c sqlldr is a little smarter...finally ? Now, *.ctl file is not always necessary.

SQL*Loader Express

In express mode, a SQL*Loader control file is not used.

So I did a small experiment.
Here's US Census Bureau data in csv format.

As is often the case, 1st line describes data content and real data begins from 2nd line.
One can pass the 1st line to sqlplus and all the rest to sqldr in 1 pass.
And sqlldr can read from standard input for over many releases.
So, here's my quick&dirty one liner.

$ curl --silent  --proxy your_proxy:8888 \
> 'http://www.census.gov/popest/data/state/asrh/2013/files/SCPRC-EST2013-18+POP-RES.csv' |
> (   \
>   read line;echo $line |
>   sed 's/^/CREATE TABLE OVER18POP (/;s/,/ varchar2(32),/g;s/$/ varchar2(32));/' |
>   sqlplus scott/tiger;  \
>   sqlldr scott/tiger table=OVER18POP data=\'-\'  \
> )

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 9 15:54:05 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Thu Jan 09 2014 15:49:02 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
Table created.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL*Loader: Release 12.1.0.1.0 - Production on Thu Jan 9 15:54:06 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: OVER18POP
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file -.dat
ORA-01031: insufficient privileges
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
Express Mode Load, Table: OVER18POP
Path used:      Direct

Load completed - logical record count 53.

Table OVER18POP:
  53 Rows successfully loaded.

Check the log file:
  OVER18POP.log
for more information about the load.

Wednesday Nov 20, 2013

キャパシティ・プラニングのマニュアル

先日紹介した本は "Forecasting .. Performance" という題名なので、....

統計的手法が出てくるOracle本 (INOUE Katsumi @ Tokyo)

Forecasting Oracle Performance: Craig Shallahamer: 9781590598023: Amazon.com: Books

日本語に訳すと "パフォーマンス予測" とかになると思いますが、同じような分野の作業として"キャパシティ・プラニング" が挙げられると思います。

キャパプラはSIerさんとかコンサル部門とかが行う場合が多いと思うのでOracleのマニュアル製品には出てこないだろうと思いますがたまたま見つけました。

キャパシティ プランニング プロセス

テスト結果を分析する前に、リトルの法則に基づいて検証し、そのテスト設定におけるボトルネックを特定する必要があります。

旧 BEA 時代のマニュアルですがOracle Databaseでの検証にも当てはまる部分が多くなかなか勉強になりました。

なお、上記ページにいくつか競合製品が出てきますが現在では全て弊社製品で賄えるはずです。

 

 

About

Personal View of a Sales Engineer in Tokyo.

Search

Archives
« May 2016
SunMonTueWedThuFriSat
1
3
4
6
7
8
9
10
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    
       
Today