oracle external table and sqlload

external table, just like a normal database table, has field and data type constraints, and can be queried, but the data in the table is not stored in the database, but in the ordinary external file associated with the database. When you query the external table, Oracle parses the file and returns data that matches the criteria.

oracle

External table external attributes

Default Directory: Specifies the default directory of the external table.

Directory: Set an external directory.

Location: Sets the external source location.

Access Driver: Set the access driver for the external table, and the default type of the external table is Oracle Loader.

Denial limit: Specifies the limit on the number of errors that can occur in an external data query.

Parallel to Degrees: Select this option to enable parallel query and specify the number of degrees for parallel access at the data source.

oracle

External table access parameters

Describe external data to oracle

Correspondence of database data columns.

Use clob: Check this option to get the value of the clob data returned to the query.

oracle

Indexes organize tables

An index organization table is a basic b-tree

Variant of the preservation organization, the data of the index organization table is saved in a b-tree index structure sorted by primary keys. Key and non-key columns are stored in each branch block in the index structure.

The index organization table has the function of a complete table, supports functions such as limits and triggers, and additional functions such as key compression. It's important to note that the Table Designer for indexed organization tables and regular tables is only different from the Options tab.

Summary of Oracle's use of SQLLDR:

Import data across databases: When you import data from database A to database B, you can use the sqlldr command.

Benefits: Even if the structure of a tablea in database A is different from that in database B, you can still import data from table A to table B. The premise is that the control file needs to be written.

Advantages: Tablea and Tableb data fields can be different.

Cons: Requires some control files.

When pouring data out of database A, it is best to replace the data with carriage return and line entry in the field content, and remove the carriage return line feed. Because later, when importing data, one row of data is used as the import unit. The syntax is as follows:

replace(replace(content,chr(10),''),chr(13),''Among them, CHR(10) and CHR(13) are carriage returns and line breaks.

It can be said: simple, fast, and with many online resources.

sqlload has been developed and applied more extensively, and this tool is relatively simple and practical. Flat file data that corresponds to a large number of formats can be quickly imported to Oracle. When importing, pay attention to the configuration of tables and control files.

sqlldr userid=test/test@testdb control= direct=true

The best way to load or import text data into a database.

It should be that the table structure of the server is different from your local one.

Displays the number of submissions that have reached the maximum number of submissions, mostly because the number of rejected entries exceeds the upper limit and the import is not allowed to continue.

It is recommended to compare the field lengths.

Is it because there's a space in the middle of the interval1 number?

You delete the space and try it when you're done.

I'm just guessing, and if it's wrong, I hope forgiveness.

Also, there should be a ")" at the end, right?