Anuroop Challa

normal run slow as i used in older days =========================== exec dbms_stats.gather_schema_stats(ownname=>'USER_NAME',estimate_percent=>30,cascade=>TRUE); To speed up stats.gather_schema ============================ exec dbms_stats.gather_schema_stats('USER_NAME',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE, degree => DBMS_STATS.DEFAULT_DEGREE); Thanks for the visit !  Any doubts or correction/suggestions, please comment below it helps me to improve in further posts.

 set pages 500  set lines 125  SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type",  d.extent_management "Management",  TO_NUMBER(TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99999990D900')) "Size (M)",  TO_NUMBER(TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 ,'99999990D900')) "Used (M)",  TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Used %"  FROM  sys.dba_tablespaces d,  (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,  (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f  WHERE  d.tablespace_name = a.tablespace_name(+)  AND d.tablespace_name = f.tablespace_name(+)  AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')  UNION ALL  SELECT d.status "Status",  d.tablespace_name "Name",  d.contents

Please set the below: ============== suggestions from oracle(Service request) as fallows=============== alter system set "_dbms_sql_security_level" = 384 scope=spfile; and bounce the instance. =================================================================== Cause of this error is setting the security level using parameter _dbms_sql_security_level. The system parameter "_dbms_sql_security_level" controls the security in DBMS_SQL in the following ways: a. Scan protection DBMS_SQL will be locked out with access denied error once an invalid cursor is detected. This scan protection security feature is on by default. To back out or disable this security feature set _dbms_sql_security_level system parameter with 0x80 (128) bit. b. Privilege checking DBMS_SQL will error out if the effective userid and roles of the caller to dbms_sql subprogram is not the same as those of the caller of the most recent parse operation. Level 0 Level 0 allows all DBM

set markup html on spool on spool db_undo_info.html set echo on show parameter undo select * from v$database; select * from v$instance; select tablespace_name, file_id, sum(bytes)/1024/1024 a,sum(maxbytes)/1024/1024 b,autoextensible from dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces where retention like '%GUARANTEE' ) group by file_id, tablespace_name, autoextensible order by tablespace_name; select tablespace_name, file_id, bytes/1024/1024/1024,maxbytes/1024/1024/1024,autoextensible,STATUS,ONLINE_STATUS from dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces where retention like '%GUARANTEE' ) order by tablespace_name; select count(status) from dba_undo_extents where status = 'UNEXPIRED'; select count(status) from dba_undo_extents where status = 'EXPIRED'; select count(status) from dba_undo_extents where status = 'ACTIVE'; select a.inst_id, a.ksppinm &q