To Speed up gather stats run as per my work travel learning

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.

Tablespace script with Temp and Undo in same

 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 "Type",
 d.extent_management "Management",
 TO_NUMBER(TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99999990D900')) "Size (M)",
 TO_NUMBER(TO_CHAR(NVL(t.bytes, 0)/1024/1024,'99999990D900' )) "Used (M)",
 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990D00') "Used %"
 FROM
 sys.dba_tablespaces d,
 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
 (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
 WHERE
 d.tablespace_name = a.tablespace_name(+)
 AND d.tablespace_name = t.tablespace_name(+)
 AND d.extent_management like 'LOCAL'
 AND d.contents like 'TEMPORARY' order by 7 desc;

Thanks for the visit !

 Any doubts or correction/suggestions, please comment below it helps me to improve in further posts.

ORA-29471 DBMS_SQL SECURITY ERROR

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 DBMS_SQL operations on the cursor without any security
checks. The cursor may be fetched from, and even rebound
and
reexecuted,
by code running with a different effective userid or roles
than in effect at the time the cursor was parsed.
Level 1 (default privilege checking level) Level
1 requires that the effective userid and roles of the caller to
dbms_sql for bind and execute operations on the cursor must be the same
as those of the caller of the most recent parse operation on the cursor.
Level 2 Level
2 requires that the effective userid and roles of the caller to
dbms_sql for all bind, execute, define, describe, and fetch operations
on the cursor must be the same as those of the caller of the most recent
parse operation on the cursor.
c. Security Enforcement in open_cursor
DBMS_SQL does not allow open_cursor with lower security level (level 0) to be specified.
Security enforcement in open_cursor is on by default. To back out or disable security
enforcement in open_cursor set _dbms_sql_security_level system parameter with 0x100 (256) bit.
By default, level 0 is disallowed. Level 0 is allowed only when security enforcement in open_cursor set by the system parameter
_dbms_sql_security_level is turned off (0x100).
When setting _dbms_sql_security_level with 384 (0x180), it means:
no security checks(level 0), no scan protection (0x80), and no security enforcement in open_cursor (0x100)

Thanks for the visit !

 Any doubts or correction/suggestions, please comment below it helps me to improve in further posts.

ORA-30036: unable to extend segment by string in undo tablespace- check script

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 "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value" from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.inst_id=b.inst_id and b.inst_id=c.inst_id
and a.ksppinm in ('_undo_autotune', '_smu_debug_mode', '_highthreshold_undoretention', 'undo_tablespace','undo_retention','undo_management') order by 2;

select tablespace_name,retention from dba_tablespaces where retention like '%GUARANTEE';

SELECT TO_CHAR(BEGIN_TIME,'MM/DD/YYYYHH24:MI:SS')BEGIN_TIME, MAXQUERYLEN,TUNED_UNDORETENTION FROM V$UNDOSTAT;

select * from v$undostat order by begin_time;

select tablespace_name, retention from dba_tablespaces where tablespace_name = (select value from v$parameter where name = 'undo_tablespace');

select file_name, autoextensible from dba_data_files where tablespace_name = (select value from v$parameter where name = 'undo_tablespace');

select value from v$parameter where name = 'undo_management';

select value from v$parameter where name = 'undo_retention';

select autoextensible from dba_data_files where tablespace_name=(select value from v$parameter where name = 'undo_tablespace');

select status,count(*),tablespace_name from dba_rollback_segs group by status,tablespace_name;

select sum(bytes) "UNEXPIRED BYTES" from dba_undo_extents where

tablespace_name=(select value from v$parameter where name = 'undo_tablespace') and status='UNEXPIRED';

select sum(bytes) "EXPIRED BYTES" from dba_undo_extents where tablespace_name=(select value from v$parameter where name = 'undo_tablespace') and status='EXPIRED';

SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

select avg(maxquerylen) from DBA_HIST_UNDOSTAT;
select avg(maxquerylen) from v$UNDOSTAT;

select max(maxquerylen),max(tuned_undoretention) from v$undostat;
select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT;

select sum(bytes) from dba_free_space where tablespace_name=(select value from v$parameter where name = 'undo_tablespace');

select * from dba_tablespace_usage_metrics where TABLESPACE_NAME = (select value from v$parameter where name = 'undo_tablespace');

set pagesize 25
set linesize 100
column UNXPSTEALCNT heading "# Unexpired|Stolen"
column EXPSTEALCNT heading "# Expired|Reused"
column SSOLDERRCNT heading "ORA-1555|Error"
column NOSPACEERRCNT heading "Out-Of-space|Error"
column MAXQUERYLEN heading "Max Query|Length"
select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN, TUNED_UNDORETENTION
from gv$undostat
order by inst_id, begin_time;

spool off
set markup html off

Thanks for the visit !

 Any doubts or correction/suggestions, please comment below it helps me to improve in further posts.