From oracle-l-bounce@freelists.org Mon Oct 10 03:56:06 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9A8u6Wv021867 for ; Mon, 10 Oct 2005 03:56:06 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9A8u0vX021843 for ; Mon, 10 Oct 2005 03:56:00 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E08BB1F999B; Mon, 10 Oct 2005 03:55:56 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16317-03; Mon, 10 Oct 2005 03:55:56 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 68B2E1F991F; Mon, 10 Oct 2005 03:55:56 -0500 (EST) Message-ID: <434A3A19.9070302@inter.net.il> Date: Mon, 10 Oct 2005 11:53:29 +0200 From: Yechiel Adar User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: ORACLE-L Subject: DBMS_METADATA for SYSTEM_GRANT for ROLE AQ_ADMINISTRATOR_ROLE return error Content-Type: text/plain; charset=ISO-8859-1; format=flowed X-archive-position: 26613 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: adar76@inter.net.il Precedence: normal Reply-To: adar76@inter.net.il X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-2.4 required=5.0 tests=AWL,BAYES_00, FROM_ENDS_IN_NUMS autolearn=no version=2.63 Hello Oracle 9.2.0.6 on windows. I am using dbms_metadata to get all the system privileges granted to roles. I am using: select 'select distinct trim(dbms_metadata.get_granted_ddl(''SYSTEM_GRANT'' , ' || '''' || role || ''')) || '';'' from dual;' from dba_roles , dba_sys_privs where role = grantee; This produce this statement for role AQ_ADMINISTRATOR_ROLE: select trim(dbms_metadata.get_granted_ddl('SYSTEM_GRANT','AQ_ADMINISTRATOR_ROLE') || ';' from dual; This command results in: specified object of type SYSTEM_GRANT not found. When I do: select * from dba_roles , dba_sys_privs where role = grantee; I get the grants for AQ_ADMINISTRATOR_ROLE. This error is only for AQ_ADMINISTRATOR_ROLE. For all the other roles I get the ddl OK. I tried it on 2 databases with the same result. I searched Metalink and google with no results. I am going to open tar but I thought to ask also the list. Please ignore the <|| ';'> it is leftover from previous test. TIA -- Adar Yechiel Rechovot, Israel -- http://www.freelists.org/webpage/oracle-l