Kan man kopiere et helt schema med tabeller, konstraints, indices og brugere på en enkelt måde ?
f.eks.
CREATE USER NEWSCHEMA as select * from OLDSCHEMA;
eller skal jeg igang med
CREATE USER NEWSCHEMA IDENTIFIED BY VALUES '7FC886885CBBD457' DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMPSMALL PROFILE DEFAULT ACCOUNT UNLOCK; GRANT DBA TO NEWSCHEMA; GRANT CONNECT TO NEWSCHEMA; GRANT AQ_USER_ROLE TO NEWSCHEMA; GRANT AQ_ADMINISTRATOR_ROLE TO NEWSCHEMA; ALTER USER NEWSCHEMADEFAULT ROLE ALL; GRANT CREATE TRIGGER TO NEWSCHEMA; GRANT CREATE ANY TYPE TO NEWSCHEMA; GRANT CREATE PROCEDURE TO NEWSCHEMA; GRANT UNLIMITED TABLESPACE TO NEWSCHEMA; ALTER USER NEWSCHEMA QUOTA UNLIMITED ON USERS; ALTER USER NEWSCHEMA QUOTA UNLIMITED ON TEMPSMALL; ALTER USER NEWSCHEMA QUOTA UNLIMITED ON EPJIDX; ALTER USER NEWSCHEMA QUOTA UNLIMITED ON EPJIDTA;
stor løkke med alle tabeller create table NEWSCHEMA.TABLE1 as select * from OLDSCHEMA.TABLE1 stor løkke slut
Du kan exportere og importere det igen som en ny TOUSER:
Copying a schema
If you only want to copy a schema to the new server, things are basically the same.
1. Perform a schema export:
exp userid=system/manager file=my_db.dmp log=my_db.log owner=SCOTT The OWNER parameter exports a schema. In my example, that would be the SCOTT schema. Again, I also make sure to log the output of my export utility to a log file. 2. FTP the dump file (and log file) to the destination server. Make sure that you FTP in binary mode!
3. Precreate a new database on the destination server.
4. Precreate the tablespaces on the new database to match the same tablespace names of your source database.
5. Precreate the user in that database.
6. Import the dump file:
imp userid=system/manager file=my_db.dmp log=imp.log fromuser=SCOTT Again, log the output to a file in case there are errors. The FROMUSER clause tells imp which schema to import. If you wish to change the objects to a new owner, use the TOUSER clause as well.
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
