Home » RDBMS Server » Server Utilities » Import schema into a newly created user (oracle, 10g, windows xp)
Import schema into a newly created user [message #556706] Wed, 06 June 2012 04:24 Go to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi All,

I need a clarification on the below query:

1) DROP USER MK CASCADE;

2) Created user

3) Created objects like procedure,index... and granted privileges.

4) Now i am performing the import as below.

impdp system/.... SCHEMAS=MK DIRECTORY=EXPBKUP DUMPFILE=ABC_Export.dmp LOGFILE=ABC_imp.log INCLUDE=TABLE TABLE_EXISTS_ACTION=REPLACE


But nothing is imported.

What is went wrong?

Is this the problem of the parameter "INCLUDE=TABLE TABLE_EXISTS_ACTION=REPLACE"? as the user is new.


Regards
Muktha


Re: Import schema into a newly created user [message #556717 is a reply to message #556706] Wed, 06 June 2012 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You know what you have to post and how...

Regards
Michel

[Updated on: Wed, 06 June 2012 04:55]

Report message to a moderator

Re: Import schema into a newly created user [message #556719 is a reply to message #556717] Wed, 06 June 2012 05:04 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi Michel,

Steps are:

1) I dropped a existing user.

DROP USER <name> CASCADE;

2) Now I am creating the user again and grant also.

CREATE USER CDSL
  IDENTIFIED BY <password>
  DEFAULT TABLESPACE CDSL
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 2 Roles for CDSL 
  GRANT DBA TO CDSL WITH ADMIN OPTION;
  GRANT ISSLAPPS TO CDSL WITH ADMIN OPTION;
  ALTER USER CDSL DEFAULT ROLE ALL;
  -- 10 System Privileges for CDSL 
  GRANT SELECT ANY TRANSACTION TO CDSL;
  GRANT DEBUG ANY PROCEDURE TO CDSL;
  GRANT QUERY REWRITE TO CDSL;
  GRANT CREATE DATABASE LINK TO CDSL;
  GRANT DEBUG CONNECT SESSION TO CDSL;
  GRANT CREATE MATERIALIZED VIEW TO CDSL;
  GRANT UNLIMITED TABLESPACE TO CDSL WITH ADMIN OPTION;
  GRANT CREATE JOB TO CDSL;
  GRANT EXECUTE ANY PROCEDURE TO CDSL;
  GRANT CREATE VIEW TO CDSL;
  -- 4 Tablespace Quotas for CDSL 
  ALTER USER CDSL QUOTA UNLIMITED ON USERS;
  ALTER USER CDSL QUOTA UNLIMITED ON CDSL;
  ALTER USER CDSL QUOTA UNLIMITED ON INDXUAT;
  ALTER USER CDSL QUOTA UNLIMITED ON CDSL_INDX;
  -- 42 Object Privileges for CDSL 
    GRANT EXECUTE ON  CAT.ORA_CHK_MNTH_STATUS TO CDSL;
    GRANT EXECUTE ON  CAT.ORA_GENERATE_TRAN_NUMBER_CDSL TO CDSL;
    GRANT INSERT, SELECT, UPDATE ON  CAT.TR_LAST_VCH TO CDSL;
    GRANT INSERT, SELECT ON  CAT.TR_VCH_DTL TO CDSL;
    GRANT INSERT, SELECT ON  CAT.TR_VCH_HDR TO CDSL;
    GRANT SELECT ON  CDSLWEB.SEQ_TRANSNUMBER TO CDSL;
    GRANT INSERT, SELECT, UPDATE ON  CDSLWEB.TBL_DEPOSITORYCLIENT TO CDSL;
    GRANT INSERT, SELECT, UPDATE ON  CDSLWEB.TBL_DEPOSITORYGROUP TO CDSL;
    GRANT SELECT, UPDATE ON  CDSLWEB.TBL_PLEDGETRANSACTION TO CDSL;
    GRANT INSERT, SELECT, UPDATE ON  CDSLWEB.TBL_POADETAILS TO CDSL;
    GRANT SELECT, UPDATE ON  CDSLWEB.TBL_TXNDTLS TO CDSL;
    GRANT SELECT, UPDATE ON  CDSLWEB.TBL_TXNMSTR TO CDSL;
    GRANT SELECT, UPDATE ON  CDSLWEB.TBL_TXNMSTR1 TO CDSL;
    GRANT SELECT, UPDATE ON  CDSLWEB.TBL_TXNSMMRY TO CDSL;
    GRANT SELECT, UPDATE ON  CDSLWEB.TBL_UPLOAD_DETAILS TO CDSL;
    GRANT SELECT ON  DP2BOSS.TBL_BPMASTER TO CDSL;
    GRANT SELECT ON  DP2BOSS.TBL_CCCALENDAR TO CDSL;
    GRANT SELECT ON  DP2BOSS.TBL_DAILYPRICELIST TO CDSL;
    GRANT SELECT ON  DP2BOSS.TBL_ISINMASTER TO CDSL;
    GRANT SELECT ON  DPBOSS.MV_UAPA_UPLOADDTL TO CDSL;
    GRANT SELECT ON  DPBOSS.TBL_BPMASTER TO CDSL;
    GRANT SELECT ON  DPBOSS.VW_KYC_CDSL TO CDSL;
    GRANT SELECT ON  SYS.DBA_REFRESH TO CDSL;
    GRANT EXECUTE ON  SYS.DBMS_IREFRESH TO CDSL;
    GRANT EXECUTE ON  SYS.DBMS_ISCHED TO CDSL WITH GRANT OPTION;
    GRANT EXECUTE ON  SYS.DBMS_LOB TO CDSL;
    GRANT EXECUTE ON  SYS.DBMS_OUTPUT TO CDSL;
    GRANT EXECUTE ON  SYS.DBMS_REFRESH TO CDSL;
    GRANT EXECUTE ON  SYS.DBMS_SCHEDULER TO CDSL WITH GRANT OPTION;
    GRANT EXECUTE ON  SYS.DBMS_SNAPSHOT TO CDSL;
    GRANT EXECUTE ON  SYS.DBMS_SYS_ERROR TO CDSL;
    GRANT EXECUTE ON  SYS.DBMS_XMLGEN TO CDSL;
    GRANT EXECUTE ON  SYS.DBMS_XMLQUERY TO CDSL;
    GRANT EXECUTE ON  SYS.DBMS_XMLSAVE TO CDSL;
    GRANT EXECUTE ON  SYS.DBMS_XMLSTORE TO CDSL;
    GRANT SELECT ON  SYS.SESSION_INFO TO CDSL;
    GRANT SELECT ON  SYS.SESSION_STAT TO CDSL;
    GRANT EXECUTE ON  SYS.UTL_FILE TO CDSL;
    GRANT EXECUTE ON  SYS.UTL_HTTP TO CDSL;
    GRANT EXECUTE ON  SYS.UTL_MAIL TO CDSL;
    GRANT EXECUTE ON  SYS.UTL_SMTP TO CDSL;
    GRANT EXECUTE ON  SYS.UTL_TCP TO CDSL;


3) Now I am performing the IMPORT.

impdp system/<password> SCHEMAS=CDSL DIRECTORY=EXPBKUP DUMPFILE=CDSL_Export.dmp LOGFILE=CDSL_imp.log  INCLUDE=TABLE TABLE_EXISTS_ACTION=REPLACE



4) The user saying, he haven't got the datas. and Missing many objects like procedures, indexes...

5) What went wrong.

Regards
Muktha
Re: Import schema into a newly created user [message #556724 is a reply to message #556719] Wed, 06 June 2012 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
5) What went wrong.


1/ What you posted
2/ Fire the user who is to dump to see his data
3/ No more problem

Regards
Michel
Re: Import schema into a newly created user [message #557333 is a reply to message #556724] Tue, 12 June 2012 00:14 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi Michel,

Logically there is no problem,

But the developer reporting, many of the triggers are missing.

Regards
Muktha
Re: Import schema into a newly created user [message #557340 is a reply to message #557333] Tue, 12 June 2012 00:54 Go to previous message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which ones?

Regards
Michel
Previous Topic: How to get date and time in the Date Column while sqlldr
Next Topic: Export data using expdp to remote host
Goto Forum:
  


Current Time: Sun Sep 27 11:57:26 CDT 2020