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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Unload data from oracleOracle
- External Tables: Querying Data From Flat Files in OracleOracle
- Oracle 11g Data Guard 使用 duplicate from active database 建立 standby databaseOracleDatabase
- Oracle 11g Data Guard 使用duplicate from active database 建立 standby databaseOracleDatabase
- Oracle 11g Data Guard 使用duplicate from active databaseOracleDatabase
- 搭建11g data guard(duplicate from active database方式)Database
- How to migrate data from Oracle to MSSQLSERVEROracleSQLServer
- How to get ORACLE_HOME from data dictionaryOracle
- Export data from a data blockExportBloC
- Oracle Database Change Data Capture featureOracleDatabaseAPT
- Upgrade Oracle Database from 10.2.0.1 to 11.2.0.4OracleDatabase
- Oracle11g RMAN Duplicate from Active DatabaseOracleDatabase
- Oracle database 升級(文件)to 10.2.0.4 from 10.2.0.1OracleDatabase
- Migrate database from single instance to Oracle RACDatabaseOracle
- [轉載]Oracle等待事件Data file init writeOracle事件
- Oracle Database 11gR2引入的DBFS(Database File System)OracleDatabase
- Oracle 12C Database File Mapping for Oracle ASM FilesOracleDatabaseAPPASM
- DBMS_FILE_TRANSFER Package in Oracle Database 10gPackageOracleDatabase
- Oracle Database on NFS : unable to lock file - already in use" ErrorOracleDatabaseNFSError
- Oracle的SQL*Net more data from client 等待事件分析OracleSQLclient事件
- reduce the database log fileDatabase
- Get data style from ExcelExcel
- get_data_from_yml
- Converting Oracle Database from Linux to Windows using RMANOracleDatabaseLinuxWindows
- Converting Oracle Database from Windows to Linux using RMANOracleDatabaseWindowsLinux
- Upgrading from Oracle Database 10g to 11gOracleDatabase
- Export/import Datas To/from a Csv FileExportImport
- Move datafile:From File System to ASMASM
- Move datafile:From ASM to File SystemASM
- It is indirectly referenced from required .class fileUI
- [轉]How to release space from databaseDatabase
- oracle 匯入報錯:field in data file exceeds maximum lengthOracle
- 【蓋國強】Oracle Wait Event:Data file init writeOracleAI
- oracle10g 資料檔案頭data file header(file header)OracleHeader
- oracle10g data guard(dg)__flashback_physical databaseOracleDatabase
- Transparent Data Encryption (TDE) in Oracle 10g Database Release 2Oracle 10gDatabase
- SQL*Net more data from clientSQLclient
- SQL SERVER – Attach mdf file without ldf file in DatabaseSQLServerDatabase