Prev: Quick move tablespace
Next: LONG to CLOB within a trigger - alternative to synching data values?
From: Helio Dias on 7 Jan 2010 07:19
On Jan 6, 10:46 pm, Cliff wrote:
> Hey all,
>
> I was looking for some guidance.  I need to do some stat analysis on a
> production audit table.  One problem is that there are no indexes on
> the audit table and spooling via sqlplus would take years.
>
> Using Export on the server, the disk space required for the dmp file is
> about 40 G ( per the DBA ).  This space is not available on the UNIX
> box.
>
> Would running EXP locally be a valid option?
>
> I realize that any solution is going to take a while of processing
> time; it's just that I don't want to get 100-hours in and then find a
> fatal flaw.
>
> Thoughts?
>
> TIA,
> Cliff

Hi Cliff,
You have also a option to export directly to pipe on Unix e.g.:
mknod exp.pipe p
gzip < exp.pipe > compactdmp.exp.gz &

Then you just have to EXP to exp.pipe e.g.:
exp file=exp.pipe

Another good solution is import without actually do the export, using
datapump and network link.

I believe that the best solution will be using import throught network
link , using the WHERE clause as Jonathan told.

Regards,
Hélio Dias
From: hpuxrac on 7 Jan 2010 08:10
On Jan 6, 7:46 pm, Cliff wrote:

snip

> Hey all,
>
> I was looking for some guidance.  I need to do some stat analysis on a
> production audit table.  One problem is that there are no indexes on
> the audit table and spooling via sqlplus would take years.
>
> Using Export on the server, the disk space required for the dmp file is
> about 40 G ( per the DBA ).  This space is not available on the UNIX
> box.
>
> Would running EXP locally be a valid option?
>
> I realize that any solution is going to take a while of processing
> time; it's just that I don't want to get 100-hours in and then find a
> fatal flaw.
>
> Thoughts?
>
> TIA,
> Cliff

Test whatever you are going to do on a smaller system ( test system )
with a smaller amount of data first so that you know what your plan
is.

Then start figuring out what will change and what else needs to be
verified before doing this on a larger system.

I recommend that you work with your DBA and not some internet
newsgroup on figuring out what your plan should be and coming to an
agreement that includes a step by step planning and verification
process.
From: Mladen Gogala on 7 Jan 2010 11:57
On Thu, 07 Jan 2010 11:41:25 +0100, Thomas Kellerer wrote:

> Mladen Gogala, 07.01.2010 04:44:
>> Running EXP is illegal in Texas.
>
> How can a state law prohibit running of a "normal" computer program?

That was a joke. Apparently, not a very good one or this clarification
wouldn't have been needed.



--
http://mgogala.byethost5.com
From: Mladen Gogala on 7 Jan 2010 11:58
On Thu, 07 Jan 2010 10:37:08 +0000, Jonathan Lewis wrote:

> how about:
> create audit_clone nologging
> as
> select (required columns) from audit_table(a)remote ?

That's going to do wonders on the remote rollback segments.



--
http://mgogala.byethost5.com
From: Jonathan Lewis on 7 Jan 2010 13:38
"Mladen Gogala" <no(a)email.here.invalid> wrote in message
news:pan.2010.01.07.16.58.25(a)email.here.invalid...
> On Thu, 07 Jan 2010 10:37:08 +0000, Jonathan Lewis wrote:
>
>> how about:
>> create audit_clone nologging
>> as
>> select (required columns) from audit_table(a)remote ?
>
> That's going to do wonders on the remote rollback segments.
>
>
>
> --
> http://mgogala.byethost5.com


It shouldn't generate any more undo and redo on "remote"
than the export; and if you're worried about that you should
also be worried about the changes of the export failing with
ORA-01555 anyway.

True, the remote select will acquire a transaction table slot,
and stop one undo segment from wrapping until the CTAS
is complete - and that can have unpleasant side effects (like
the example I blogged about
http://jonathanlewis.wordpress.com/2009/10/07/undone/ ),
so it will be worth testing how fast the CTAS can run with
a "rownum" limit to get an estimate of how long the entire
operation might take.

Bear in mind, the CTAS may operate faster than the select
used by exp (and that's something worth testing), and it's
always possible that the analysis of the audit_table doesn't
need all the columns - and the CTAS can be selective on
columns, unlike the export which has to select all columns.


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com



First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Quick move tablespace
Next: LONG to CLOB within a trigger - alternative to synching data values?