oracle

import data file into Oracle database, using SQL*Loader utility … sqlldr … sql loader

by

use exp

For example, in the following example, benefits table has a column called v_status. This exp command will export only the rows that contains “INVALID” as value in the v_status column.

$ exp hradmin/mypassword TABLES=benefits query=\"where v_status=\'INVALID\'\"
..
About to export specified tables via Conventional Path ...
. . exporting table BENEFITS 20783 rows exported

 

SQL*Loader (sqlldr) is the utility to use for high performance data loads. The data can be loaded from any text file and inserted into the database.

螢幕快照 2014 09 03 上午10 42 12

Read more

[memo] Oracle : 把資料用 UTF8 編碼存進 table / NLS_LANG / NCHAR / NVARCHAR / NLS_CHARACTERSET / NLS_NCHAR_CHARACTERSET / SQL plus

by

UTF8 sample:

螢幕快照 2014-10-28 下午9.21.47

URL : http://www.unicode.org/cgi-bin/GetUnihanData.pl?codepoint=竝

螢幕快照 2014-10-28 下午9.28.02

Oracle : 把資料用16進位印出來 dump(field,16)
螢幕快照 2014-10-28 下午8.30.11

螢幕快照 2014-10-28 下午8.35.36

 

用 locale -a 看 系統支援的 locales

參考 : Oracle® Database Globalization Support Guide : http://docs.oracle.com/cd/E11882_01/server.112/e10729/toc.htm

Read more

oracle export / import command

by

exp userid=user/password file=/path/database_$(date +%Y%m%d).dmp log=/path/database_$(date +%Y%m%d).log ROWS=Y

imp user/password file=database_20100914.dmp fromuser=user1 touser=user2 grants=n ignore=y feedback=1000000

 

2017.0716

imp tsdb/password file=TBL_STORE_DAY_2015_201703291490748896.dmp ignore=y feedback=1000000 FULL=y

root@ora11xe:/data# imp tsdb/password file=XXXX.dmp ignore=y feedback=1000000 FULL=y

Import: Release 11.2.0.2.0 - Production on Mon Jul 17 20:16:45 2017

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


Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by ZZZZ, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export server uses UTF8 NCHAR character set (possible ncharset conversion)
. importing ZZZZ's objects into TTTT
. importing ZZZZ's objects into TTTT
. . importing table         "XXXX"
..................................................
                                                     50040378 rows imported
Import terminated successfully without warnings.