Database import with data only; receives ORA-28101

[Millicent Howze-Simmons]

Hi.

Performing an Import on Oracle 10g Release 2.
Table Objects already exist but want data only imported. I receive an error ORA-28101 during import but data is restored.

I am using parameters full=n ignore=y rows=y.

Does anyone know how to import data with table objects already existing in the database ?

[Jeeshan]

Hi

watch this link

http://www.orafaq.com/wiki/Import_Export_FAQ

[TwoProc]

You've got a policy on the table already when it was created, and when importing it's trying to put the policy on again.

Maybe try adding "CONSTRAINTS=N" to your import.

This might turn off the fine grain policy features.

Or.... if it's telling what the table is that's failing the duplicate policy name - you could just turn off the policies for that table.


At this link:
http://www.en8848.com.cn/Reilly%20Books/books/oracle/guide8i/ch07_03.htm
There is an example - but it's for Oracle 8i - check your documentation for a 10g version and see if it's different/better/etc. There may even be a "DISABLE" policy command by now, instead of having to drop the policy that might work for you.

You could use the example below to drop the policy on the table, then do your import. Of course, you'd have to make sure that at the end, the policies are all back from the import after it is finished.

-------------------------------------

The following procedure uses the DBMS_RLS package's DROP_POLICY procedure to drop all policies for a specific schema and database object:

/* Filename on companion disk: droppol.sp */
CREATE OR REPLACE PROCEDURE drop_policy (
objname IN VARCHAR2,
polname IN VARCHAR2 := '%',
objschema IN VARCHAR2 := NULL)
AUTHID CURRENT_USER
IS
BEGIN
FOR rec IN (
SELECT object_owner,
object_name,
policy_name
FROM ALL_POLICIES
WHERE object_owner LIKE NVL (objschema, USER)
AND object_name LIKE objname
AND policy_name LIKE polname)
LOOP
DBMS_RLS.DROP_POLICY (
rec.object_owner, rec.object_name, rec.policy_name);
END LOOP;
END;
/

----------------------------

[Yogeeraj_1]

hi,

As mentioned above, the error message is related to some "policy" existing on the objects you are trying to import.

the simplest way to export/import table data (only) is to specify parameters:

ignore=y
constraints=n
indexes=n
grants=n
tables=('')

The most important check that you must perform is whether the number of records exported is same as the number of records imported.

Question: Why would you want to import data into an existing table?

Good luck!
kind regards
yogeeraj