Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: copying tables to another db, oracle 9.2

Re: copying tables to another db, oracle 9.2

From: Carel-Jan Engel <>
Date: Tue, 21 Mar 2006 11:39:21 +0100
Message-Id: <>


At first: a lot of counter-questions to start with:

In addition to what Daniel wrote: Is this a new application, or is it there for a long time already? That can help you to decide whether you want to transform the data in a better queriable format, or keep it the way it is.

What is the reason you want to keep all non-application work out of your database? Security? Resource consumption?

If resource consumption is what worries you, is this just during office hours, or 24x7? In other words, is there a window that can be used to extract all data from the application database to move it to the reporting database? Is the amount of data growing? Is a large part of the data non-volatile? Is the data changed during the reporting process?

Wat license model are you on? Enterprise or Standard Edition? What metrics? Named User Plus or CPU-based licensing? CPU-based might give you the need to license the other host, Named User Plus doesn't. The University might have some special site-license, I don't know that for the US.

How critical is your application? You might consider setting up a standby database (other members of the list were probably wondering why it took me so long before coming up with this ;-). When you configure Data Guard (or even vanilla log shipping when using Standard Edition) you can send all redo to the reporting host, and apply the archives once a day, just before office hours. Then open the database read-only, run all the reports you like all day long, and repeat the process the next day.
This way you have the benefit an almost zero extra load on your application database (no extraction processes, no queries, just redo log forwarding), a standby database in case of disaster which is useful as a reporting database during 'normal' operation. It will also serve you as a spare server during upgrades or maintenance of your application server. I've several customers with a configuration that's used this way. Of course this doubles your need for storage, I don't know what part of your application database is needed for reporting. If it's only a small part of a pretty large database the whole thing might not make any sense at all.

The idea of transportable tablespaces gets much better from 10gR2 on: this release enables you to use an rman backup to create a transportable tablespace from. This avoids making the tablespace in the source database read-only. You just take a backup, and rman creates an auxiliary database as the source database for the transportable tablespaces. Not of much use for you now, but interesting enough to keep in mind for the future. It really works nice.

Just my $0.02
Best regards,

Carel-Jan Engel

If you think education is expensive, try ignorance. (Derek Bok) ===

On Mon, 2006-03-20 at 21:03 -0700, Daniel W. Fink wrote:

> Tricia,
> There are many ways to transfer the data. Database links, Oracle
> Warehouse Builder, export/import, transportable tablespaces, extract to
> sql*loader data files, streams, replication, home grown versions, etc. I
> would start by reading up on ETL (Extract Transform Load) in the Oracle
> Data Warehousing guide or a good book on data warehousing. You might
> find that different tables require different methods. This is not a
> simple job if it is done right.
> Keep in mind several things (major assumptions about your environment here):
> 1) Copying tables that are exactly like your application tables may work
> for a few months, but eventually the queries will become resource hogs.
> That is the motivation behind Transforming, to make the data structures
> more conducive to quick querying.
> 2) Try not to extract every piece of data every time you have to load.
> Again, this may work for a few months, but eventually you will be
> pulling over data that already exists in your current data
> warehouse/mart. Timestamps are your friend (being able to only extract
> last month's data is much faster than extracting 3 years of
> then discard all but last months). Also look at tables that do not
> change (do you need to pull over the STATES_OF_THE_UNITED_STATES table
> every night?) and exclude them from the load if they have not changed.
> If you are not careful, eventually your daily extract will run in 23
> hours 42 the users a whopping 18 minutes of uptime to
> run all their reports.
> 3) One of the downsides to transportable tablespaces is that you cannot
> make the tablespace read only in order to transport it. You can only
> make a tablespace read only when there are no active transactions in the
> database. If you have the ability to stop all transaction activity, this
> is not going to be a problem, but if you can't do can't use
> transportable tablespaces.
> Good luck and you've come to the right place to ask questions and get help.
> Regards,
> Daniel Fink
> Tricia McKellar wrote:
> > Hello,
> >
> > I'm new to oracle dba responsibilities and in need of some direction.
> > I need to implement a process and I'm not sure which technologies
> > and/or processes I should investigate.
> >
> > Our application produces a set of reporting views in the production
> > database (solaris, oracle 9.2). My managers and others are anxious to
> > run reports on the data; I've created a report user and throttled
> > resources for this user, but my goal is to restrict all
> > non-application access to the production database. I would like to
> > automate the copy the reporting data to a dedicated report database on
> > another host, preferably daily.
> >
> > Can you give me some direction on the technologies/processes to copy
> > these tables to another database I might investigate? We cannot
> > shutdown the production database daily. We do use rman to disk, but
> > do not have rman set up with a media manager yet. One of my books
> > mentions "transportable tablespaces"--should I read up on that?
> > Should I do some homework on export/import? One of the reporting views
> > could contain millions of rows, but the other dozen or so tables are
> > comparatively small (thousands of rows).
> >
> > Thank you for any guidance!
> >
> > --Tricia
> >
> > Tricia McKellar
> > Operations & Systems Analyst
> > NC State University
> > --
> >
> >
> >
> >
> >
> --

Received on Tue Mar 21 2006 - 04:39:21 CST

Original text of this message