Re: Any valid security concerns using Data Pump over conventional exp/imp?
Date: Fri, 3 Sep 2010 11:31:19 +0100
Message-ID: <OF92B54DBD.4B161371-ON80257793.00369D04-80257793.0039CCA8_at_ons.gsi.gov.uk>
Alternatively it might be worth noting that expdp seems to be "broken" in 11.1.0.7
I have a database with ~350,00 objects and expdp now appears to hang at the estimate phase for any expdp operation.
With testing I've shown that the operation will actually complete but takes 17 hours (even for a schema containing 1 table of 100 rows). The time taken is proportional to the number of objects in the database i.e. the same operation on database containing 50,000 objects completes in 15 minutes. For those of you that are interested the time taken appears to be on runing;
select /*+rule*/
sys_xmlgen(value(ku$), xmlformat.createformat2('TABLE_DATA_T', '7')),
0, ku$.base_obj.name, ku$.base_obj.owner_name, 'TABLE',
to_char(ku$.bytes_alloc), to_char(ku$.et_parallel), ku$.fgac,
ku$.nonscoped_ref, ku$.xmlschemacols, ku$.name, ku$.name, 'TABLE_DATA', ku$.part_name, ku$.parttype, ku$.property, ku$.refpar_level, ku$.schema_obj.owner_name, ku$.ts_name, ku$.schema_obj.name, ku$.trigflag,
decode(
ku$.schema_obj.type_num,
2, decode(
bitand(ku$.property, 8224),
8224, 'NESTED PARTITION', 8192, 'NESTED TABLE', 'TABLE'
),
19, decode(
bitand(ku$.property, 8224),
8224, 'NESTED PARTITION',
'PARTITION'
),
20, 'PARTITION',
'SUBPARTITION'
),
to_char(ku$.unload_method), ku$.xmltype_fmts from sys.ku$_10_2_table_data_view ku$
where not bitand(ku$.base_obj.flags, 128) != 0 and not(bitand(ku$.base_obj.flags, 16) = 16) and ku$.base_obj.obj_num in(
select *
from table(DBMS_METADATA.fetch_objnums(100001)))
I've tried the various "fixes" for other occurencies of expdp being slow e.g. version=10.2.04 , exclude=statistics but they haven't had any effect.
I don't regard my enviroment as particularly strange and everything worked in 10.2.0.4 so I'm little suprised that I haven't seen any other reports of the same problem.
I have an SR outstanding but so far the only response I've had is "that it looks like an impdp bug and there is no patch for it". I pointed out that I'm running expdp but have no response for three days which unfortuately is what I've come to expect from MOS these days.
Cheers,
Ian
|---------+----------------------------->
| | knecht.stefan_at_gmai|
| | l.com |
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 02/09/2010 11:58 |
| | Please respond to |
| | knecht.stefan |
| | |
|---------+-----------------------------> >--------------------------------------------------------------------------------------------------------------------------------------------------| | | | To: bwmyers_at_gmail.com | | cc: oracle-l_at_freelists.org | | Subject: Re: Any valid security concerns using Data Pump over conventional exp/imp? | >--------------------------------------------------------------------------------------------------------------------------------------------------|
Might want to make them aware of the fact that in 11g "exp" is desupported ;-)
Stefan P Knecht
CEO & Founder
s_at_10046.ch
10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland
Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info_at_10046.ch
http://www.10046.ch
On Thu, Sep 2, 2010 at 1:53 AM, Bill Myers <bwmyers_at_gmail.com> wrote: Meant to post this to all others too (long time reader, first time poster to Oracle-L...so bear with me if I don't Reply All).
Thanks for the input and validation, great answers all around. These are 10g databases, all internally managed PKI. Here's the kicker too...they won't even allow DP on development and testing databases.
I'm definitely not discounting the decades of experience that these two have on me (one of the reasons I took the job), but it comes down to new technologies displacing outdated policies in my opinion.
Thanks again!
Bill
This email was received from the INTERNET and scanned by the Government Secure Intranet anti-virus service supplied by Cable&Wireless Worldwide in partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) In case of problems, please call your organisation?x02019;s IT Helpdesk. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.
For the latest data on the economy and society consult National Statistics at http://www.ons.gov.uk
Please Note: Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications
Legal Disclaimer : Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics
The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless Worldwide in partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) On leaving the GSi this email was certified virus free. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 03 2010 - 05:31:19 CDT