EXP-00008: ORACLE error 6550 encountered

Hi,

Recently I did a full export from an Oracle 10g database using a regular oracle account(not sys or system), and got the following errors:

...
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table DEF$_AQCALL
EXP-00008: ORACLE error 6550 encountered
ORA-06550: line 1, column 18:
PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1204
ORA-06512: at "SYS.DBMS_SQL", line 323
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 97
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 126
ORA-06512: at line 1
. . exporting table DEF$_AQERROR
...
. . exporting table DEF$_ERROR
EXP-00008: ORACLE error 6510 encountered
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 50
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 126
ORA-06512: at line 1
. . exporting table DEF$_LOB 0 rows exported
...

I have found that we need to grant some sys procedures to this user for getting rid of these errors.
So I logged in as sys and executed the following commands:
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO ;
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO ;
And it really helped me to fix it.

Thanks,
Sergey.

How to fix the "Could not open virtual machine" vmware error.

Hello,

Today I got the following error when tried to open a vmware virtual machine:

Could not open virtual machine: C:\vm\cent4\Red Hat Enterprise Linux 4.vmx.
This virtual machine appears to be in use.

To fix this error I have found that it is enough to delete a lck file
from the "C:\vm\cent4\Red Hat Enterprise Linux 4.vmx.lck\" directory.


Thanks,
Sergey.

How to truncate all tables in a schema which contain foreign key constraints:

1. Connect as system to a database, however it is possible to perform these scripts from another account which has got enough privileges.
2. Prepare scripts for disabling and enabling foreign key constraints.
2.1. The first script includes the output of the following query:

SELECT 'ALTER TABLE'||' '||owner||'.'||table_name||' DISABLE CONSTRAINT '||constraint_name||' ;' FROM dba_constraints WHERE constraint_type = 'R' and owner='name of schema' and status = 'ENABLED';

Let's name the output of this query as disable_constraints.sql

2.2. The second script includes the output of a very similar query.

SELECT 'ALTER TABLE'||' '||owner||'.'||table_name||' ENABLE CONSTRAINT '||constraint_name||' ;' FROM dba_constraints WHERE constraint_type = 'R' and owner='name of schema' and status = 'ENABLED';

Let's name the output of this query as enable_constraints.sql

3. Prepare scripts for truncating all tables in the schema.
SELECT 'TRUNCATE TABLE '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='name of schema';

Name the output of this query as truncate_tables.sql

4. Run these scripts in the following order:
@disable_constraints.sql
@truncate_tables.sql
@enable_constraints.sql

That's it.

Thanks,
Sergey.

How to change the location of oraInventory.

If you want to change the location of oraInventory you need just put a new location in the /var/opt/oracle/oraInst.loc (on Solaris) in the inventory_loc variable and move the oraInventory directory from the old place to the new one.

Thanks,
Sergey.

How to import a schema from a dump file and ignore all data in tables.

If you need to import schema without data from a dump file you should follow the following routine:
1. Extract the DDL script of this schema from the dump file:
imp file=exp_file.dmp indexfile=ddl_script.sql userid=system/xxxxxxxx@destination_db fromuser=dmp_user touser=dest_user
Where:
exp_file.dmp – the oracle dump file.
ddl_script.sql – the DDL script which will include all DDL commands of this schema.
xxxxxxxx – system password.
destination_db – an oracle tnsnames alias of the destination database. We could omit it if we are doing import on the same computer where the database is with proper environment variables.
dmp_user – user name in the dump file.
dest_user – user name in the destination database.
2. After the ddl_script.sql file is ready you should remove the REM comments from it.
3. Connect as dest_user to the destination database and execute the ddl_script.sql script. Now we have the non-populated schema without PL/SQL code.
4. Import the dump file on the dest_user schema using the following command:
imp file= exp_file.dmp IGNORE=N userid=system/xxxxxxxx fromuser=dmp_user touser=dest_user
Using IGNORE=N flag we skip all inserts and keep having empty tables however PL/SQL code will be imported.

That’s it.

Thanks,
Sergey.