Re: Export user DDL and privileges?

From: Norman Dunbar <oracle_at_dunbar-it.co.uk>
Date: Mon, 30 Jul 2012 11:20:57 +0100
Message-ID: <50166009.3000509_at_dunbar-it.co.uk>

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

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

Received on Mon Jul 30 2012 - 05:20:57 CDT