Re: copying tables to another db, oracle 9.2

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 data...to 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 minutes...giving 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 this...you 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l