Oracle role info for schema

schema role info
user role info

col pr head "Profile" for a8
col rn head "Resource" for a25
col rt head "Type" for a10
col li head "Value" for a10
break on pr skip

prompt
prompt Profile Details
prompt ===============

select p.profile pr,p.resource_name rn,p.resource_type rt,p.limit li from dba_users u,dba_profiles p
where u.profile=p.profile
and u.username='&&user';

col gr head "Grantor" for a8
col tn head "Object" for a20
col ow head "Owner" for a8
col pr head "Privilege" for a10

prompt
prompt Object Privileges
prompt =================

select t.grantor gr,t.table_name tn,t.owner ow,t.privilege pr from dba_tab_privs t
where t.grantee='&&user';

col cn head "Column" for a20

prompt
prompt Column Privileges
prompt =================
select c.grantor gr,c.column_name cn,c.table_name tn,c.owner ow,c.privilege pr from dba_col_privs c
where c.grantee='&&user';

col ad head "Adm" for a3
col pr head "Privilege" for a30

prompt
prompt System Privileges
prompt =================
select s.privilege pr,s.admin_option ad from dba_sys_privs s
where s.grantee='&&user';

col gr head "Granted Role" for a30
col dr head "Def" for a3
col ad head "Adm" for a3
prompt
prompt Role Privileges
prompt ===============
select r.granted_role gr,r.default_role dr,r.admin_option ad from dba_role_privs r
where r.grantee='&&user';

Profile Details
===============
Enter value for user: SCOTT
old   3: and u.username='&&user'
new   3: and u.username='SCOTT'

Profile  Resource                  Type       Value
-------- ------------------------- ---------- ----------
DEFAULT  COMPOSITE_LIMIT           KERNEL     UNLIMITED
         SESSIONS_PER_USER         KERNEL     UNLIMITED
         CPU_PER_SESSION           KERNEL     UNLIMITED
         CPU_PER_CALL              KERNEL     UNLIMITED
         LOGICAL_READS_PER_SESSION KERNEL     UNLIMITED
         LOGICAL_READS_PER_CALL    KERNEL     UNLIMITED
         IDLE_TIME                 KERNEL     UNLIMITED
         CONNECT_TIME              KERNEL     UNLIMITED
         PRIVATE_SGA               KERNEL     UNLIMITED
         FAILED_LOGIN_ATTEMPTS     PASSWORD   10
         PASSWORD_LIFE_TIME        PASSWORD   180
         PASSWORD_REUSE_TIME       PASSWORD   UNLIMITED
         PASSWORD_REUSE_MAX        PASSWORD   UNLIMITED
         PASSWORD_VERIFY_FUNCTION  PASSWORD   NULL
         PASSWORD_LOCK_TIME        PASSWORD   1
         PASSWORD_GRACE_TIME       PASSWORD   7

16 rows selected.


Object Privileges
=================
old   2: where t.grantee='&&user'
new   2: where t.grantee='SCOTT'

Grantor  Object               Owner    Privilege
-------- -------------------- -------- ----------
SYS      DBMS_REDEFINITION    SYS      EXECUTE


Column Privileges
=================
old   2: where c.grantee='&&user'
new   2: where c.grantee='SCOTT'

no rows selected


System Privileges
=================
old   2: where s.grantee='&&user'
new   2: where s.grantee='SCOTT'

Privilege                      Adm
------------------------------ ---
UNLIMITED TABLESPACE           NO


Role Privileges
===============
old   2: where r.grantee='&&user'
new   2: where r.grantee='SCOTT'

Granted Role                   Def Adm
------------------------------ --- ---
RESOURCE                       YES NO
DBA                            YES NO
CONNECT                        YES NO

Oracle AWR Report for a Single SQL Statement

Generating an AWR Report for a Single SQL Statement
AWR for single sql
Oracle AWR report for single sql



SQL> select DBID from v$database ;

DBID
----------
1267852645

select SNAP_ID,BEGIN_INTERVAL_TIME from dba_hist_snapshot ;


@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

65j29t2qc8cqt


sys> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  204677159 xxxDB               1 xxxdb


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text

Type Specified:  text


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 204677159         1 xxxDB        aptdb        apt-xxxx-01
                                                .xxxxxxxx

Using  204677159 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
xxxdb        xxxDB     3937 17 Nov 2011 00:00      1
                               3938 17 Nov 2011 01:00      1
                               3939 17 Nov 2011 02:00      1
                               3940 17 Nov 2011 03:00      1
                               3941 17 Nov 2011 04:00      1
                               3942 17 Nov 2011 05:00      1
                               3943 17 Nov 2011 06:00      1
                               3944 17 Nov 2011 07:00      1
                               3945 17 Nov 2011 08:00      1
                               3946 17 Nov 2011 09:00      1
                               3947 17 Nov 2011 10:00      1
                               3948 17 Nov 2011 11:00      1
                               3949 17 Nov 2011 12:00      1
                               3950 17 Nov 2011 13:00      1
                               3951 17 Nov 2011 14:00      1
                               3952 17 Nov 2011 15:00      1
                               3953 17 Nov 2011 16:00      1
                               3954 17 Nov 2011 17:00      1
                               3955 17 Nov 2011 18:00      1
                               3956 17 Nov 2011 19:00      1
                               3957 17 Nov 2011 20:00      1
                               3958 17 Nov 2011 21:00      1
                               3959 17 Nov 2011 22:00      1
                               3960 17 Nov 2011 23:00      1
                               3961 18 Nov 2011 00:00      1
                               3962 18 Nov 2011 01:00      1
                               3963 18 Nov 2011 02:00      1
                               3964 18 Nov 2011 03:00      1
                               3965 18 Nov 2011 04:00      1
                               3966 18 Nov 2011 05:00      1
                               3967 18 Nov 2011 06:00      1
                               3968 18 Nov 2011 07:00      1
                               3969 18 Nov 2011 08:00      1
                               3970 18 Nov 2011 09:00      1
                               3971 18 Nov 2011 10:00      1
                               3972 18 Nov 2011 11:00      1
                               3973 18 Nov 2011 12:00      1
                               3974 18 Nov 2011 13:00      1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3937
Begin Snapshot Id specified: 3937

Enter value for end_snap: 3974
End   Snapshot Id specified: 3974




Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 65j29t2qc8cqt  --- <<<<<<<<<<<<<<<<<<<< you have to put sql id here

Oracle Tablespace High water mark

Tablespace High water mark
Tablespace HWM



col tablespace_name format a15
col file_size format 99999
col file_name format a50
col hwm format 99999
col can_save format 99999

SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
ANUJTEST        /opt/app/oracle/oradata/orcl/anujtest.dbf                 10      8        2
EXAMPLE         /opt/app/oracle/oradata/orcl/example01.dbf               100     81       19
PERFSTAT        /opt/app/oracle/oradata/orcl/anuj_perfstat.dbf          1000    115      885
RMAN            /opt/app/oracle/oradata/orcl/rman.dbf                     50      7       43
SYSAUX          /opt/app/oracle/oradata/orcl/sysaux01.dbf                830    777       53
SYSTEM          /opt/app/oracle/oradata/orcl/system01.dbf                840    823       17
TSAPEXU         /opt/app/oracle/oradata/orcl/tsapexu01.dbf               110     97       13
UNDOTBR         /opt/app/oracle/oradata/orcl/undotbR.dbf                 500     46      454
USERS           /opt/app/oracle/oradata/orcl/users01.dbf                 609    576       33

9 rows selected.

Oracle object on System and sysaux tablespace more then 10Mb size

Oracle Table IDL_UB1$




col SEGMENT_NAME format a30
col owner format a20
select owner,segment_name,segment_type,bytes/(1024*1024) size_m,TABLESPACE_NAME from dba_segments
where tablespace_name = 'SYSTEM'
and bytes/(1024*1024) > 10 --- Mb
union all
select owner,segment_name,segment_type,bytes/(1024*1024) size_m,TABLESPACE_NAME from dba_segments

 set linesize 300

 col SEGMENT_NAME format a30

 col owner format a20

 select owner,segment_name,segment_type,bytes/(1024*1024) size_m,TABLESPACE_NAME from dba_segments

   where tablespace_name = 'SYSTEM'

   and bytes/(1024*1024) > 10 --- Mb

    union all

    select owner,segment_name,segment_type,bytes/(1024*1024) size_m,TABLESPACE_NAME from dba_segments

    where tablespace_name = 'SYSAUX'

   and bytes/(1024*1024) > 10 -- Mb

    order by size_m desc;

set lines 500 pages 10000 col TSname format a25 heading 'TSpace|Name|||' col TSstatus format a9 heading 'TSpace|Status|||' col TSSizeMb format 99999999 heading 'TSpace|Size|Mb||' col TSUsedMb format 99999999 heading 'TSpace|Used|Space|Mb|' col TSFreeMb format 99999999 heading 'TSpace|Free|Space|Mb|' col TSUsedPrct format 99999999 heading 'TSpace|Used|Space|%|' col TSFreePrct format 99999999 heading 'TSpace|Free|Space|%|' col TSSegUsedMb format 99999999 heading 'TSpace|Segmt|Space|Mb|' col TSExtUsedMb format 99999999 heading 'TSpace|Extent|Space|Mb|' col AutoExtFile format a6 heading 'Auto|Extend|File|?|' col TSMaxSizeMb format a6 heading 'TSpace|MaxSize|Mb||' col TSMaxUsedPrct format a6 heading 'TSpace|Maxed|Used|Space|%' col TSMaxFreePrct format a6 heading 'TSpace|Maxed|Free|Space|%' WITH ts_total_space AS (SELECT TableSpace_name, SUM(bytes) as bytes, SUM(blocks) as blocks, SUM(maxbytes) as maxbytes FROM dba_data_files GROUP BY TableSpace_name), ts_free_space AS (SELECT ddf.TableSpace_name, NVL(SUM(dfs.bytes),0) as bytes, NVL(SUM(dfs.blocks),0) as blocks FROM dba_data_files ddf, dba_free_space dfs WHERE ddf.file_id = dfs.file_id(+) GROUP BY ddf.TableSpace_name), ts_total_segments AS (SELECT TableSpace_name, SUM(bytes) as bytes, SUM(blocks) as blocks FROM dba_segments GROUP BY TableSpace_name), ts_total_extents AS (SELECT TableSpace_name, SUM(bytes) as bytes, SUM(blocks) as blocks FROM dba_extents GROUP BY TableSpace_name) SELECT dt.TableSpace_name as "TSname", dt.status as "TSstatus", ROUND(ttsp.bytes/1024/1024,0) as "TSSizeMb", ROUND((ttsp.bytes-tfs.bytes)/1024/1024,0) as "TSUsedMb", ROUND(tfs.bytes/1024/1024,0) as "TSFreeMb", ROUND((ttsp.bytes-tfs.bytes)/ttsp.bytes*100,0) as "TSUsedPrct", ROUND(tfs.bytes/ttsp.bytes*100,0) as "TSFreePrct", ROUND(ttse.bytes/1024/1024,0) as "TSSegUsedMb", ROUND(tte.bytes/1024/1024,0) as "TSExtUsedMb", CASE WHEN ttsp.maxbytes = '0' then 'No' ELSE 'Yes' END as "AutoExtFile", CASE WHEN ttsp.maxbytes = '0' then '-' ELSE TO_CHAR(ROUND(ttsp.maxbytes/1024/1024,0)) END as "TSMaxSizeMb", CASE WHEN ttsp.maxbytes = '0' then '-' ELSE TO_CHAR(ROUND((ttsp.bytes-tfs.bytes)/ttsp.maxbytes*100,0)) END as "TSMaxUsedPrct", CASE WHEN ttsp.maxbytes = '0' then '-' ELSE TO_CHAR(ROUND((ttsp.maxbytes-(ttsp.bytes-tfs.bytes))/ttsp.maxbytes*100,0)) END as "TSMaxFreePrct" FROM dba_TableSpaces dt, ts_total_space ttsp, ts_free_space tfs, ts_total_segments ttse, ts_total_extents tte WHERE dt.TableSpace_name = ttsp.TableSpace_name(+) AND dt.TableSpace_name = tfs.TableSpace_name(+) AND dt.TableSpace_name = ttse.TableSpace_name(+) AND dt.TableSpace_name = tte.TableSpace_name(+) AND dt.TableSpace_name = 'SYSAUX' ; TSpace TSpace TSpace TSpace TSpace TSpace TSpace TSpace TSpace Auto TSpace TSpace TSpace Name Status Size Used Free Used Free Segmt Extent Extend MaxSiz Maxed Maxed Mb Space Space Space Space Space Space File Mb Used Free Mb Mb % % Mb Mb ? Space Space % % ------------------------- --------- --------- --------- --------- --------- --------- --------- --------- ------ ------ ------ ------ SYSAUX ONLINE 149103 6724 142379 5 95 6718 6718 Yes 196607 3 97 SQL> SQL>

https://grepora.com/2016/02/08/purge-sysaux-tablespace/

How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes (Doc ID 1563921.1)

How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes (Doc ID 1563921.1)

Oracle Calculate Working Day

Working Day




create or replace FUNCTION workingday (v_begdate IN DATE, v_enddate IN DATE )
RETURN number IS

currdate date := v_begdate; -- next date

theDay varchar2(10); -- day of the week for currdate

businessday number := 0; -- count for business days

begin

-- start date must be earlier than end date


if v_enddate - v_begdate <= 0 then

return (0);

end if;

loop

-- end_date is reached

exit when currdate = v_enddate;

-- what day of the week is it??????

select to_char(currdate,'fmDay') into theDay from dual;

-- count it only if it is a weekday

if theDay <> 'Saturday' and theDay <> 'Sunday' then

businessday := businessday + 1;

end if;

currdate := to_date(currdate+1);

end loop;

return (businessday);

EXCEPTION

WHEN OTHERS THEN

raise_application_error(-20001,'There was an error in workingday....') ;

return 0;

END workingday ;
/


Function created.



SQL> select workingday ('24-oct-2011','14-nov-2011') "Workingday" from dual;

Workingday
----------
15





select workingday ('24-oct-2011','15-nov-2011') "Workingday" from dual;

Oracle Top 25 sql

Oracle  top 10 sqls'

Oracle top sqls'
Oracle top sql
Oracle sql full text
t25.sql


--- ===========================
prompt Top SQL by Disk Reads
col "SQL"  format a45
col Module  format a12
select sql_id,
substr(sql_text,1,100)                         "SQL",
(cpu_time/1000000)                             "CPU_Seconds",
disk_reads                                     "Disk_Reads",
buffer_gets                                    "Buffer_Gets",
executions                                     "Executions",
case when rows_processed = 0 then null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1))   "Buffer_gets/executions",
(elapsed_time/1000000)                         "Elapsed_Seconds",
module        "Module"
from gv$sql s
where PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
and rownum<25
and Module!='DBMS_SCHEDULER'
order by disk_reads desc nulls last;
-- ( 'SYS' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' ,'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , ' ANONYMOUS ' , 'XDB','ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )

prompt Top SQL by Buffer Gets
col "SQL"  format a45
col Module  format a12
select
inst_id,
sql_id,
substr(sql_text,1,100)                       "SQL",
(cpu_time/1000000)                           "CPU_Seconds",
disk_reads                                   "Disk_Reads",
buffer_gets                                  "Buffer_Gets",
executions                                   "Executions",
case when rows_processed = 0 then null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end                                          "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1)) "Buffer_gets/executions",
(elapsed_time/1000000)                       "Elapsed_Seconds",
module                                       "Module"
from gv$sql s
where PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
and rownum<25
and Module!='DBMS_SCHEDULER'
order by buffer_gets desc nulls last;

prompt Top SQL by CPU
col "SQL"  format a45
col Module  format a12
select
inst_id,
sql_id,
substr(sql_text,1,100)                      "SQL",
(cpu_time/1000000)                          "CPU_Seconds",
disk_reads                                  "Disk_Reads",
buffer_gets                                 "Buffer_Gets",
executions                                  "Executions",
case when rows_processed = 0 then
null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end       "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1)) "Buffer_gets/executions",
(elapsed_time/1000000)                       "Elapsed_Seconds",
module                                       "Module"
from gv$sql s
where PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
and rownum<25
and Module!='DBMS_SCHEDULER'
order by cpu_time desc nulls last;

prompt Top SQL by Executions
col "SQL" format a45
col Module format a12
select
inst_id,
sql_id,
substr(sql_text,1,100)                       "SQL",
(cpu_time/1000000)                           "CPU_Seconds",
disk_reads                                   "Disk_Reads",
buffer_gets                                  "Buffer_Gets",
executions                                   "Executions",
case when rows_processed = 0 then
null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end      "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1)) "Buffer_gets/executions",
(elapsed_time/1000000)                       "Elapsed_Seconds",
module                                       "Module"
from gv$sql s
where PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
and rownum<25
and Module!='DBMS_SCHEDULER'
order by executions desc nulls last;
col rows_processed  format a10
col sqltext   format a80
select rownum as rank, a.* from (select inst_id,sql_id,buffer_gets, lpad(rows_processed ||decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed",executions, loads,(decode(rows_processed,0,1,1)) * buffer_gets/ decode(rows_processed,0,1,rows_processed) avg_cost,
substr(sql_text,1,80) sqltext
from gv$sqlarea
where hash_value = hash_value
and PARSING_SCHEMA_NAME='&user'
order by 6 desc) a
where rownum <25
/

prompt sql full text
set long 5000
select inst_id,SQL_FULLTEXT from GV$SQLAREA where SQL_ID='&sql_id';
-- ==========================================================
Top explain plan
column load format a6 justify right
column executes  format 9999999
col load   format a22
break on load on executes skip 1
-- break on sql_id on executes skip 1
select
sql_id||'->'||substr(to_char(s.pct, '99.00'), 2) || '%' load,s.executions executes, p.sql_text
from ( select
address,
buffer_gets,
executions,
pct,
rank() over (order by buffer_gets desc) ranking
from ( select
 address,
 buffer_gets,
 executions,
 100 * ratio_to_report(buffer_gets) over () pct
 from sys.gv_$sql
where
-- command_type != 47 --- for plsql execution
PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
) where buffer_gets > 50 * executions ) s,
sys.gv_$sqltext p
where s.ranking <= 25
and p.address = s.address
order by 1, s.address, p.piece ;

Unix Command

Good Unix Commands

http://bhami.com/rosetta.html