Duplicate tablespace on one bank

[Pertel]

Personal,

I have a tablespace called production and a user called production, which has access to this tablespace. I need to duplicate this tablespace production more with the test name and a user test to access the test tablespace. It's possible? like?

I use Oracle 10g Release 2 and OS is Ubuntu 7.04.

[diegoleite]

I do not know if I can help you. I do not understand exactly what you want.você would you like to replicate these tablespace and that owner? Or just create tablespace with your Datafiles and User?

[Pertel]

Diegolite,

I need to replicate the production tablespace with different name and owner. The way I know how to generate the dump

Select all

exp userid=producao file=producao.dmp logfile=producao.log full=y

If I'm not wrong, this way it generates a dump compler of the Owner tablespaces production, but when I do the import

Select all

imp userid=username file=producao.dmp full=y

Select all

imp userid=username file=producao.dmp full=y

Select all

imp userid=username file=producao.dmp full=y

99]] Oracle only matters if I inform the name of the Owner "owner" of Taplespace, which in this case is producing. I need to generate a dump and matter it with any Owner that I want. Can I be clearer or complicated more?! LOL...

[gilbertoca]

The IM command list me some interesting parameters! How about you try them?

Select all

 
gilberto@ti-des05:~> imp help=y 
 
Import: Release 10.1.0.2.0 - Production on Seg Nov 12 09:37:59 2007 
 
Copyright (c) 1982, 2004, Oracle.  All rights reserved. 
 
You can let Import prompt you for parameters by entering the IMP 
command followed by your username/password: 
 
     Example: IMP SCOTT/TIGER 
. 
. 
. 
Keyword  Description (Default)       Keyword      Description (Default) 
-------------------------------------------------------------------------- 
USERID   username/password           FULL         import entire file (N) 
BUFFER   size of data buffer         FROMUSER     list of owner usernames 
FILE     input files (EXPDAT.DMP)    TOUSER       list of usernames 
 

Gilberto

[Pertel]

Well, it's Gilbertoca, I'vê already tested some more things so far I could not reach my goal, can you help me with any tips?

Thanks.

[gilbertoca]

Create users on the other bank and assign the required permissions (if Creating a test bank from production, otherwise use grants = y). When you import using the command twice:

# Importing the data without the constraints

Select all

imp system/manager file=arquivo full=y fromuser=scott touser=blake grants=n constraints=n

# Importing without the Data with constraints

Select all

 imp \'/ AS SYSDBA\'  file=arquivo full=y fromuser=scott touser=blake grants=n constraints=y rows=n

Gilberto

[Pertel]

Gilberto,

I'vê been researching a solution when I was waiting for your response and I found this:

Select all

imp system/manager@banco8 fromuser=macul touser=schemaaserimportado file=arquivo.dmp. 

With this tip I generated the Dump

Select all

exp userid=producao file=producao.dmp full=y statistics=none

I imported

Select all

imp userid=system/xxxx@sid fromuser=producao touser=teste file=producao.dmp full=y

so far, only when I went to look at Tablespace test by DBConsole, she was empty and The production tablespaces had increased its size, with this tip I arrived very close to my goal, it would be all ok if the import had played everything to Tablespa test. It is possible? I would like to make it clear that I need two equal tablespaces on the same bank, one will be for a client and the other will be for the new customer.

[gilbertoca]

You have a prerequisite for creating any object in the bank, the existence of a storage area, aka tablespace!
Exp and Imp utilities are used under these objects and not under a storage area (Tablespace). That is, the area where these imported objects will reside need already exist, understand?

See the example I quoted: Where do you think the Blake object (the User Schema) is residing?
Another thing, the behavior of the IMP utility is correct, where, the rule is that the non-existence of the user being imported, entails their creation in the standard storage area (defined by the DBA in the creation of the instance).

Gilberto

[Pertel]

That is, Gilberto, I have two schemas (production and test) occupying the same storage area, where, initially the two are identical and any change (INSERT, UPDATE, DELETE) performed in any of the schemas, will not affect in the data of the other? In other words, I managed to generate a copy of my schema and import it on the same bank, for a different name schema?

[gilbertoca]

Right!

Gilberto

[cigano]

Dude,
as you have the data on the same tablespace now you can make a move from the user tables test for the test tablespace, as in the example:

alter owner.table moves tablespace tablespace;

Make a test table and verify if this is what you are looking for.

: D

[jamirjr]

- Move the tables to the correct tablespace

Select all

select 'ALTER TABLE oooo.'||A.TABLE_NAME ||' MOVE TABLESPACE aaaaa;' 
FROM DBA_TABLES A 
WHERE A.OWNER = 'nnnn'

- Do not forget the indicates
- Move the indices for the correct tablespace

Select all

select 'ALTER INDEX oooo.'||A.INDEX_NAME ||' REBUILD TABLESPACE aaaaa;' 
FROM DBA_INDEXES A 
WHERE A.OWNER = 'nnnn'