Oracle - copying tables from one schema to another

There may well be other methods but this works for me.

This example copies some tables ('MY_TABLE1','MY_TABLE2',',MY_TABLE3') from a schema  named 'SRC_SCHEMA' to a schema 'DEST_SCHEMA'

1) Create a parameter file (called PARS.txt) describing the tables you want to copy:
   a) using a LIKE

schemas=SRC_SCHEMA dumpfile=some_tables.dmp INCLUDE=TABLE:"LIKE 'MY_TA_%'"

   b) here is an example listing specific tables

schemas=SRC_SCHEMA dumpfile=some_tables.dmp INCLUDE=TABLE:"IN ('MY_TABLE_1','MY_TABLE_2','MY_TABLE_3')"

2) export these tables using expdp

expdp system/password PARFILE=PARS.txt

3) import these tables from the created dump file using impdp.

NB1: there are other options to the parameter TABLE_EXISTS_ACTION. This one will simply overwrite what is there.

NB2: the example shows the remapping of two TABLESPACES as well

impdp system/password TABLE_EXISTS_ACTION=REPLACE dumpfile=some_tables.dmp REMAP_SCHEMA=SRC_SCHEMA:DEST_SCHEMA  REMAP_TABLESPACE=SRC_TSPACE1:DEST_TSPACE1   REMAP_TABLESPACE=SRC_TSPACE2:DEST_TSPACE2

oracle - listing locks & removing them

While logged in as sysadmin, SQL to list any locks on a particular table:

SELECT SID,SERIAL# 
FROM V$SESSION 
WHERE SID IN (SELECT SESSION_ID 
FROM DBA_DML_LOCKS 
WHERE NAME = 'SOME_TABLE_NAME');

SQL to remove a specific lock, using the values from the above SQL:

ALTER SYSTEM KILL SESSION 'SID,SERIALl#';

SQLdeveloper & showing the full date

I keep getting this every time I install SQLDeveloper.

The date shown is just dd/mm/yyyy with no hours:minutes:seconds

The solution can be found here on Zenx's blog, but to save me looking here it is also.

1. From SQL Developer, open menu Tools >> Preferences.
2. From the Preferences dialog, select Database >> NLS Parameters from the left panel.
3. From the list of NLS parameters, enter DD-MON-RR HH24:MI:SS into the Date Format field.
4. Save and close the dialog, done!

Thanks Zenx.

Also, if you want milli-seconds add ':SSSSS' at the end.

oracle - listing all tables in a schema

select table_name,

to_number( extractvalue( xmltype(

dbms_xmlgen.getxml('select count(*) c from '||table_name))

,'/ROWSET/ROW/C')) count

from user_tables;

courtesy of Burleson

AmbiguousTableNameException in dbUnit

There is plenty of documentation on AmbiguousTableNameException in dbunit but I was unable to find a clear example showing the problem and the solution.

We have an application that needs to work on Oracle & SQLServer. The integration tests make extensive use of dbunits dataset classes to populate test databases. The tests clear out various tables & populate the tables as required. Previous to me being involved there would be only one database that had to be populated for use and cleaned out to run the tests. I found this a pain so I created a test database for the integration test use only.

SQL Server continued to work fine however when running the Oracle tests I got lots of AmbiguousTableNameExceptions being thrown. It took me a good while to make the connection as to the cause despite it being written in black & white.

Our code was creating the DatabaseConnection as follows:

DataSource ds = (DataSource) applicationContext.getBean(getDataSourceName());
DatabaseConnection mDbConnection = new DatabaseConnection(getConnection(ds));

Logically this should be absolutely fine. First off it all worked before. The Datasource fully describes the connection. In the debugger I could see the username, password and connection url. Accessing the database from the command line using the same parameters all looked cool. Upgrading to dbunit 2.2 proved fruitless.

So why would the connection come up with an AmbiguousTableNameException when that user has no privileges to see any other schema? The long answer to that defeats me.
However as I said the solution is there on the dbunit website.

Despite the fact that the connection has everything defined you still need to tell the DatabaseConnection object the schema name. In the constructor pass in the Schema name and all will be fine.

DatabaseConnection mDatabaseConnection = new DatabaseConnection(connection, getSchemaName());

This all appears to be a bug in dbunit and potentially a security flaw as it implies a connection with limited access can see other schemas.

importing oracle dumps using imp or impdp

Using Oracle Enterprise Manager I was trying to import a DMP file into an oracle database & I got the error:

Import Submit Failed
There is a problem reading from the import files: ORA-39143: dump file "c:\temp\case9_0.DMP" may be an original export dump file .

There appears to be two import tools and Enterprise Manager only knows about 'impdp'. There is the old import tool 'imp' that is needed if the dump file was created using 'exp' as opposed to 'expdp' or via Enterprise Manager.

The solution.
Simply run:

imp user/password

where user & password are for the user in the dump file and it will prompt you for all the answers.

Using the ruby oracle_adapter on Windows XP

I have been having some problems getting my windows install of ruby to connect to oracle. It is so much easier on ubuntu. Looking at the blog of a friend Andrew Beacock other people have had the same problem, admittedly a while ago.

I tried the gem install command:

gem install activerecord-oracle-adapter

with no luck.

I tried

gem install activerecord-oracle-adapter --source http://gems.rubyforge.org

also with no luck.

Then I tried this. I am sure it is not the purist solution but it works !!!
Download the oracle_adapter.rb

Then copy this into the relevant directory under where you have ruby installed.

C:\ruby\lib\ruby\gems\1.8\gems\activerecord-2.0.2\lib\active_record\connection_adapters

And bobs your father's brother ...

My database.yml file looks like this:

development:
adapter: oracle
database: my_sid
username: my_user
password: my_password