Home » RDBMS Server » Server Administration » can select on dba_segments view but throwing error when using on a package (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)
 can select on dba_segments view but throwing error when using on a package [message #684407] |
Wed, 26 May 2021 14:28  |
wtolentino
Messages: 422
Registered: March 2005
|
Senior Member |
|
|
i have this package that is throwing an error:
SQL> create or replace package sqldba.sqldba_misc_pkg as
2 function db_size (pOwner varchar2, pSegmentName varchar2, pSegmentType varchar2) return number;
3 procedure help;
4 end sqldba_misc_pkg;
5 /
Package created.
Elapsed: 00:00:00.01
SQL> show errors;
No errors.
SQL>
SQL> create or replace package body sqldba.sqldba_misc_pkg as
2
3 function db_size (pOwner varchar2, pSegmentName varchar2, pSegmentType varchar2) return number is
4 vSize number := 0;
5 begin
6 select nvl(sum(bytes),0) db_size
7 into vSize
8 from dba_segments ds
9 where ds.owner = pOwner
10 and ds.segment_name = decode(pSegmentName,null,ds.segment_name,pSegmentName)
11 and ds.segment_type = decode(pSegmentType,null,ds.segment_type,pSegmentType);
12 return (vSize);
13 end;
14
15 procedure help as
16 begin
17 dbms_output.put_line('Package allows authorized users to get the database size of schema and/or segment');
18 dbms_output.put_line('It is comprised of 1 function and 1 procedures');
19 dbms_output.put_line('...');
20 dbms_output.put_line('Help - this procedure ');
21 dbms_output.put_line('db_size - this function returns the size of the schema and/or segment');
22 dbms_output.put_line('function db_size (pOwner, pSegmentName, pSegmentType)');
23 dbms_output.put_line('db_size function has 3 parameters: schema name, segment name, and segment type');
24 dbms_output.put_line('schema name is required, segment name and segment type is optional');
25 dbms_output.put_line('Examples: ');
26 dbms_output.put_line('.');
27 dbms_output.put_line('.');
28 dbms_output.put_line('To get the schema size');
29 dbms_output.put_line('.');
30 dbms_output.put_line('select sqldba.sqldba_misc_pkg.db_size(''SQLDBA'',null,null) from dual;');
31 dbms_output.put_line('.');
32 dbms_output.put_line('To get the schema and segment size');
33 dbms_output.put_line('.');
34 dbms_output.put_line('select sqldba.sqldba_misc_pkg.db_size(''SQLDBA'',''TABLE_A'',''TABLE'') from dual;');
35 end;
36 end sqldba_misc_pkg;
37 /
Warning: Package Body created with compilation errors.
Elapsed: 00:00:00.05
SQL> show errors;
Errors for PACKAGE BODY SQLDBA.SQLDBA_MISC_PKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5 PL/SQL: SQL Statement ignored
8/12 PL/SQL: ORA-00942: table or view does not exist
SQL>
SQL> describe dba_segments;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
SEGMENT_NAME VARCHAR2(128)
PARTITION_NAME VARCHAR2(128)
SEGMENT_TYPE VARCHAR2(18)
SEGMENT_SUBTYPE VARCHAR2(10)
TABLESPACE_NAME VARCHAR2(30)
HEADER_FILE NUMBER
HEADER_BLOCK NUMBER
BYTES NUMBER
BLOCKS NUMBER
EXTENTS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
RETENTION VARCHAR2(7)
MINRETENTION NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
RELATIVE_FNO NUMBER
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
INMEMORY VARCHAR2(8)
INMEMORY_PRIORITY VARCHAR2(8)
INMEMORY_DISTRIBUTE VARCHAR2(15)
INMEMORY_DUPLICATE VARCHAR2(13)
INMEMORY_COMPRESSION VARCHAR2(17)
CELLMEMORY VARCHAR2(24)
SQL> select substr(user,1,20) username, count(*) cnt from dba_segments;
USERNAME CNT
-------------------- ----------
SQLDBA 13245
SQL>
select substr(grantee,1,10) grantee,
substr(owner,1,10) owner, substr(table_name,1,35) table_name,
substr(grantor,1,10) grantor, substr(privilege,1,10) privilege
from dba_tab_privs
where grantee = 'SQLDBA'
and owner = 'SYS'
and privilege = 'SELECT'
order by table_name;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
---------- ---------- ----------------------------------- ---------- ----------
SQLDBA SYS ALL_UNIFIED_AUDIT_ACTIONS SYS SELECT
SQLDBA SYS ALL_USERS SYS SELECT
SQLDBA SYS AUDIT_NG$ SYS SELECT
SQLDBA SYS AUDIT_UNIFIED_ENABLED_POLICIES SYS SELECT
SQLDBA SYS DBA_2PC_PENDING SYS SELECT
SQLDBA SYS DBA_AUDIT_POLICIES SYS SELECT
SQLDBA SYS DBA_CONSTRAINTS SYS SELECT
SQLDBA SYS DBA_CONS_COLUMNS SYS SELECT
SQLDBA SYS DBA_DB_LINKS SYS SELECT
SQLDBA SYS DBA_ERRORS SYS SELECT
SQLDBA SYS DBA_HIST_SNAPSHOT SYS SELECT
SQLDBA SYS DBA_HIST_WR_CONTROL SYS SELECT
SQLDBA SYS DBA_INDEXES SYS SELECT
SQLDBA SYS DBA_IND_STATISTICS SYS SELECT
SQLDBA SYS DBA_OBJECTS SYS SELECT
SQLDBA SYS DBA_PENDING_TRANSACTIONS SYS SELECT
SQLDBA SYS DBA_ROLE_PRIVS SYS SELECT
SQLDBA SYS DBA_SEQUENCES SYS SELECT
SQLDBA SYS DBA_SOURCE SYS SELECT
SQLDBA SYS DBA_SYNONYMS SYS SELECT
SQLDBA SYS DBA_SYS_PRIVS SYS SELECT
SQLDBA SYS DBA_TABLES SYS SELECT
SQLDBA SYS DBA_TAB_COLS SYS SELECT
SQLDBA SYS DBA_TAB_COLUMNS SYS SELECT
SQLDBA SYS DBA_TAB_COL_STATISTICS SYS SELECT
SQLDBA SYS DBA_TAB_HISTOGRAMS SYS SELECT
SQLDBA SYS DBA_TAB_PRIVS SYS SELECT
SQLDBA SYS DBA_TAB_STATISTICS SYS SELECT
SQLDBA SYS DBA_USERS SYS SELECT
SQLDBA SYS GV_$PROCESS SYS SELECT
SQLDBA SYS GV_$SESSION SYS SELECT
SQLDBA SYS GV_$UNIFIED_AUDIT_TRAIL SYS SELECT
SQLDBA SYS INT$AUDIT_UNIFIED_ENABLED_POL SYS SELECT
SQLDBA SYS OBJ$ SYS SELECT
SQLDBA SYS PENDING_TRANS$ SYS SELECT
SQLDBA SYS PRODUCT_COMPONENT_VERSION SYS SELECT
SQLDBA SYS STMT_AUDIT_OPTION_MAP SYS SELECT
SQLDBA SYS SYSTEM_PRIVILEGE_MAP SYS SELECT
SQLDBA SYS UNIFIED_FGA_AUDIT_TRAIL_V SYS SELECT
SQLDBA SYS USER_INDEXES SYS SELECT
SQLDBA SYS V_$PARAMETER SYS SELECT
SQLDBA SYS V_$SESSION SYS SELECT
42 rows selected.
thank you.
[Updated on: Wed, 26 May 2021 14:45] Report message to a moderator |
|
|
|
|
|
|
|
| Re: can select on dba_segments view but throwing error when using on a package [message #684410 is a reply to message #684409] |
Wed, 26 May 2021 15:24  |
wtolentino
Messages: 422
Registered: March 2005
|
Senior Member |
|
|
thanks Michel and Ed. right it needs a direct grant. i checked on other database like UAT. it does not have a direct grants and the package compiled successfully. but it does not compile on QA they both have no direct grants on DBA_SEGMENTS view.
UAT database
SQL> select substr(grantee,1,20) grantee,
2 substr(owner,1,10) owner, substr(table_name,1,35) table_name,
3 substr(grantor,1,10) grantor, substr(privilege,1,10) privilege
4 from dba_tab_privs
5 where table_name = 'DBA_SEGMENTS'
6 order by table_name;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
-------------------- ---------- ----------------------------------- ---------- ----------
SELECT_CATALOG_ROLE SYS DBA_SEGMENTS SYS SELECT
CTXSYS SYS DBA_SEGMENTS SYS SELECT
MDSYS SYS DBA_SEGMENTS SYS SELECT
SQL> create or replace package sqldba.sqldba_misc_pkg as
2 function db_size (pOwner varchar2, pSegmentName varchar2, pSegmentType varchar2) return number;
3 procedure help;
4 end sqldba_misc_pkg;
5 /
Package created.
Elapsed: 00:00:00.13
SQL> show errors;
No errors.
SQL>
SQL> create or replace package body sqldba.sqldba_misc_pkg as
2
3 function db_size (pOwner varchar2, pSegmentName varchar2, pSegmentType varchar2) return number is
4 vSize number := 0;
5 begin
6 select nvl(sum(bytes),0) db_size
7 into vSize
8 from dba_segments ds
9 where ds.owner = pOwner
10 and ds.segment_name = decode(pSegmentName,null,ds.segment_name,pSegmentName)
11 and ds.segment_type = decode(pSegmentType,null,ds.segment_type,pSegmentType);
12 return (vSize);
13 end;
14
15 procedure help as
16 begin
17 dbms_output.put_line('Package allows authorized users to get the database size of schema and/or segment');
18 dbms_output.put_line('It is comprised of 1 function and 1 procedures');
19 dbms_output.put_line('...');
20 dbms_output.put_line('Help - this procedure ');
21 dbms_output.put_line('db_size - this function returns the size of the schema and/or segment');
22 dbms_output.put_line('function db_size (pOwner, pSegmentName, pSegmentType)');
23 dbms_output.put_line('db_size function has 3 parameters: schema name, segment name, and segment type');
24 dbms_output.put_line('schema name is required, segment name and segment type is optional');
25 dbms_output.put_line('Examples: ');
26 dbms_output.put_line('.');
27 dbms_output.put_line('.');
28 dbms_output.put_line('To get the schema size');
29 dbms_output.put_line('.');
30 dbms_output.put_line('select sqldba.sqldba_misc_pkg.db_size(''SQLDBA'',null,null) from dual;');
31 dbms_output.put_line('.');
32 dbms_output.put_line('To get the schema and segment size');
33 dbms_output.put_line('.');
34 dbms_output.put_line('select sqldba.sqldba_misc_pkg.db_size(''SQLDBA'',''TABLE_A'',''TABLE'') from dual;');
35 end;
36 end sqldba_misc_pkg;
37 /
Package body created.
Elapsed: 00:00:00.04
SQL> show errors;
No errors.
SQL>
SQL> create or replace package body sqldba.sqldba_misc_pkg as
2
3 function db_size (pOwner varchar2, pSegmentName varchar2, pSegmentType varchar2) return number is
4 vSize number := 0;
5 begin
6 select nvl(sum(bytes),0) db_size
7 into vSize
8 from dba_segments ds
9 where ds.owner = pOwner
10 and ds.segment_name = decode(pSegmentName,null,ds.segment_name,pSegmentName)
11 and ds.segment_type = decode(pSegmentType,null,ds.segment_type,pSegmentType);
12 return (vSize);
13 end;
14
15 procedure help as
16 begin
17 dbms_output.put_line('Package allows authorized users to get the database size of schema and/or segment');
18 dbms_output.put_line('It is comprised of 1 function and 1 procedures');
19 dbms_output.put_line('...');
20 dbms_output.put_line('Help - this procedure ');
21 dbms_output.put_line('db_size - this function returns the size of the schema and/or segment');
22 dbms_output.put_line('function db_size (pOwner, pSegmentName, pSegmentType)');
23 dbms_output.put_line('db_size function has 3 parameters: schema name, segment name, and segment type');
24 dbms_output.put_line('schema name is required, segment name and segment type is optional');
25 dbms_output.put_line('Examples: ');
26 dbms_output.put_line('.');
27 dbms_output.put_line('.');
28 dbms_output.put_line('To get the schema size');
29 dbms_output.put_line('.');
30 dbms_output.put_line('select sqldba.sqldba_misc_pkg.db_size(''SQLDBA'',null,null) from dual;');
31 dbms_output.put_line('.');
32 dbms_output.put_line('To get the schema and segment size');
33 dbms_output.put_line('.');
34 dbms_output.put_line('select sqldba.sqldba_misc_pkg.db_size(''SQLDBA'',''TABLE_A'',''TABLE'') from dual;');
35 end;
36 end sqldba_misc_pkg;
37 /
Warning: Package Body created with compilation errors.
Elapsed: 00:00:00.05
SQL> show errors;
Errors for PACKAGE BODY SQLDBA.SQLDBA_MISC_PKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5 PL/SQL: SQL Statement ignored
8/12 PL/SQL: ORA-00942: table or view does not exist
SQL>
[Updated on: Wed, 26 May 2021 15:26] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 04 04:48:38 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
