OraFAQ Forum: Server Utilities » unwanted trigger created when importing

Home » RDBMS Server » Server Utilities » unwanted trigger created when importing (oracle 10.2.0.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

unwanted trigger created when importing [message #432602]

Wed, 25 November 2009 02:58

goracle9
Messages: 136
Registered: December 2006

Senior Member

I created 1 schema namely REMY then I import objects from 2 schemas(SHAJI,ASHMA) without data, when I do like this, there is 1 trigger REMY.part_deletion in SHAJI. Part_deletion is a trigger already there in SHAJI
when I import, the trigger part_deletion imported in to REMY, but why this REMY.part_deletion creating in SHAJI(sorce schema). Can anybody help me, I don't want to create trigger like this in source schema

why its hapening like this

Report message to a moderator

Re: unwanted trigger created when importing [message #432623 is a reply to message #432602]

Wed, 25 November 2009 04:50

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Import imports data into the target schema. It is specified within the IMP command (either by "username/password" or "fromuser ... touser"). Therefore, it can not import anything into a schema that is not mentioned here.

How do you know that "part_deletion" trigger came into the SHAJI schema during import session? How do you know REMY.part_deletion exists in SHAJI schema?

Report message to a moderator

Re: unwanted trigger created when importing [message #432626 is a reply to message #432623]

Wed, 25 November 2009 05:03

goracle9
Messages: 136
Registered: December 2006

Senior Member

developer showing me the trigger Remy.part_deletion in the forms trigger belongs to the table

Report message to a moderator

Re: unwanted trigger created when importing [message #432633 is a reply to message #432626]

Wed, 25 November 2009 06:29

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Sorry, but I don't understand that.

Developer (a person? SQL Developer?) showed you that trigger REMY.PART_DELETION exists in forms (Forms Developer? I suppose not. Which "forms", then?) and belongs to that table?

Could you provide a piece of code (executed in SQL*Plus and pasted here) or a screenshot (if you use GUI) so that we could see what's going on?

Report message to a moderator

import problem [message #433087 is a reply to message #432602]

Sun, 29 November 2009 23:41

goracle9
Messages: 136
Registered: December 2006

Senior Member

when i import all the object from one schema to another newly created schema, triggers are recreated in source schema like(targetschema.trigger_name).

i used (imp system/passwrd rows=n filename=file.dmp log=impschema.log)

please let me fix the problem

Report message to a moderator

Re: import problem [message #433100 is a reply to message #433087]

Mon, 30 November 2009 01:25

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Well, using such an import command, you have imported objects into SYSTEM schema.

Why didn't you simply

EXP old_user/its_password FILE=old_schema.dmp
IMP new_user/its_password FILE=old_schema.dmp

or, if you insist on SYSTEM, use FROMUSER and TOUSER parameters?

Report message to a moderator

Re: import problem [message #433103 is a reply to message #433100]

Mon, 30 November 2009 01:40

goracle9
Messages: 136
Registered: December 2006

Senior Member

Sorry to not mention clearly sorry.

i used

$imp parfile=impuser.par

parfile:

userid=system/paswrd
fromuser=shaji
touser=remy
rows=n
grants=y
indexes=y
file=/home/exp.dmp
log=/home/imp_shaji_nov24.log

in this case triggers create like (remy.triger1,remy.trigger2,remy.trigger3)in shaji schema
mean triggers created source schema with qualifier of target schema

so in source schema all the user triggers are repeated with target qualifier(remy.trigger_name)

pls help, sorry to not given clear problem

Report message to a moderator

Re: import problem [message #433107 is a reply to message #433103]

Mon, 30 November 2009 02:18

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I still don't get it. Here's what I've done: first, create users REMY nad SHAJI:

SQL> create user remy identified by remy;

User created.

SQL> grant connect, resource to remy;

Grant succeeded.

SQL> create user shaji identified by shaji;

User created.

SQL> grant connect, resource to shaji;

Grant succeeded.

Now connect as SHAJI (this is a "source" schema), create a table and a trigger:

SQL> connect shaji/shaji@ora10
Connected.

SQL> create table test (id number);

Table created.

SQL> create or replace trigger trg_test
  2  before insert on test
  3  for each row
  4  begin
  5    null;
  6  end;
  7  /

Trigger created.

SQL> select trigger_name, table_owner, table_name from user_triggers;

TRIGGER_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
TRG_TEST                       SHAJI                          TEST

So far so good, right?
Export SHAJI:

SQL> $exp shaji/shaji@ora10 file=shaji.dmp

Export: Release 10.2.0.1.0 - Production on Pon Stu 30 09:11:27 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SHAJI
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SHAJI
About to export SHAJI's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SHAJI's tables via Conventional Path ...
. . exporting table                           TEST          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

Import into REMY:

SQL> $imp remy/remy@ora10 file=shaji.dmp full=y

Import: Release 10.2.0.1.0 - Production on Pon Stu 30 09:11:50 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SHAJI, not by you

import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
. importing SHAJI's objects into REMY
. . importing table                         "TEST"          0 rows imported
Import terminated successfully without warnings.

Now let's see what triggers do we have in REMY and SHAJI schemas:

SQL> connect remy/remy@ora10
Connected.

SQL> select trigger_name, table_owner, table_name from user_triggers;

TRIGGER_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
TRG_TEST                       REMY                           TEST

SQL> connect shaji/shaji@ora10
Connected.

SQL> select trigger_name, table_owner, table_name from user_triggers;

TRIGGER_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
TRG_TEST                       SHAJI                          TEST

SQL>

I don't see anything strange here. Could you, please, do the same and SHOW where exactly remy's trigger ends up in shaji's schema?

Report message to a moderator

Re: import problem [message #433363 is a reply to message #433107]

Tue, 01 December 2009 23:15

goracle9
Messages: 136
Registered: December 2006

Senior Member

Mr.Littlefoot

Thanks for your effort

see..

in test server already 2 users like whadmin,srvadmin
then i m creating another user BADEEL then i import all object from whadmin and srvadmin to BADEEL

exp srvadmin/pwd@develop file=srv.dmp rows=n statistics=none log=srv.log

exp whadmin/pwd@develop file=wh.dmp rows=n statistics=none log=wh.log

imp badeel/pwd@develop file=srv.dmp log=srvimp.log

imp badeel/pwd@develop file=wh.dmp log=whimp.log

now i select triggers

connect srv/pwd@develop

SQL> CONN SRVADMIN/SRVDEV@DEV
Connected.
SQL> select trigger_name,table_owner,table_name from user_triggers;

TRIGGER_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
CAR_BLOCK_HIST                 SRVADMIN                       SC_CAR_MAST
INSERT_JOBCODES                SRVADMIN                       SC_CAMPAIGN
LEDG_UP                        SRVADMIN                       SC_INVOICE
LOG_SC_P_TRANS                 SRVADMIN                       LOG_SC_P_TRANS
STK_UP                         SRVADMIN                       SC_P_TRANS
UP_HIST                        SRVADMIN                       SC_BLK_CARD

6 rows selected.


SQL> CONN WHADMIN/WHDEV@DEV
Connected.
SQL> /

TRIGGER_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
M_CUSTOMER                     WHADMIN                        M_CUSTOMER
M_PAYMENT_MODE                 WHADMIN                        M_PAYMENT_MODE
M_SUPPLIER                     WHADMIN                        M_SUPPLIER
M_TRANSACTION                  WHADMIN                        M_TRANSACTION_TYPE
M_WHOSE                        WHADMIN                        M_WHOUSE
PART_DELETION                  WHADMIN                        M_PART

6 rows selected.

CONNECT BADEEL/BADEELDEV@DEV

  1* SELECT TRIGGER_NAME,TABLE_OWNER,TABLE_NAME FROM USER_TRIGGERS ORDER BY 1
SQL> /

TRIGGER_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
CAR_BLOCK_HIST                 SRVADMIN                       SC_CAR_MAST
INSERT_JOBCODES                BADEEL                         SC_CAMPAIGN
LEDG_UP                        SRVADMIN                       SC_INVOICE
LOG_SC_P_TRANS                 BADEEL                         LOG_SC_P_TRANS
M_CUSTOMER                     WHADMIN                        M_CUSTOMER
M_PAYMENT_MODE                 BADEEL                         M_PAYMENT_MODE
M_SUPPLIER                     BADEEL                         M_SUPPLIER
M_TRANSACTION                  BADEEL                         M_TRANSACTION_TYPE
M_WHOSE                        BADEEL                         M_WHOUSE
PART_DELETION                  WHADMIN                        M_PART
STK_UP                         SRVADMIN                       SC_P_TRANS
UP_HIST                        BADEEL                         SC_BLK_CARD

12 rows selected.

all the triggers imported from 2 users(srvadmin/whadmin) but see in table_owner column

why it is showing source username instead of BADEEL

trigger part_deletion showing table owner=whadmin instead of BADEEL.

pls explain

in IT deptmt. developers saying that one more trigger showing in there application like

part_deletion & BADEEL.part_deletion(redendency with extention of badeel)

like this some other triggers also in srvadmin.

[EDITED by LF: applied [code] tags]

[Updated on: Wed, 02 December 2009 01:01] by Moderator

Report message to a moderator

Re: import problem [message #433390 is a reply to message #433363]

Wed, 02 December 2009 01:14

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Hm, strange ... I'm sorry, but I wouldn't know what has happened. I've tried to do the same (exporting a "real" user and importing the DMP into another, newly created schema), but - all tables were owned by schema owner.

No idea.

Hopefully, someone will be able to provide an answer.

Report message to a moderator

Re: import problem [message #433434 is a reply to message #433363]

Wed, 02 December 2009 05:29

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

IND> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected.

IND> SHO USER
USER is "MOHAN"
IND>  select trigger_name,table_owner,table_name from user_triggers;

no rows selected

IND> CONN SRIRAM/SRIRAM@IND
Connected.
IND> select trigger_name,table_owner,table_name from user_triggers;

no rows selected

IND> ED
Wrote file afiedt.buf

  1   CREATE OR REPLACE TRIGGER SRIRAM.TRIGGER_TEST
  2    BEFORE UPDATE
  3    ON MOHAN.DEPT
  4      BEGIN
  5      NULL;
  6*   END ;
IND> /

Trigger created.
IND> SET LINE 180
IND> /

TRIGGER_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
TRIGGER_TEST                   MOHAN                          DEPT

1 row selected.

IND> CONN MOHAN/MOHAN@IND
Connected.
IND> select trigger_name,table_owner,table_name from user_triggers;

no rows selected.
IND> CONN MOHAN/MOHAN@IND
Connected.
IND> select trigger_name,table_owner,table_name from user_triggers;

no rows selected
IND> CONN SRIRAM/SRIRAM@IND
Connected.
IND> ED
Wrote file afiedt.buf

  1   CREATE OR REPLACE TRIGGER SRIRAM.TRIGGER_TEST2
  2    BEFORE UPDATE
  3    ON SRIRAM.CUSTOMER_INFO
  4      BEGIN
  5      NULL;
  6*   END ;
IND> /

Trigger created.

IND> SHO USER
USER is "SRIRAM"
IND> select trigger_name,table_owner,table_name from user_triggers;

TRIGGER_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ -----------------------------
TRIGGER_TEST2                  SRIRAM                         CUSTOMER_INFO
TRIGGER_TEST                   MOHAN                          DEPT

2 rows selected.

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\Administrator.XXXX>exp system/signupdb@ind file=c:\orafaq_test.dmp owner=sriram

Export: Release 10.2.0.1.0 - Production on Wed Dec 2 16:38:22 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SRIRAM
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SRIRAM
About to export SRIRAM's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SRIRAM's tables via Conventional Path ...
. . exporting table                       CATEGORY          0 rows exported
. . exporting table                  CUSTOMER_INFO          0 rows exported
. . exporting table                     EVENT_INFO          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\Administrator.XXXXX>imp system/signupdb@ind file=c:\orafaq_test.dmp fromuser=sriram touser=mohan

Import: Release 10.2.0.1.0 - Production on Wed Dec 2 16:44:27 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SRIRAM's objects into MOHAN
. . importing table                     "CATEGORY"          0 rows imported
. . importing table                "CUSTOMER_INFO"          0 rows imported
. . importing table                   "EVENT_INFO"          0 rows imported
About to enable constraints...
Import terminated successfully without warnings.

C:\Documents and Settings\Administrator.XXXXX>

IND> CONN MOHAN/MOHAN@IND
Connected.

IND> select trigger_name,table_owner,table_name from user_triggers;

TRIGGER_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
TRIGGER_TEST2                  SRIRAM                         CUSTOMER_INFO
TRIGGER_TEST                   MOHAN                          DEPT

2 rows selected.

IND> SHO USER
USER is "MOHAN"
IND>

IND> SHO USER
USER is "MOHAN"
IND> select * from cat where table_name like 'CUSTOMER_INFO';

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
CUSTOMER_INFO                  TABLE

1 row selected.

IND> sho user
USER is "MOHAN"
IND>

Even though the table exists in the schema "MOHAN "
It is showing the table_owner as sriram just because of the specification in that trigger code.(sriram. CUSTOMER_INFO).

Sriram Smile

Smile

[Updated on: Wed, 02 December 2009 05:30]

Report message to a moderator

Re: import problem [message #433448 is a reply to message #433434]

Wed, 02 December 2009 06:35

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Ah, so it seems that this is crucial:

CREATE OR REPLACE TRIGGER SRIRAM.TRIGGER_TEST
BEFORE UPDATE
ON MOHAN.DEPT
BEGIN
NULL;
END ;

That means that user SRIRAM is allowed to create triggers for MOHAN's tables (i.e. owns CREATE ANY TRIGGER privilege). OK then, if that's what has happened.

Thank you, Sriram!

Report message to a moderator

Re: import problem [message #433449 is a reply to message #433448]

Wed, 02 December 2009 06:47

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

IND> ED
Wrote file afiedt.buf

  1   CREATE OR REPLACE TRIGGER SRIRAM.TRIGGER_TEST2
  2    BEFORE UPDATE
  3    ON SRIRAM.CUSTOMER_INFO
  4      BEGIN
  5      NULL;
  6*   END ;
IND> /

Trigger created.

@ Littlefoot
Please observe this code part On the above reply....

Sriram created trigger on his own object(SRIRAM.CUSTOMER_INFO).

sriram Smile

Smile

Report message to a moderator

Re: import problem [message #433450 is a reply to message #433449]

Wed, 02 December 2009 06:48

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

TRIGGER_TEST2, yes - on his own table.

TRIGGER_TEST, no.

Report message to a moderator

Re: import problem [message #433451 is a reply to message #433450]

Wed, 02 December 2009 06:55

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Yes thats what i mean...

See the below

IND> CONN MOHAN/MOHAN@IND
Connected.

IND> select trigger_name,table_owner,table_name from user_triggers;

TRIGGER_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
TRIGGER_TEST2                  SRIRAM                         CUSTOMER_INFO
TRIGGER_TEST                   MOHAN                          DEPT

2 rows selected.

IND> SHO USER
USER is "MOHAN"
IND>

This is after import (Please see above reply),TRIGGER_TEST2 is based on the table CUSTOMER_INFO of sriram User not mohan user just because of the code sriram.CUSTOMER_INFO.

Hope i am clear(this mohan has all the privileges as sriram)

sriram Smile

Smile

[Updated on: Wed, 02 December 2009 06:56]

Report message to a moderator

Re: import problem [message #433458 is a reply to message #433451]

Wed, 02 December 2009 07:29

goracle9
Messages: 136
Registered: December 2006

Senior Member

Thanks Mr.Sriram,Littlefoot

as tour experiment

look this

CONNECT BADEEL/BADEELDEV@DEV

1* SELECT TRIGGER_NAME,TABLE_OWNER,TABLE_NAME FROM USER_TRIGGERS ORDER BY 1
SQL> /

TRIGGER_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------
CAR_BLOCK_HIST SRVADMIN SC_CAR_MAST
INSERT_JOBCODES BADEEL SC_CAMPAIGN
LEDG_UP SRVADMIN SC_INVOICE
LOG_SC_P_TRANS BADEEL LOG_SC_P_TRANS
M_CUSTOMER WHADMIN M_CUSTOMER
M_PAYMENT_MODE BADEEL M_PAYMENT_MODE
M_SUPPLIER BADEEL M_SUPPLIER
M_TRANSACTION BADEEL M_TRANSACTION_TYPE
M_WHOSE BADEEL M_WHOUSE
PART_DELETION WHADMIN M_PART
STK_UP SRVADMIN SC_P_TRANS
UP_HIST BADEEL SC_BLK_CARD

12 rows selected.

1) so--- PART_DELETION trigger created like
create trigger on whadmin.M_PART correct??

2) M_TRANSACTION trigger created like
create trigger on M_TRANSACTION_TYPE CORRECT???

THESE TRIGGERS ARE CREATED WHILE IMPORT OBJECT FROM WHADMIN TO BADEEL MIND PLZ.

MAKE ME CLEAR PLZ.

Report message to a moderator

Re: import problem [message #433464 is a reply to message #433451]

Wed, 02 December 2009 07:51

goracle9
Messages: 136
Registered: December 2006

Senior Member

ONE MORE QUEARY

i have cretaed user name BADEEL default_tablespacename BADEEL

SQL> CONN SYSTEM/PWD@DEV
Connected.
SQL> SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS;

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
BADEEL BADEEL

after i m imported objects from srvadmin and whadmin to BADEEL

SEE BADEEL USING OTHER TABLESPACES INSTEAD OF DEFAULT_TABLESPACE

SQL> CONN BADEEL/PWD@DEV
Connected.
SQL> SELECT TABLESPACE_NAME,BYTES FROM USER_TS_QUOTAS;

TABLESPACE_NAME BYTES
------------------------------ ---------
INDX 1.021E+09
SERVICE 1.422E+09
PARTS 781713408

not even star using DEFAULT_TABLESPACE WHY???

SERVICE AND PARTS IS A DEFAULT_TABLESPACE FOR SRVADMIN AND WHADMIN

Report message to a moderator

Re: import problem [message #433485 is a reply to message #433464]

Wed, 02 December 2009 09:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

By default import deposits objects into their original tablespaces when they do not exist within new DB

Report message to a moderator

Re: import problem [message #433494 is a reply to message #433464]

Wed, 02 December 2009 09:48

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

Because this is looks permission issue. Before import you need to revoke "RESOURCE" roles otherwise data will import existing user tablespace.

Check the below example

SQL> create tablespace test datafile 'E:\ORACLE_HOME\PRODUCT\10.2.0\ORADATA\TESTDB\babu01.dbf' size 100m autoextend on next 1m

Tablespace created.

SQL> create user test1 identified by test default tablespace test quota unlimited on test;

User created.

SQL> create user test2 identified by test default tablespace test quota unlimited on test;

User created.

[b]SQL> grant connect, resource to test1;

Grant succeeded.

SQL> grant connect to test2;

Grant succeeded.[/b]

SQL>

C:\>SET ORACLE_SID=TESTDB

C:\>exp scott/tiger file=E:\Tmp\scott.dmp log=E:\Tmp\scott.log owner=scott

Export: Release 10.2.0.4.0 - Production on Wed Dec 2 15:39:10 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

C:\>imp system/sys file=E:\tmp\scott.dmp fromuser=scott touser=test1

Import: Release 10.2.0.4.0 - Production on Wed Dec 2 15:40:34 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into TEST1
. . importing table "BONUS" 0 rows imported
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
. . importing table "SALGRADE" 5 rows imported
About to enable constraints...
Import terminated successfully without warnings.

C:\>imp system/sys file=E:\tmp\scott.dmp fromuser=scott touser=test2

Import: Release 10.2.0.4.0 - Production on Wed Dec 2 15:40:43 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into TEST2
. . importing table "BONUS" 0 rows imported
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
. . importing table "SALGRADE" 5 rows imported
About to enable constraints...
Import terminated successfully without warnings.

SQL> L
1* select segment_name,tablespace_name from dba_segments where owner='TEST1'
SQL> /

SEGMENT_NA TABLESPACE_NAME
---------- ------------------------------
SALGRADE USERS
PK_EMP USERS
EMP USERS
PK_DEPT USERS
DEPT USERS
BONUS USERS

6 rows selected.

SQL> select segment_name,tablespace_name from dba_segments where owner='TEST2';

SEGMENT_NA TABLESPACE_NAME
---------- ------------------------------
SALGRADE TEST
PK_EMP TEST
EMP TEST
PK_DEPT TEST
DEPT TEST
BONUS TEST

6 rows selected.

- Babu

Report message to a moderator

Re: import problem [message #433553 is a reply to message #433494]

Wed, 02 December 2009 23:27

goracle9
Messages: 136
Registered: December 2006

Senior Member

Thanks,

if i drop user test1 with cascade option will it delete all objects created in user tablespace & reclaimed the space from that user tablespace???

Report message to a moderator

Re: import problem [message #433555 is a reply to message #433553]

Wed, 02 December 2009 23:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>if i drop user test1 with cascade option will it delete all objects created in user tablespace & reclaimed the space from that user tablespace???
Why ask us, rather than see exactly what happens by doing so yourself?

Report message to a moderator

Previous Topic:	HOW TO EXPORT JUST CONSTRAINTS & SEQUENCE?
Next Topic:	Doubts abt user data

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 11 04:09:07 CST 2025