Tag: oracle

I encountered an issue while importing a oracle dump, and a nice workaround.

The situation:

We have two oracle users, each one with a specific tablespace (ORI_TSDATA for user ORI on ORI_SID, TARGET_TSDATA for user TARGET on TARGET_SID).
I want to export a dump of tables and data from user1 and import it inside user2 tablespace.

The export is made with the exp command (details here).

exp USERID=ORI/ORI_PASSWORD@ORI_SID File=ori.dmp LOG=export.log

The import is performed with

imp USERID=TARGET/TARGET_PASSWORD@TARGET_SID File=ori.dmp LOG=import.log FROMUSER=ORI TOUSER=TARGET

The issue:

Although we us the FROMUSER and TOUSER parameters in the import, the import utility is unable to migrate some tables to a different tablespace.

The migration works for tables using standard columns types, but not with the ones that contain LOB data (Binary objects).
In fact, the import utility tries to recreate the tables that contain BLOB in ORI_TSDATA (the exported tablespace) that does not exist in the target system.

IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "BINARY_CONTENTS" ("ID" NUMBER(19, 0) NOT NULL ENABLE, "CONTEN"
"T" BLOB NOT NULL ENABLE, "MIME_TYPE" VARCHAR2(255 CHAR) NOT NULL ENABLE, "F"
"ILE_NAME" VARCHAR2(255 CHAR) NOT NULL ENABLE, "FILE_SIZE" NUMBER(10, 0) NOT"
" NULL ENABLE)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIA"
"L 131072 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ORI"
"_TSDATA" LOGGING NOCOMPRESS LOB ("CONTENT") STORE AS  (TABLESPACE "ORI_"
"TSDATA" ENABLE STORAGE IN ROW CHUNK 16384 PCTVERSION 10 NOCACHE LOGGING  ST"
"ORAGE(INITIAL 81920 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'ORI_TSDATA' does not exist

In fact, if you visualize the dump file, you will notice that LOB create statements explicitly mention the target Tablespace (ie the one you exported from) while other create statement do not mention them.

The official fix:

We should create a tablespace similar to the exported one (ORI_TSDATA) in the target server and migrate the tables to the target tablespace (TARGET_TSDATA) after the import. This cannot always be done, for example if ORI_TSDATA is already used for another purpose on the target server.

The workaround:

We can force the import to TARGET_TSDATA:

• create the table structure in TARGET_TSDATA prior to importing the dump
• use the ignore=yes parameter at import time

The import utility will ignore the warnings, and use the existing tables in TARGET_TSDATA to populate the data.

Use the ddl_create_tables.sql script if you have one or retrieve thetable creation orders from the dump otherwise.

imp TARGET_PASSWORD@TARGET_SID File=ori.dmp LOG=import.log FROMUSER=ORI TOUSER=TARGET IGNORE=y

Another workaround

We could edit the dump file with an editor like VI and replace the target tablespace names inside.

For all tables from the SCOTT schema

EXEC dbms_stats.gather_schema_stats(’SCOTT’, cascade=>TRUE);

For the table EMP of SCOTT schema

EXEC dbms_stats.gather_table_stats(‘SCOTT’,’EMP’,cascade=>TRUE);

References:

http://www.oradev.com/create_statistics.jsp

# ——————————————————————————————-
Principle:

• Step 1: the generate_drop_all.sql script is used to generate drop statements for all objects that belongs to the current user. Theses statements are generated into drop_all_objects.sql
• Step 2: the drop_all_objects.sql script is used to drop everything prior to import
• Step 3: import…

Details:

# Step 1 :Move to the /tmp directory
# Copy the generate_drop_all.sql file here
# Generate the drop script
exit | sqlplus target_user/target_password @generate_drop_all.sql

# -> a new file drop_all.sql is generated

# Step2 : Run the drop script
exit | sqlplus target_user/target_password @drop_all_objects.sql

# Step3 : Import the dump
imp userid=target_user/target_password file= the_dump_file.dump log=the_log_file.log fromuser=origin_user_name touser=target_user_name

Content of the generate_drop_all.sql file

set feedback off
set trimspool on
set echo off
set verify off
set linesize 200
set pagesize 0
spool drop_all_objects.sql

select ‘drop ‘ || object_type || ‘ ‘ || object_name || ‘ cascade constraints;’
from user_objects
where object_type in (‘TABLE’);

select ‘drop ‘ || object_type || ‘ ‘ || object_name || ‘;’
from user_objects
where object_type not in (‘TABLE’, ‘INDEX’, ‘PACKAGE BODY’, ‘LOB’);

select ‘commit;’
spool off

Import an Oracle dump to oracle XE in 3 steps:

• Create a directory to contain a new tablespace
• Create a dedicated tablespace and user that reflects the name of the ones used to export
• Import the dump into this new tablespace

Detailled instructions

————————————————
Create a directory for Data (tablespace)
———————————————–

mkdir C:\myDataFiles

———————————————————
Create a TAB_CS tablespace

Use exactly this name as it will be requested for import
Connect to XE as System user (use SQL developper for instance)
and run the following script and COMMIT !
———————————————————–

CREATE  TABLESPACE TAB_CS
DATAFILE 'C:\myDataFiles\TAB_CS.ora'
SIZE 10M
AUTOEXTEND ON NEXT 5M;
 
CREATE  USER MyUser
IDENTIFIED BY MyPass
DEFAULT TABLESPACE TAB_CS
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK
QUOTA UNLIMITED ON TAB_CS;
 
GRANT CONNECT TO MyUser;
GRANT RESOURCE TO MyUser;
GRANT DBA  TO MyUser;

—————-
Import the DUMP
—————-
Copy the dump file to c:\TheDumpFIle.dump

Open windows command prompt (Start/Run/cmd) and run:

C:\oraclexe\app\oracle\product\10.2.0\server\BIN\imp.exe M260/M260 file=TheDumpFIle.dump full=yes

Import should run without any warning

Verify that everything is OK ussing an Oracle client connect as MyUservia SQL developper and check tables

I spent quite a time searching for a way to restart an oracle server yesterday… without being root.

For the record, here is what I did:

Start the listener:

/home/ORACLE/product/9.2.0/bin/lsnrctl start

Start the instance:

sqlplus /nolog
SQL> conn /AS sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

I checked that it runs via :

ps -ef |grep smon
myUser 25942     1  0 16:24:48 ?        0:00 ora_smon_NAMEOFTABLESPACE
myUser 26297  9609  0 16:31:14 pts/4    0:00 grep smon