|
|
| Don't understand your question, please explain ! [message #52151 is a reply to message #52134] |
Fri, 05 July 2002 03:07   |
B
Messages: 327
Registered: August 1999
|
Senior Member |
|
|
Hi Dude !
You have a export file .. ok but "exported in tablespace system" ???? it does'nt mean anything ( to me :-( I've a poor english ..so ...). Did'nt you export some objects store in the tablespace system, and don't you want to recreate it in a non-system tablespace ?
|
|
|
|
| Re: reg import from one table space to another tablespace [message #52157 is a reply to message #52134] |
Fri, 05 July 2002 06:49 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
It isn't too hard to do. Always do a full export before you do anything. For each schema:
1. export the schema. (exp system/manager owner=SCHEMA)
2. drop objects for that schema from the system tablespace.
3. modify the schema account.
SQL> alter user SCHEMA quota 0M on system;
SQL> alter user SCHEMA quota unlimited on YOURTS;
revoke RESOURCE, DBA, UNLIMITED TABLESPACE, or any privs granting space anywhere else.
4. import the user. (imp system/manager fromuser=SCHEMA touser=SCHEMA)
5. Grant the schema access to the index tablespace by giving them quota and move the indexes:
SQL> alter index SCHEMA.INDEXNAME rebuild tablespace TABLESPACENAME;
6. grant privs back to the SCHEMA.
Here is a script to drop all the objects for the SCHEMA:
rem =========================================================================
rem
rem drop_user_objects.sql
rem
rem Copyright (C) Oriole Software, 1999
rem
rem Downloaded from http://www.oriolecorp.com
rem
rem This script for Oracle database administration is free software; you
rem can redistribute it and/or modify it under the terms of the GNU General
rem Public License as published by the Free Software Foundation; either
rem version 2 of the License, or any later version.
rem
rem This script is distributed in the hope that it will be useful,
rem but WITHOUT ANY WARRANTY; without even the implied warranty of
rem MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
rem GNU General Public License for more details.
rem
rem You should have received a copy of the GNU General Public License
rem along with this program; if not, write to the Free Software
rem Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
rem
rem =========================================================================
rem
rem This script, to be run by a DBA, generates a script to drop
rem all of a user's objects - while retaining the Oracle account with
rem all its privileges. This is especially useful before reimporting
rem data on a per-user basis, for instance to update the contents of
rem a test database.
rem
rem Usage : @drop_user_objects <username>
rem
rem It generates drop_<username>_objects.sql
rem
rem Note that it take cares of dropping only objects which do not
rem depend on other objects (indexes and triggers for instance are
rem dropped with the associated tables, as are package bodies with
rem the associated packages) in order to avoid harmless but irritating
rem error messages. However, no in-depth checking of dependencies is
rem performed.
rem This may lead to a number of misleading error messages :
rem - if you are using clusters (God forbid!) the generated script will
rem try to drop the clustered tables after the cluster is gone.
rem - with Oracle8, 'hidden' tables such as overflow tables for index
rem organized tables will be explicitly dropped after the main
rem table has been dropped.
rem - etc.
rem
rem As a rule, do not worry about '... does not exist' messages
rem
rem Note that, when run, the script which actually drops the objects
rem will generate a spool file.
rem
set pause off
set echo off
set scan on
set verify off
set pagesize 0
set feedback off
set recsep off
set termout off
spool drop_&1._objects.sql
select 'spool drop_&1._objects.log'
from dual
/
select 'drop ' || object_type || ' ' || owner || '.' || object_name
|| decode(object_type,
'CLUSTER', ' including tables cascade constraints;',
'TABLE', ' cascade constraints;',
';')
from dba_objects
where owner = upper('&1')
and object_type in ('CLUSTER', 'TABLE', 'VIEW', 'SEQUENCE', 'SYNONYM',
'FUNCTION',
'PROCEDURE', 'PACKAGE')
/
select 'spool off'
from dual
/
spool off
set feedback on
set pagesize 24
set termout on
prompt Run @drop_&1._objects to drop &1's objects ...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
