| Schema Import on a logical standby db [message #240799] |
Fri, 25 May 2007 18:51  |
tmathew
Messages: 18
Registered: May 2007
|
Junior Member |
|
|
Friends,
has anyone done a schema import on a logical standby?
Here is the scenario,
have a schema on dev environment manily for reporting purpose and I need to import this into logical standby of a primary production db.
Here is what I tried,
1) created seprate tbl spaces on logical standby
2) created the new schema user
3) granted following previleges to this new schema
CREATE SESSION
CREATE TABLE
SELECT ANY TABLE
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
4) CREATE TABLE test (ID VARCHAR2(10), MB_ID VARCHAR2(25) NOT NULL ENABLE)
CREATE TABLE test (ID VARCHAR2(10), MB_ID VARCHAR2(25) NOT NULL ENABLE)
*
ERROR at line 1:
ORA-01031: insufficient privileges
I've given quota unlimted for most of the tbl spaces. I'm little hesitant to grant dba role to this user.
any idea what's the issue here?
Thanks,
Mathew
|
|
|
|
|
|
| Re: Schema Import on a logical standby db [message #240804 is a reply to message #240799] |
Fri, 25 May 2007 22:09  |
tmathew
Messages: 18
Registered: May 2007
|
Junior Member |
|
|
Thanks Dreams for your quick response!
DID you take expdp or exp?
I did use exp
exp userid=user_new/password file=user_schema052407.dmp log=user_schema052407.log statistics=none
ru trying to imp it through the user which you just reacently created??
can you paste your imp command?
Yes,
imp userid=user_new/password@db1 file=port_schema052407.dmp log=port_schema.log statistics=none
try this give imp_full_database privevge to the user you created and then try to imp.
I haven't given imp_full_database previlage.But new schema user not able to create any table through sqlplus and getting following error.
CREATE TABLE test (ID VARCHAR2(10), MB_ID VARCHAR2(25) NOT NULL ENABLE)
*
ERROR at line 1:
ORA-01031: insufficient privileges [/I]
don't think i can do the import if i can't fix the above error.
Thanks,
Mathew
|
|
|
|
| Re: Schema Import on a logical standby db [message #240805 is a reply to message #240799] |
Fri, 25 May 2007 22:25 |
tmathew
Messages: 18
Registered: May 2007
|
Junior Member |
|
|
But can query the database as this new schema user. I suspect datagurd is protecting the new schema which i creted. i wanted to try ALTER SESSION DISABLE DATA GUARD but not sure what previlage I need to grant for this new schema in order to run that command
Mathew
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
