This content has been marked as final. Show 10 replies
These are my Import Parameters:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Produc
With the Partitioning, OLAP and Data Mining options
Import file: EXPDAT.DMP > D:\oracle dump files\Data_Backup.dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no > no
Ignore create error due to object existence (yes/no): no > yes
Import grants (yes/no): yes > yes
Import table data (yes/no): yes > yes
Import entire export file (yes/no): no > yes
we tried using Export and Import using Data Pump.
expdp system/password@orcl full=y directory=dmpdir2 dumpfile=FULL_DB.dmp
impdp system/password@orcl full=y table_exists_action=truncate directory=dmpdir2 dumpfile=FULL_DB.dmp
Still the same result as using exp and imp. _LT tables have data but when you query using the View, no results are found.
There's a few things you can do:
Check for the views validity: select status from dba_objects where object_type = 'VIEW'; Maybe all it needs is a recompile.
Check for the WM privileges (maybe the privileges did not export/import well: select * from ALL_WORKSPACE_PRIVS; )
Documentation says:Did you already have version-enabled tables or workspaces in the database you imported in?
A database with version-enabled tables can be exported to another Oracle database only if the other database has Workspace Manager installed and does not currently have
any version-enabled tables or workspaces (that is, other than the LIVE workspace).
Besides, on a more general note: What is it you are trying to achieve? [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11826/long_repl.htm#i634199]Replication? A backup? OTA? Export/Import may not necessaruly be the best way to what you are trying to achieve, that's why I'm asking.
We are actually trying to refresh the data of our QA/Dev server.
QA/Dev Server does not have workspaces and version-enabled tables.
Production Server contains workspaces and version-enabled tables.
Also, before we did the import in the QA/Dev server, we dropped the user with cascade options to ensure that there won't be any conflicts.
Upon checking my WM Views for my version-enabled tables, they are valid. But ALL_WORKSPACE_PRIVS and ALL_WM_VERSIONED_TABLES does not contain data.
rcx4009v2 wrote:Well, there you go. WM does not know of any versioned tables, so the whole thing does not work properly. Try exporting and importing including the WMSYS-schema, while specifying
But ALL_WORKSPACE_PRIVS and ALL_WM_VERSIONED_TABLES does not contain data.
table_exists_action=truncateThat way WM's metadata will come across as well, which it is missing now.
What version of Workspace Manager are you using? Note that the source and target databases currently need to be the same version of Workspace Manager. This version can be found in the dba_registry or wm_installation view. In addition, the target database cannot have any existing workspaces or version enabled table.
If those match, then I would check the import log for errors related to the WMSYS schema. Most likely there is some type of error preventing the import of the WMSYS metadata. It is not necessary to explicitly include the WMSYS schema, as it will be included in any FULL database export.
So I managed to successfully Export/Import Full database with Version Enabled tables to my other server.
What I did is the following:
1. Export using EXP SYS/password@ORCL_TNS FULL=Y FILE=FULL_DB.DMP from the source Server
2. I then Dropped the User for my Schema and then Drop WMSYS user both with cascade options in my destination Server.
3. I re-installed Workspace Manager as indicated in Oracle Documentation (http://docs.oracle.com/cd/B14117_01/appdev.101/b10824/long_inst.htm)
4. After reinstalling Workspace Manager, I then Imported my dump file using IMP SYS/password@ORCL_TNS FULL=Y IGNORE=Y FILE=FULL_DB.DMP
After these steps, I can now query using the Views of my tables.
Thank you all for the support.