Oracle XE-Data Uploading from CSV file

This is a step-by-step guide on the simplest of the simplest ways by which data can be uploaded from CSV file to Oracle table. I have done uploading using this way for as many as 10,000 records without any performance issues. It went very swiftly.

Login to OracleXE first and follow the instructions below:

Step 1 - Click Utilities

Step 2 - Click Data Load/Unload

Step 3 - Click Load

Step 4

Step 5 - Click Load Spreadsheet Data

Step 6 - Follow the screen

Step 7 - Follow the screen

Step 8 - Follow the screen

Step 9 - Follow the screen

Step 10 - Follow the screen

Oracle Software Patching Using OPatch

The OPatch utility is a tool that allows the application and rollback of interim patches to Oracle products.

Patches are a small collection of files that are copied over an existing installation. They are associated to particular versions of Oracle Products. Patches, when applied to the correct version of an installed product, results in an upgraded version of the product.Interim patches are bug fixes that are made available to customers in response to specific bugs. They require a particular base release or patchset to be installed before they can be applied. They generally address specific bugs for a particular customer. These patches are not versioned and are generally made available in a future patchset as well as the next product release.

About OPatch

OPatch is an Oracle supplied utility to assist you with the process of applying interim patches to Oracle's software. OPatch is a Java-based utility which requires the Oracle Universal Installer to be installed. It is platform independent and runs on all supported operating systems.

OPatch supports the following:

• Applying an interim patch.

• Rolling back the application of an interim patch.

• Detecting conflict when applying an interim patch after previous interim patches have been applied. It also suggests the best options to resolve a conflict.

• Reporting on installed products and interim patch.

Requirements for OPatch

The OPatch utility requires the following:

• The Oracle home environment variable (ORACLE_HOME) must point to a valid Oracle home directory and match the value used during installation of the Oracle home directory.

• Java SDK 1.4 or higher, Java commands for Windows and ar, cp, fuser, and, make commands for UNIX must be made available.

• The library path must be set correctly for Oracle Real Application Clusters environments. OPatch uses some APIs to detect if the system is Real Application Clusters. Ensure that the library path is set correctly as follows:

  For Solaris LD_LIBRARY_PATH = $ORACLE_HOME/lib32:$ORACLE_HOME/lib For HP-UX -  SHLIB_PATH=$ORACLE_HOME/lib32:/usr/lib

For the latest information about the OPatch utility, and to check for updates, and to get latest versions refer to OracleMetaLink at

http://www.oracle.com/support/metalink/index.html or here

How can I identify which index represents which primary or unique key constraint?

The connection between constraints and the indexes which are used to check these constraints for the current user can be described by this query:

        select  --+ rule
          o.owner index_owner,
          o.object_name index_name,
          n.name constraint_name
        from sys.cdef$ c, dba_objects o, sys.con$ n
        where c.enabled = o.object_id
        and c.con# = n.con#
        and n.owner# = uid;

If you leave away the condition and n.owner# = uid you get all the constraints. You may further limit this query to your constraint name by adding the condition and n.name = 'your_constraint_name'.

Why can indexes and constraints be so different? In particular, you may use for example an index on columns (c, a, b) to enable a unique constraint on columns (a, b, c). Remember a constraint is a logical structure whereas an index is a physical one. So a unique or a primary constraint just describe the uniqueness. If (c, a, b) is unique then all other permutations are unique as well.

Further, these indexes may also be non-unique. You need this if you have a deferred constraint that is checked only at commit time. If you would insist on a unique index the attempt to insert duplicate values would fail before the commit although another command may have undone the duplicate entry.

This original query was contributed to comp.databases.oracle.server by Thomas Kyte.

Tips on exp and imp

Export Tips:

* Use buffer parameter while using export and import by atleast 5000000( 5MB ) which will increase the performance of exp and imp by 3 folds. (conventional path)
* Take care with NLS_LANG. It must match with V$NLS_PARAMETERS.NLS_CHARACTERSET.
* For Speedy Exports set parameter db_file_multiblock_read_count =128. (OS Dependent)
* The Export parameter BUFFER applies only to conventional path Exports. For direct path Export, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file.
* Use direct mode export (direct=Y).
* RECORDLENGTH recommended values: Multiples of the file system I/O block size, Multiples of DB_BLOCK_SIZE


Import Tips:

* Use indexes=n to ignore the index importing.
* Using indexfile to create the index to file, create the indexes after the importing using script file.
* Using rows=n indexes=y to import index in a separate import action.


Example:

exp eva6004/eva6004@uwms file=eva6004.dmp log=eva6004.log direct=y recordlength=640000 statistics=none

imp eva6004/eva6004@uwm file=eva6004.dmp fromuser=eva6004 touser=eva6004 indexes=n

imp eva6004/eva6004@uwm file=eva6004.dmp fromuser=eva6004 touser=eva6004 indexes=y rows=n

Oracle SQL Developer v 1.1 Evaluation Release 3 - Known Issues

Contents

1. General
2. Connections
3. Creating and Modifying Objects
4. PL/SQL
5. Preferences
6. Reports
7. Schema Diff
8. Linux Only Issues
9. Oracle 8.1.7 (Not Certified)

1. General

• Settings from previous release not migrated
• Extensions for SQL Developer 1.0 no longer work 1.1.
• You can't invoke SQL*Plus for users with TNSNAMES Connections.
• Query Builder
  • Where Clause for a self referencing table is incorrect.
  • Tables are not listed alphabetically.
  • You can't always drag a table onto the white space. Workaround: Close and reopen the Query Builder.
  • Two consecutive queries give an Ora error. Workaround: Add ';' to the first, before adding second query.
• Icons to be updated for production release.
  • PL/SQL icons require more definition
• There is an error when you add External Tools. This is not causing any SQL Developer errors. You can ignore it.

2. Connections

• Cannot connect to remote database as OPS$ account.
• Import Connection: Does not permit replacing existing connections.

3. Creating and Modifying Objects

• For Functions, Packages & Procedures: Invalid icons not always shown for invalid objects.
• Create new Database Link. Failure occurs if you drop and try to recreate a database link with the same name.
• It is not possible to create a second index for a materialized view.
• Importing Data into Table using Excel spreadsheet has some issues.

4. PL/SQL

• Can't save spec and body in same file.
• Remote debugging works only with IP, not with hostname.
• In some instances compiling PL/SQL results in a 'token' error.

5. Preferences

• File Types: Associate File Extension With SQL Developer, Preference is not saved.(Windows only)
• Database - > Worksheet parameters Unable to run using @ or Start when you set the working directory. Workaround: Use the full path.

6. Reports

• SQL Developer hangs or throws an exception when you rerun the Sessions report.

7. Schema Diff

• The Tools menu now shows an item "Schema Diff". This is not functional yet.

8. Linux Only Issues

• Browsing
  • Views > Data Tab - Actions not working.
• Connections
  • Core dump while expanding a connection created using connection identifier.
  • New connection using connect identifier gives ocijdbc10 error.
• SQL Worksheet
  • Describe - F4 key does not work correctly.

9. Oracle 8.17 (NOTE: SQL Developer is only certified for database 9.2.0.1 and above)

• There are general issues with browsing 8.1.7 objects.

Courtesy: http://www.oracle.com/technology/products/database/sql_developer/files/knownIssues_v1_1.html

Download Oracle Sql Developer here