Undo issue

In Undo Segments there are three types of extents, they are

Unexpired Extents – Undo data whose age is less than the undo retention period.
Expired Extents – Undo data whose age is greater than the undo retention period.
Active Extents – Undo data that is part of the active transaction.

-----------------------------------
Check the overall status for undos.
-----------------------------------

SQL> select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB
from dba_undo_extents group by tablespace_name, status;

TBS STATUS  GB

UNDO_TDS EXPIRED  5.95526123046875
UNDO_TDS UNEXPIRED 0.0001220703125

--------------------------

SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"  FROM v$undostat;

UNDO_BLK_PER_SEC
1241.68

----------------------
Undo Blocks per Second
----------------------


SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",       
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",       
       ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" 
FROM (       
      SELECT SUM(a.bytes) undo_size         
        FROM v$datafile a,v$tablespace b,dba_tablespaces c         
       WHERE c.contents = 'UNDO'         
         AND c.status = 'ONLINE'         
         AND b.name = c.tablespace_name           
         AND a.ts# = b.ts#       
      ) d,v$parameter e,v$parameter f,( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec         
                                          FROM v$undostat 
                                      ) g WHERE e.name = 'undo_retention'  AND f.name = 'db_block_size'

          
ACTUAL_UNDO_SIZE_MB  UNDO_RETENTION_SEC  OPTIMAL_UNDO_RETENTION_SEC
-------------------  ------------------  --------------------------
6108      900       630

----------------------
Optimal Undo Retention
----------------------


SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",       

ACTUAL_UNDO_SIZE_MB  UNDO_RETENTION_SEC<]" 
    FROM (        SELECT SUM(a.bytes) undo_size         
        FROM v$datafile a, v$tablespace b, dba_tablespaces c 
       WHERE c.contents = 'UNDO'         
 tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from 
SIZE_MB FREE_MB  NEEDED_UNDO_SIZE
-------------------  ------------------  -----------------
6108      900      8730.5625


------------------------------------------------------
Calculate Needed UNDO Size for given Database Activity
------------------------------------------------------


select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from  dba_data_files group by tablespace_name union all
 select  tablespace_name || '  **TEMP**'
 , sum(bytes)/1024/1024 used_mb
 from  dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
 from  dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/


TBS  SIZE_MB FREE_MB %USED
---  ------- ------- -----

UNDO_TDS 6108   10  99

1. What are the current UNDO settings (including UNDERSCORE settings)? 

SET PAGESIZE 900 
COL NAME FORMAT A32 
COL VALUE FORMAT A40 
SPOOL UNDOPARAMS.TXT 
/* Database identification */ 
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database; 
select * from V$version where banner like 'Oracle Database%'; 
/* Internal AUM settings */ 
select nam.ksppinm NAME, val.KSPPSTVL VALUE 
from x$ksppi nam, x$ksppsv val 
where nam.indx = val.indx and (nam.ksppinm like '%undo%' or nam.ksppinm like '_smu%' or nam.ksppinm in ('event', '_first_spare_parameter' ) ) 
order by 1; 
SPOOL OFF 

2. What are the various statuses for Undo Extents? 
SELECT DISTINCT STATUS, SUM(BYTES)/1024/0124 "BYTES (MB)", COUNT(*) 
FROM DBA_UNDO_EXTENTS 
GROUP BY STATUS; 

3. Please provide the TUNED_UNDORETENTION details from V$UNDOSTAT. 
Note that the nls_date_format is needed so that the time is displayed. 

ALTER SESSION SET nls_date_format = 'DD/MM/YYYY HH24:MI:SS'; 
SET PAGESIZE 900 
SET LINESIZE 255 
SPOOL UNDOSTAT.TXT 
/* Database identification */ 
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database; 
select * from V$version where banner like 'Oracle Database%'; 
/* Tuned Undo Retention */ 
SELECT MAX(TUNED_UNDORETENTION), MAX(MAXQUERYLEN), MAX(NOSPACEERRCNT), MAX(EXPSTEALCNT) FROM V$UNDOSTAT; 
SELECT BEGIN_TIME, END_TIME, TUNED_UNDORETENTION, MAXQUERYLEN, MAXQUERYID, NOSPACEERRCNT, EXPSTEALCNT, UNDOBLKS, TXNCOUNT FROM V$UNDOSTAT; 
SPOOL OFF 

4. For the UNDO TableSpace, what is the RETENTION GUARANTEE? 
SELECT tablespace_name, retention, min_extlen FROM dba_tablespaces WHERE contents = 'UNDO'; 

5. How much free space is in the UNDO TableSpace? 
SELECT SUM(BYTES)/1024/1024 "BYTES (MB)" FROM dba_free_space WHERE tablespace_name='&UNDOTBS'; 

6. For the Data Files used for Undo, what is the autoextend setting? 
COL AUTOEXTENSIBLE FORMAT A14 
SELECT FILE_ID, BYTES/1024/1024 AS "BYTES (MB)", MAXBYTES/1024/1024 AS "MAXBYTES (MB)", AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='&UNDOTBS'; 

7. set pagesize 50 
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; 

Tablespace usage

SET LINES 150
SET pages 300
col "Tablespace" FOR a30
col "Total MB" FOR a15
col "Free MB" FOR a15
col "Used MB" FOR a15
col "% Used" FOR a15      
SELECT
  tablespace_name "Tablespace",
  d.STATUS "Status",
  TO_CHAR((a.bytes / 1048576),'99,999,990.900') "Total MB",
  TO_CHAR(((DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),'99,999,990.900') "Free MB",
  TO_CHAR(((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),'99,999,990.900') "Used MB",
  TO_CHAR((((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576)*100) / (a.bytes / 1048576),'999,999.9') "% Used"
FROM
  (sys.dba_tablespaces d JOIN sys.sm$ts_avail a USING (tablespace_name))
  LEFT OUTER JOIN sys.sm$ts_free f USING (tablespace_name)
where tablespace_name='PSAPIDOCD'
ORDER BY 6
/

select file_name,bytes/(1024*1024*1024) from dba_data_files where tablespace_name='PSAPTF90' order by 1;

GRANT SELECT ON SCHEMA OBJECTS

GRANT SELECT ON SCHEMA OBJECTS TO USER THROUGH ROLE
set heading off;

select 'grant select on '||owner|| '.' ||object_name || to ROLE_NAME;' from dba_objects where owner='SCHEMA_NAME';

grant ROLE_NAME to USER_NAME;

Find a role
select * from dba_roles where role like '&role'
/
Show what roles are granted to a userselect grantee,granted_role,admin_option from dba_role_privs
where grantee like ('&username')
/
Show what table privileges are granted to a role
select owner || '.' || table_name "TABLE",column_name,privilege,grantable
from role_tab_privs where role like '&role'
/
Show what system privileges are granted to a role
select privilege,admin_option from role_sys_privs where role like '&role'
/

Archivelog deletion using RMAN

rman target /

list archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-3';

delete noprompt archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-3';

If you know that the logs are unavailable because you deleted them by using an operating system utility, then run the following command at the RMAN prompt to update RMAN metadata:
CROSSCHECK ARCHIVELOG ALL;

It is always better to use RMAN to delete logs than to use an operating system utility. The easiest method to remove unwanted logs is to specify the DELETE INPUT option when backing up archived logs.

For example, enter:
BACKUP DEVICE TYPE sbt ARCHIVELOG ALL DELETE ALL INPUT;

PACKAGE BACKUP in ORACLE

PACKAGE BACKUP
--------------
set heading off
set echo off
set flush off
set pagesize 50000
set linesize 32767
set long 99999999
spool PACKAGE_NAME.pks
select dbms_metadata.get_ddl('PACKAGE','','') from dual ;
spool off;

OR

set linesize 500
set pagesize 100000
set head off
set verify off
spool PACKAGE_NAME.pks
select text from dba_source where owner=''and name='';
spool off

OR

select text from dba_source where owner='' and name='PACKAGE_NAME' and type='PACKAGE' order by line;

PACKAGE BODY BACKUP
-------------------
select dbms_metadata.get_ddl('PACKAGE_BODY','','') from dual ;

OR

set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool PACKAGE_BODY_NAME.pkb
select text from dba_source where owner=''and name='';
spool off

OR

select text from dba_source where owner='' and name='PACKAGE_BODY_NAME' and type='PACKAGE BODY' order by line;

DROP PACKAGE
------------
set pagesize 0
set linesize 800
col object_name for a30
col object_type for a25
col owner for a25
spool package_drop.sql
select owner,object_name,object_type from dba_objects where object_name='';
drop package .;
spool Off;

GRANT USER ACCESS ON SCHEMA

user needs read only access on existing schema

----------------------------------------------

select 'grant '||decode(object_type,'TABLE','select','VIEW','select','SEQUENCE','SELECT ','PROCEDURE','EXECUTE ','PACKAGE','EXECUTE ','TRIGGER','EXECUTE ','FUNCTION','EXECUTE ')|| ' on '||owner||'.'||object_name||' to target user' from dba_objects where OWNER='source user' and object_type not in ('INDEX','PACKAGE BODY','DATABASE LINK','LOB');

user needs full access on existing schema

-----------------------------------------

select 'grant '||decode(object_type,'TABLE','select,insert,delete,update ','VIEW','select,insert,delete,update ','SEQUENCE','SELECT ','PROCEDURE','EXECUTE ','PACKAGE','EXECUTE ','TRIGGER','EXECUTE ','FUNCTION','EXECUTE ')|| ' on '||owner||'.'||object_name||' to targetuser' from dba_objects where OWNER='source user' and object_type not in 

('INDEX','PACKAGE BODY','DATABASE LINK','LOB');

Related:

-------

Running the SQL*Plus script below (substituting &Owner and &NewUser) will produce a listing of all the permissions to allow the New User to access all the objects owned by OWNER. Review the output of the script and then run it to Grant the new permissions to NewUser.

Set pagesize 0

define OWNER=

define NEWUSER=

Spool new_grants.txt

Select

decode(OBJECT_TYPE,

'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES ON '||'&OWNER'||'.',

'VIEW','GRANT SELECT ON '||'&OWNER'||'.',

'SEQUENCE','GRANT SELECT ON '||'&OWNER'||'.',

'PROCEDURE','GRANT EXECUTE ON '||'&OWNER'||'.',

'PACKAGE','GRANT EXECUTE ON '||'&OWNER'||'.',

'FUNCTION','GRANT EXECUTE ON '||'&OWNER'||'.' )||object_name||' TO &NewUser;'

From USER_OBJECTS where OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE', 'FUNCTION')

Order By OBJECT_TYPE;

Spool Off;

exit

Kill sesion using script in rac database

BEGIN
  FOR r IN (select sid,serial#,inst_id from gv$session where username = 'user')
  LOOP
    EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid || ',' || r.serial# ||',@' ||r.inst_id|| '''immediate';
  END LOOP;
END;
/