Oracle Index – Synchronize periodically

You can create an index which will be synchronized automatically everytime you commit:

or just periodically (p. x. every hour):

See Topic on StackOverflow

Oracle tnsnames configuration

Usually the file is located under:

You can add an entry like this:

If everything is ok you can try to test the connection

Issuing a ping

or login as user

or login as sysdba

Oracle: Exporting/Importing tables containing CLOB data

Exporting the table using SQLWorkbench

Imagine you have a table images with a character column filename and a clob column image_data

First you need to install the SQL-Workbench. Install does just mean to extract the archive in that case.

The following command exports the table into a textfile. It does also generate the loader file which can be used with sqlldr. The CLOB data is exported as files with the name image_data_0, image_data_1, … and will be in the same directory. Those files are referenced in the textfile.

Importing the table using sqlldr

To import the table you need to have the generated control file (image_data.ctl), the textfile (image_data.txt) and all the clob data files (image_data_*).

The command is easy:

The above command assumes that we have a user named user which has the password password. And we assume that there is the server address OURORA configured in the tnsnames.ora

Possible errors

If you get that error delete the ORACLE_HOME environment variable then it should work…

Oracle Collection of Useful Stuff

General pitfalls

  • Every statements should be ended with ;
  • Create Procedure statements shall be ended with a ; and a / on the next line like:
  • Identifiers shall have a maximum of 30 characters, otherwise the code using them fails

Output text from within a script

Execute content of another file

Loads the content of the file script.sql and executes it.

Alter a table in a procedure

If you want to use DDL in a procedure you need to wirte it as Dynamic SQL not Static SQL.

Drop index if exists

Sometimes you don’t want an SQL-Script to raise an exception if you try to delete an index which does not exist. But unfortunately there is no if exists option for the drop index statement. You can however define a procedure for that case:

Drop materialized view if exists

Sometimes you don’t want an SQL-Script to raise an exception if you try to delete a materialized view which does not exist. But unfortunately there is no if exists option for the drop materialized view statement. You can however define a procedure for that case:

Drop view if exists

Sometimes you don’t want an SQL-Script to raise an exception if you try to delete a view which does not exist. But unfortunately there is no if exists option for the drop view statement. You can however define a procedure for that case:

Fixing ORA-29861:
domain index is marked LOADING/FAILED/UNUSABLE

First of all you need to identify the indices. This can be done with the following statement:

The easiest solution to get rid of the error is to drop and recreate those indices

Batch-Script to Startup sqlplus with a connection string

The following script asks for a username and password and does then startup sqlplus logged on with the specified user.

If you want to login as sysdba using the batchscript you need to change the sqlplus command to:

Combining the rows of multiple queries into one resultset

The above query would count all translations in the tables german, french and english

Set a new password for a user

Oracle SQL-Script output

First you need to activate it and call the script:

In the script you can output something with that line:

After the script has been executed you can see the output for example in the Script Output tab of SQLDeveloper.