Home » RDBMS Server » Server Utilities » move objects from one database to another (Oracle 10.2.0.2.0,Unix)
move objects from one database to another [message #510079] Thu, 02 June 2011 07:58 Go to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member


Hi,

I want to move 350 database objects from one database to another database schema.

Need your valuable inputs.

Thanks
Re: move objects from one database to another [message #510083 is a reply to message #510079] Thu, 02 June 2011 08:19 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is there a problem with using export/import for this?
Re: move objects from one database to another [message #510116 is a reply to message #510083] Thu, 02 June 2011 09:45 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member



How to use since there are 350 tables to be moved to another database ?

Thanks
Re: move objects from one database to another [message #510123 is a reply to message #510116] Thu, 02 June 2011 09:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What difference do you think the number of tables makes?
Re: move objects from one database to another [message #510124 is a reply to message #510116] Thu, 02 June 2011 09:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>How to use since there are 350 tables to be moved to another database ?
compose CONTROL file that contains each of the 350 table names
Re: move objects from one database to another [message #510135 is a reply to message #510124] Thu, 02 June 2011 10:29 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Whether it is to move into another schema or database Oracle will handle the case.
SQL> host exp help=y

Export: Release 11.2.0.1.0 - Production on Thu Jun 2 20:56:59 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.



You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

     Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export

Export terminated successfully without warnings.

SQL> host expdp help=y

Export: Release 11.2.0.1.0 - Production on Thu Jun 2 20:57:17 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

------------------------------------------------------------------------------

The available keywords and their descriptions follow. Default values are listed within square brackets.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.

COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS
Data layer option flags.
Valid keyword values are: XML_CLOBS.

DIRECTORY
Directory object to be used for dump and log files.

DUMPFILE
Specify list of destination dump file names [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.

ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.

ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].

ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.

ESTIMATE
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.

ESTIMATE_ONLY
Calculate job estimates without performing the export.

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

FILESIZE
Specify the size of each dump file in units of bytes.

FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

FULL
Export entire database [N].

HELP
Display Help messages [N].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of export job to create.

LOGFILE
Specify log file name [export.log].

NETWORK_LINK
Name of remote database link to the source system.

NOLOGFILE
Do not write log file [N].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file name.

QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [N].

SAMPLE
Percentage of data to be exported.

SCHEMAS
List of schemas to export [login schema].

SOURCE_EDITION
Edition to be used for extracting metadata.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

TABLES
Identifies a list of tables to export.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES
Identifies a list of tablespaces to export.

TRANSPORTABLE
Specify whether transportable method can be used.
Valid keyword values are: ALWAYS and [NEVER].

TRANSPORT_FULL_CHECK
Verify storage segments of all tables [N].

TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be unloaded.

VERSION
Version of objects to export.
Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.

------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

ADD_FILE
Add dumpfile to dumpfile set.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [N].

START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.



SQL>


Re: move objects from one database to another [message #510138 is a reply to message #510135] Thu, 02 June 2011 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why using SQL*Plus to execute host commands?

Regards
Michel
Re: move objects from one database to another [message #510143 is a reply to message #510138] Thu, 02 June 2011 10:52 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Am A bit LAZY! As I have already connected to sqlplus Just executed that.

Sriram
Re: move objects from one database to another [message #510237 is a reply to message #510143] Fri, 03 June 2011 02:19 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member



Thanks Sriram and others...
Re: move objects from one database to another [message #510280 is a reply to message #510237] Fri, 03 June 2011 07:04 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Hi,


The request has been changed.Want to move only 'PROCEDURES',FUNCTIONS and
'TRIGGERS' owned by bala schema.

what is the export and import parameters used for this condition and also query to fetch
only these 3 objects in database.


Thanks
Re: move objects from one database to another [message #510283 is a reply to message #510280] Fri, 03 June 2011 07:19 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use datapump with the include parameter.
However you really ought to have scripts for all those objects in source control.
Then you wouldn't need to use datapump.
Re: move objects from one database to another [message #510286 is a reply to message #510283] Fri, 03 June 2011 07:31 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member



can you provide me those scripts ?

Re: move objects from one database to another [message #510288 is a reply to message #510286] Fri, 03 June 2011 07:34 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Can you try reading the docs? Click on the link. Scroll down to the bit that describes the include parameter.
Read the examples that go with it.

Re: move objects from one database to another [message #510295 is a reply to message #510283] Fri, 03 June 2011 08:12 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
or DBMS_METADATA.
Re: move objects from one database to another [message #510388 is a reply to message #510295] Sat, 04 June 2011 12:37 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Hi,

Actually there were only less number of objects to be moved and i moved all the objects by taking
the code and executed them directly in the database. I found some of the objects
created but with INVALID status. The error i found was

401/10 PL/SQL: Statement ignored
402/13 PLS-00201: identifier 'DEMO_MAIL.BEGIN_MAIL' must be declared
412/10 PL/SQL: Statement ignored
412/10 PLS-00201: identifier 'DEMO_MAIL.ATTACH_TEXT' must be declared
421/16 PL/SQL: Statement ignored
421/16 PLS-00201: identifier 'DEMO_MAIL.BEGIN_ATTACHMENT' must be
declared
459/22 PL/SQL: Statement ignored
459/22 PLS-00201: identifier 'DEMO_MAIL.WRITE_RAW' must be declared
487/16 PL/SQL: Statement ignored

I checked the code of object in the source database, the object and DEMO_MAIL.BEGIN_MAIL exist but
only the object exist but DEMO_MAIL.BEGIN_MAIL does not exist in the database(B) where i compiled.

FYI
I ran the same code of source database(A) to database (B)

Need your suggestion.

Thanks


[Updated on: Sat, 04 June 2011 12:39]

Report message to a moderator

Re: move objects from one database to another [message #510389 is a reply to message #510388] Sat, 04 June 2011 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either:
- object does not exist
- stored object owner has not direct access to it

Regards
Michel

[Updated on: Sat, 04 June 2011 14:57]

Report message to a moderator

Re: move objects from one database to another [message #510390 is a reply to message #510389] Sat, 04 June 2011 13:57 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I checked the code of object in the source database, the object and DEMO_MAIL.BEGIN_MAIL exist
Since errors were thrown either above or below is incorrect or incomplete.
>I ran the same code of source database(A) to database (B)

Since you decided to ignore Posting Guidelines, http://www.orafaq.com/forum/t/88153/0/, we can only guess at what might be wrong.
Sad
Previous Topic: Estimate tablespace growth while loading data using sqlldr
Next Topic: DMP from 11G to 10G
Goto Forum:
  


Current Time: Thu Mar 28 09:55:50 CDT 2024