Re: Export user DDL and privileges?
- From: Norman Dunbar <oracle@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 30 Jul 2012 11:20:57 +0100
On 27/07/12 18:25, Tim Hall wrote:
> You scared me so I did a little test. The lack of a tablespace quota
> doesn't affect it as far as I can see. Am I missing something?
Try:
alter user tim_test quota unlimited on system; (chose your tablespace
well!) and see if it still works. The problem I found in dbms_metadata
is a proper numeric quota will work fine, but unlimited quotas are not
extracted and, if the account has unlimited quota, but no numeric
quotas, dbms_metadata barfs at the tablespace_quota bit, and that barf
brings down your union all query.
It's a bug in Oracle 10.2.0.5 by the look of things, it works fine on
11202/11203 on SLES 11.2 but barfs, as described, on 10205 on HP-UX.
Shame it's the 10205 users I want to extract the metadata for, but I've
"fixed" it for that version by commenting out the call to dbms_metadata
and adding in this instead:
select 'alter user '|| username || ' quota ' ||
decode(max_bytes, -1, 'unlimited',
to_char(max_bytes/1024/1024) ||'M') ||
' on ' || tablespace_name || ';'
from dba_ts_quotas
where username = :v_username;
Cheers,
Norm.
--
Norman Dunbar
Dunbar IT Consultants Ltd
Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL
Company Number: 05132767
--
//www.freelists.org/webpage/oracle-l
Other related posts:
