Home » RDBMS Server » Server Utilities » IMPDP full tablespace (11.2.0.1.0)
IMPDP full tablespace [message #457701] Tue, 25 May 2010 12:14 Go to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
I'm trying to move a complete tablespace, with all associated schemas/users and data.

The command that I'm using is as follows:

impdp DP_USER/DP_USER directory=datapumps network_link=backup tablespaces=SDE


DP_USER has the IMP/EXP_FULL_DATABASE, as does the target of the database link.

The error that I get is the following:

Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"SDE"."VERSION" failed to create with error:
ORA-01918: user 'SDE' does not exist


The SDE tablespace has objects from the SDE schema, but the SDE schema does not seem to be created along with the tablespace. How do I cause it to be?



Re: IMPDP full tablespace [message #457703 is a reply to message #457701] Tue, 25 May 2010 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> How do I cause it to be?
CREATE USER SDE IDENTIFIED BY PASSWORD;
Re: IMPDP full tablespace [message #457704 is a reply to message #457703] Tue, 25 May 2010 12:23 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Alright, that would work - but I actually have many tablespaces and users. I'd rather something that is a little more generic. Is that possible?
Re: IMPDP full tablespace [message #457705 is a reply to message #457704] Tue, 25 May 2010 12:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> Is that possible?
yes, for some definition of "possible"
A Small Matter Of Programing (SMOP) to generate desired code.
Re: IMPDP full tablespace [message #457706 is a reply to message #457705] Tue, 25 May 2010 12:32 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Seems to be a chicken and egg deal.

I can't import the schema without the tablespace and I can't import the tablespace without the schema.

The schemas have relatively involved permissions and grants etc, so I don't want to create those from scratch.

If I have to create something from scratch I suppose I'd take the tablespaces.

From my reading it seems to be possible to do both within the database pump framework, but it seems either I'm mistaken or you don't know.
Re: IMPDP full tablespace [message #457708 is a reply to message #457706] Tue, 25 May 2010 12:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
QL> select 'CREATE USER ' || USERNAME || ' IDENTIFIED BY PASSWORD;' FROM DBA_USERS;

'CREATEUSER'||USERNAME||'IDENTIFIEDBYPASSWORD;'
------------------------------------------------------------------
CREATE USER MGMT_VIEW IDENTIFIED BY PASSWORD;
CREATE USER SYS IDENTIFIED BY PASSWORD;
CREATE USER SYSTEM IDENTIFIED BY PASSWORD;
CREATE USER DBSNMP IDENTIFIED BY PASSWORD;
CREATE USER SYSMAN IDENTIFIED BY PASSWORD;
CREATE USER BONGO IDENTIFIED BY PASSWORD;
CREATE USER SH IDENTIFIED BY PASSWORD;
CREATE USER HR IDENTIFIED BY PASSWORD;
CREATE USER DBADMIN IDENTIFIED BY PASSWORD;
CREATE USER OUTLN IDENTIFIED BY PASSWORD;
CREATE USER FLOWS_FILES IDENTIFIED BY PASSWORD;

'CREATEUSER'||USERNAME||'IDENTIFIEDBYPASSWORD;'
------------------------------------------------------------------
CREATE USER MDSYS IDENTIFIED BY PASSWORD;
CREATE USER ORDSYS IDENTIFIED BY PASSWORD;
CREATE USER EXFSYS IDENTIFIED BY PASSWORD;
CREATE USER WMSYS IDENTIFIED BY PASSWORD;
CREATE USER APPQOSSYS IDENTIFIED BY PASSWORD;
CREATE USER APEX_030200 IDENTIFIED BY PASSWORD;
CREATE USER OWBSYS_AUDIT IDENTIFIED BY PASSWORD;
CREATE USER ORDDATA IDENTIFIED BY PASSWORD;
CREATE USER CTXSYS IDENTIFIED BY PASSWORD;
CREATE USER ANONYMOUS IDENTIFIED BY PASSWORD;
CREATE USER XDB IDENTIFIED BY PASSWORD;

'CREATEUSER'||USERNAME||'IDENTIFIEDBYPASSWORD;'
------------------------------------------------------------------
CREATE USER ORDPLUGINS IDENTIFIED BY PASSWORD;
CREATE USER OWBSYS IDENTIFIED BY PASSWORD;
CREATE USER SI_INFORMTN_SCHEMA IDENTIFIED BY PASSWORD;
CREATE USER OLAPSYS IDENTIFIED BY PASSWORD;
CREATE USER SCOTT IDENTIFIED BY PASSWORD;
CREATE USER ORACLE_OCM IDENTIFIED BY PASSWORD;
CREATE USER XS$NULL IDENTIFIED BY PASSWORD;
CREATE USER BI IDENTIFIED BY PASSWORD;
CREATE USER PM IDENTIFIED BY PASSWORD;
CREATE USER MDDATA IDENTIFIED BY PASSWORD;
CREATE USER IX IDENTIFIED BY PASSWORD;

'CREATEUSER'||USERNAME||'IDENTIFIEDBYPASSWORD;'
------------------------------------------------------------------
CREATE USER DIP IDENTIFIED BY PASSWORD;
CREATE USER OE IDENTIFIED BY PASSWORD;
CREATE USER APEX_PUBLIC_USER IDENTIFIED BY PASSWORD;
CREATE USER SPATIAL_CSW_ADMIN_USR IDENTIFIED BY PASSWORD;
CREATE USER SPATIAL_WFS_ADMIN_USR IDENTIFIED BY PASSWORD;



after doing CREATE USER .... as primed from SQL above,
then impdb .... TABLESPACE= should succeed.
Re: IMPDP full tablespace [message #457711 is a reply to message #457708] Tue, 25 May 2010 12:51 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
And then afterwards I'd need to setup all the permissions and assigned tablespaces?

i.e.

DROP USER SDE CASCADE;

CREATE USER SDE
  IDENTIFIED BY <>
  DEFAULT TABLESPACE SDE
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 15 System Privileges for SDE 
  GRANT CREATE SESSION TO SDE;
  GRANT ADMINISTER DATABASE TRIGGER TO SDE;
  GRANT CREATE TYPE TO SDE;
  GRANT CREATE PROCEDURE TO SDE;
  GRANT DROP PUBLIC SYNONYM TO SDE;
  GRANT CREATE SEQUENCE TO SDE;
  GRANT CREATE PUBLIC SYNONYM TO SDE;
  GRANT CREATE TABLE TO SDE;
  GRANT CREATE OPERATOR TO SDE;
  GRANT CREATE INDEXTYPE TO SDE;
  GRANT CREATE VIEW TO SDE;
  GRANT CREATE LIBRARY TO SDE;
  GRANT CREATE TRIGGER TO SDE;
  GRANT SELECT ANY TABLE TO SDE;
  GRANT UNLIMITED TABLESPACE TO SDE;
  -- 2 Object Privileges for SDE 
    GRANT DELETE, INSERT, SELECT, UPDATE ON SDE_MDR.SERVER_CONFIG TO SDE;
    GRANT DELETE, INSERT, SELECT, UPDATE ON SDE_WEBMAP.SERVER_CONFIG TO SDE;



Perhaps instead of TABLESSPACES=... I should instead use FULL=Y and INCLUDE statements to specify the tablespaces and users? Playing with that syntax now.

[Updated on: Tue, 25 May 2010 12:56]

Report message to a moderator

Re: IMPDP full tablespace [message #457715 is a reply to message #457701] Tue, 25 May 2010 13:09 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
It appears that this syntax does exactly what I want:

impdp DP_USER/DP_USER directory=datapumps network_link=backup full=y INCLUDE=TABLESPACE:\"LIKE \'SDE\'\" INCLUDE=USER:\"LIKE \'SDE\'\" remap_datafile=\'D:\ORACLE\ORADATA\NETECHORATEST\SDE.DBF':\'C:\app\tmcallister\oradata\tmcall11gr2\SDE.DBF\'


Remapping the datafiles is slightly frustrating, especially since they appear to be case sensitive, but obviously required in this case due to a different disk structure. Oracle should be able to handle tablespaces & users, and it does.
Re: IMPDP full tablespace [message #457718 is a reply to message #457715] Tue, 25 May 2010 13:48 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
... or not. It runs so it must work ...

This created the base structures (schema & tablespace) but none of the permissions, objects, etc. I probably need to flesh out the INCLUDE statements a little more. Any tips appreciated.
Re: IMPDP full tablespace [message #457723 is a reply to message #457718] Tue, 25 May 2010 14:20 Go to previous message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Looks like I should of just kept researching myself Razz, anyway for anyone for whom this happens to come up in a search with I suggest:

(0) INCLUDE SCHEMAS as opposed to USERS. Using USERS seems to result in only the most basic user being created with that name/password(no objects, permissions [not even connect]).
(1) using a parfile (removes the need to add in escape characters for quotes).

Here is the final parfile that I used:
directory=datapumps
network_link=backup
full=y
INCLUDE=TABLESPACE:"IN ('SDE','SDE_WEBMAP_GEODB')"
INCLUDE=SCHEMA:"IN ('SDE','WEBMAP_XBROKER')"
remap_schema=USERS:SDE_WEBMAP_GEODB
remap_datafile=D:\ORACLE\ORADATA\NETECHORATEST\SDE.DBF:C:\app\tmcallister\oradata\tmcall11gr2\SDE.DBF
remap_datafile=D:\ORACLE\ORADATA\NETECHORATEST\SDE_WEBMAP_GEODB:C:\app\tmcallister\oradata\tmcall11gr2\SDE_WEBMAP_GEODB.DBF


Previous Topic: SQL loader question
Next Topic: Oracle jobs appear to be running but.....
Goto Forum:
  


Current Time: Tue Sep 28 20:17:18 CDT 2021