Unload data to a flat file from Oracle database
From : asktom
[@more@]How Can I unload data to a flat fileMany times we are asked
- "Does anyone know an easy way of dumping the data from an Oracle table into a delimited(comma, tab etc) ascii file?"
- "Does anyone know an easy way to unload data in a format for sqlldr to reload later?"
Well here is a way to do it into Excel or any spreadsheet that understands the industry standard SYLK file format:
Here is a pro*c program that does it to a flat file very fast:
and here is a PLSQL routine that uses utl_file to do the same:
A reader (Andy Rivenes) offers this more "robust" plsql implementation based on the original code
And lastly, SQLPlus can do this quite easily but it is a pain to have to write a script/table. What I've done is setup scripts for UNIX and NT that allow you from the command line to execute things like:
$ sqlldr_exp scott/tiger dept LOAD DATA INFILE * INTO TABLE dept REPLACE FIELDS TERMINATED BY '|' ( deptno ,dname ,loc ) BEGINDATA 10|ACCOUNTING|NEW YORK 20|RESEARCH|DALLAS 30|SALES|RESTON 40|OPERATIONS|BOSTONAs you can see, this script unloaded the scott.dept table into a format that sqlldr can easily reload. All you would need to do is execute:
$ sqlldr_exp scott/tiger dept > dept.ctlto create a control file that can be moved somewhere else and reloaded back into a dept table very quickly.
I also use a slight modification of this script called "flat". Flat does the same thing as sqlldr_exp does mostly except that it dumps the data into a tab delimited format without the sqlldr extras at the top. This makes the extract usable in spreadsheets and such.
In both cases some things you need to be aware of are with regards to this script:
- There is an absolute limit of 2000 bytes in 7.x and 4000 bytes in 8.x per line/row for unloaded data. The total size of the unloaded data is unlimited -- the maximum size of an individual row of data is what is limited.
- It makes no attempt to unload dates with the century or time component -- you must change your default NLS_DATE_FORMAT if this is a problem.
- Beware of data with pipes or tabs in it!
- Beware of data with newlines as well...
- The NT scripts (.cmd files) need modifications if your command line sqlplus is not called SQLPLUS (eg: its plus33 or something similar)
- On NT, you need to set your SQLPATH environment variable and put these files into that directory OR you need to run flat and sqlldr_exp from those directories so sqlplus can find the corresponding flat.sql and sqlldr_exp.sql files.
Alternately, you can get a gzipped tar file for use on Unix without zip.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/66634/viewspace-1013157/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Converting Oracle Database from Linux to Windows using RMANOracleDatabaseLinuxWindows
- Oracle database 升級(文件)to 10.2.0.4 from 10.2.0.1OracleDatabase
- Oracle 12C Database File Mapping for Oracle ASM FilesOracleDatabaseAPPASM
- [20181018]Oracle Database 12c: Data Redaction.txtOracleDatabase
- How To Replicate Data from Oracle to Postgres Using GoldenGate (Doc ID 1544137.1OracleGo
- oracle ORA-01157: cannot identify/lock data file 64OracleIDE
- Oracle設定日誌引數-ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;OracleDatabase
- oracle 匯入報錯:field in data file exceeds maximum lengthOracle
- oracle12.2 adg ORA-46952: standby database format mismatch for password fileOracleDatabaseORM
- The database owner SID recorded in the master database differs from the database owner SID recorded in database 'DB_NAME'DatabaseAST
- ORA-16649: possible failover to another database prevents this database from beiAIDatabase
- Oracle 19c透過recover standby database from service修復GAP案例OracleDatabase
- get_data_from_yml
- Oracle 業務資料unload恢復過程Oracle
- Export/import Datas To/from a Csv FileExportImport
- ORACLE database vaultOracleDatabase
- Oracle clone databaseOracleDatabase
- Oracle Database Cloud - Database as a Service Quick StartOracleDatabaseCloudUI
- Unreal: Dynamic load map from Pak fileUnreal
- Oracle Database Scheduler整理OracleDatabase
- Oracle Physical Database LimitsOracleDatabaseMIT
- OAF export data from VO in xlsx formatExportORM
- Oracle 12.2 使用Database Link優化Standby Database WorkloadOracleDatabase優化
- 【等待事件】SQL*Net more data from dblink事件SQL
- ELF file data encoding not little-endianEncoding
- Oracle 19c Concepts(01):Introduction to Oracle DatabaseOracleDatabase
- Oracle 19c Concepts(13):Oracle Database InstanceOracleDatabase
- Oracle OCP(35):Database 安裝OracleDatabase
- oracle 10g flashback databaseOracle 10gDatabase
- Oracle DG Standby Database型別OracleDatabase型別
- Oracle DG建立Physical Standby DatabaseOracleDatabase
- Oracle DG建立Logical Standby DatabaseOracleDatabase
- Oracle data link建立Oracle
- Oracle 建立PDB-from ScratchOracle
- Oracle:db file scattered readOracle
- Oracle OCP(38):Database 物理結構OracleDatabase
- 關於Oracle Database Vault介紹OracleDatabase
- Scheduler in Oracle Database 10g(轉)OracleDatabase
- [翻譯]-Detect And Repair Corruption in an Oracle DatabaseAIOracleDatabase