This process was repeated monthly. So, all references in the target database were
dropped each time the process was performed.
>>> [EMAIL PROTECTED] 02/07/01 11:56AM >>>
Thanks a whole bunch Tim, but one final question.
Why did you drop the tablespaces
<drop tablespace stage_haines_data including contents;
<drop tablespace stage_haines_index including contents;
-----Original Message-----
Sent: Wednesday, February 07, 2001 4:36 AM
To: Multiple recipients of list ORACLE-L
Filtering attachments is a reasonable precaution, I guess. Here is the
pasted script:
#!/bin/ksh
# This script is used to transport tablespaces from STAGE database to CARS.
# It needs two parameters as "From DB" and "To DB".
#
# Created : 2/16/2000 by Pawan Sanwal (Whittman-Hart)
#
# This script need to run from the same machine where both databases exist.
#
# Exit if not all the arguments supplied.
if [ "$1x" = "x" ] || [ "$2x" = "x" ]; then
echo ;
echo "Usage : transport.sh <From Stage Oracle SID> <To Cars Oracle SID>";
echo ;
exit 1;
fi
#
# The tablespaces should always be transported from Stage to Cars databases.
#
if [ `echo $1|grep stage >/tmp/log 2>/tmp/err; echo $?` -eq 1 ] ||
[ `echo $2|grep cars >/tmp/log 2>/tmp/err; echo $?` -eq 1 ]; then
echo ;
echo "Usage : transport.sh <From Stage Oracle SID> <To Cars Oracle SID>";
echo ;
exit 1;
fi
export ORACLE_SID=$2
#
# Ask for system user passwords on both databases.
#
echo
echo "Enter system user password for "$2" :\c"
echo [8m"\c"
read secondpwd
echo [0m
echo "Enter system user password for "$1" :\c"
echo [8m"\c"
read firstpwd
echo [0m
#
# Gather datafiles information for the transportable tablespaces, viz.
# tablespaces stage_haines_data and stage_haines_index.
#
ls -1 /u0??/oradata/$1/stage_haines*data*dbf >/tmp/temp1 2>/tmp/err
ls -1 /u0??/oradata/$1/stage_haines*index*dbf >>/tmp/temp1 2>/tmp/err
ls -1 /u0??/oradata/$2/stage_haines*data*dbf >/tmp/temp2 2>/tmp/err
ls -1 /u0??/oradata/$2/stage_haines*index*dbf >>/tmp/temp2 2>/tmp/err
#
# Exit if this script is being run from different machine than where
databases
# exist.
#
if [ `cat /tmp/temp1|grep "stage_haines" >/tmp/log 2>/tmp/err; echo $?` -eq
1 ] ||
[ `cat /tmp/temp2|grep "stage_haines" >/tmp/log 2>/tmp/err; echo $?` -eq
1 ]; then
echo "The databases do not exist on this machine. Please run the script";
echo "from the machine where databases exist.";
echo ;
exit 1;
fi
paste /tmp/temp1 /tmp/temp2 >/tmp/copy_temp.sh
sed 's/^/cp /' /tmp/copy_temp.sh >/tmp/copy_files.sh
sqlplus -s system/$secondpwd@$2 <<!
set pages 500
set lines 200
set head off
set feedback off
set verify off
set term off
drop tablespace stage_haines_data including contents;
drop tablespace stage_haines_index including contents;
!
export ORACLE_SID=$1
sqlplus -s system/$firstpwd@$1 <<!
set pages 500
set head off
set term off
execute
sys.dbms_tts.transport_set_check('STAGE_HAINES_DATA,STAGE_HAINES_INDEX',TRUE
);
spool /tmp/violations
select * from sys.transport_set_violations;
spool off
!
nohup grep "no rows selected" /tmp/violations.lst >>/dev/null
if [ $? = 1 ]; then
echo "Tablespaces are not self-contained... exiting...";
exit 1;
fi
#
# Alter the tablespaces in STAGE database to read only mode.
#
export ORACLE_SID=$1
sqlplus -s system/$firstpwd@$1 <<!
set pages 500
set head off
set term off
set verify off
set feedback off
alter tablespace stage_haines_data read only;
alter tablespace stage_haines_index read only;
!
#
# Export the tablespaces metadata from the Stage database.
#
exp userid=stage_user/stage_user@$1 file=/tmp/stage.dmp
transport_tablespace=y tablespaces=stage_haines_data, stage_haines_index
triggers=n constraints=n
#
# Copy the datafiles for the transportable tablespaces to the CARS
directoies.
#
chmod 777 /tmp/copy_files.sh
/tmp/copy_files.sh
#
# Alter the tablespaces in STAGE database read write.
#
sqlplus -s system/$firstpwd@$1 <<!
set pages 500
set head off
set term off
set verify off
set feedback off
alter tablespace stage_haines_data read write;
alter tablespace stage_haines_index read write;
!
#
# Import the tablespaces metadata into the CARS database.
#
export ORACLE_SID=$2
imp userid=system/$secondpwd@$2 file=/tmp/stage.dmp transport_tablespace=y
datafiles=`cat /tmp/temp2`
#
# As stage user grant SELECT privilege to stage_user_role on the transported
# objects in the cars database.
#
sqlplus -s system/$secondpwd@$2 <<!
set pages 500
set head off
set term off
set verify off
set feedback off
col password new_value oldpswd noprint
col tmppassword new_value tmppswd noprint
col loweruser new_value lower_user noprint
select lower(username) loweruser, password
from dba_users
where username = UPPER('stage');
create user "&lower_user" identified by temp123;
select password tmppassword from dba_users
where username = '&lower_user';
drop user "&lower_user";
alter user stage identified by values '&tmppswd';
connect stage/temp123@$2
grant select on stage.haines_building_dta to stage_user_role;
grant select on stage.haines_res_bus_dta to stage_user_role;
grant select on stage.haines_transfer_dta to stage_user_role;
grant select on stage.haines_work_site_land_dta to stage_user_role;
alter user stage identified by values '&&oldpswd';
!
>>> [EMAIL PROTECTED] 02/06/01 04:38PM >>>
Could you include the file in your message. The list strips non-text
attachments.
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Tim
> Sawmiller
> Sent: Tuesday, February 06, 2001 3:47 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: How to use Export Transportable Tablespaces
>
>
> Exporting Tablespaces is not achieved using fromuser/touser. The
> entire tablespace is transported, no matter whose objects are in
> it. Actually, just the meta data is exported and imported. The
> other step is to physically copy the files supporting the
> tablespace to a new location and attaching them to the the target
> database. See attached file for an example.
>
>
>
> >>> [EMAIL PROTECTED] 02/06/01 02:10PM >>>
> There are straightforward examples in the 8.1.7 documentation. I
> don't know if that feature is available in 8.1.6. But
> regardless, you should investigate WHY the fromuser/touser didn't
> achieve the desired results or I fear you'll be disappointed again.
>
> Jim
>
> >>> [EMAIL PROTECTED] 02/06/01 12:22PM >>>
>
> Does anyone have an example of how to use transportable tablespaces with
> export/import.
>
> I want export a user (8.1.6 database) source and import fromuser
> touser(8.1.6 database) target.
>
> I tried to import fromuser/ touser, but many contraints were not
> created and
> lost some data
> so I was told to try transportable tables. Can't find any good examples on
> how to use this new
> feature.
>
> Can someone please help me.
>
> Thanks
> Larry
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Larry Taylor
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jim Conboy
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Glenn Travis
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Taylor
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
