DBA SCRIPTS

DB MONITORING




































DB MONITORING


































DATABASE INFO


































ASM



















SRVCTL COMMANDS












CRSCTL & RAC
















SQL PROF/BASELINE









PARTITIONING










STATISTICS















FLASHBACK TECH











RMAN SCRIPTS














USER MANAGEMENT













TABLESPACE & DATAFILE








MULTITENANT(CDB-PDB)











SCHEDULER & JOBS
















DATAGUARD MONITORING








OBJECT MANAGEMENT
















AUDITING & SECURITY















NETWORK MANAGEMENT










OEM/CLOUD CONTROL








EXPDP/IMPDP











    1. Please suggest new scripts that can be added to this script collections
    For any queries or suggestion ,Please post in forum.dbaclass.com.

Keep visiting us.

152 thoughts on “DBA SCRIPTS”

    1. Dear Pavan,

      There is some issue with the backend code, on which we are working. By Tomorow EOD, it will be fixed.

      Reply

  7. Really great help. Makes our regular works much easier and efficient.
    Thanks a lot and hope the alignment issue will be fixed soon.

    Regards
    Ranajit

    Reply

    1. Dear Karteek,

      Please post the question in our forum.(forum.dbaclass.com).

      Toad is the best utility for doing schema comparison. If you need, I can share the steps.

      Regards
      Rajkishore

      Reply

    1. You can use the below script.

      SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,
      t.NAME “Tablespace Name”, s.growth/(1024*1024) “Growth in MB”,
      (SELECT sum(bytes)/(1024*1024)
      FROM dba_segments
      WHERE segment_name=o.object_name) “Total Size(MB)”
      FROM DBA_OBJECTS o,
      ( SELECT TS#,OBJ#,
      SUM(SPACE_USED_DELTA) growth
      FROM DBA_HIST_SEG_STAT
      GROUP BY TS#,OBJ#
      HAVING SUM(SPACE_USED_DELTA) > 0
      ORDER BY 2 DESC ) s,
      v$tablespace t
      WHERE s.OBJ# = o.OBJECT_ID
      AND s.TS#=t.TS#
      AND rownum < 51 ORDER BY 6 DESC /

      Reply

      1. Thanks for the script but i have a query here. I don’t find anything related to 60 days in the provided script. If we want to query the result for 90 days how can we change the given script.

        Reply
  17. Pingback: Some useful Links. | iamoracledba

  20. Hi Admin – Could you provide query to find out the debug enabled at db resource level..i.e package …table…etc..

    Reply

    1. Hi Sai,

      Currently i dont have the zip format. But i am working a small interactive tool, Which will be a bundle of all these scripts.

      Regards
      Admin

      Reply

  25. Hi Team,

    I am Not able to view any of the scripts under DBA SCRIPTS. It was going as Blank page.

    I was using Google Chrome Browser.

    Please suggest, how can I view all these scripts.

    Thanks in Advance.

    Regards,
    Abhinay.

    Reply

    1. Hi, It seems java scripts/popup were blocked by your browser. These are working in my browser.
      Could you please check by removing adblocker if you have.

      Reply

  27. Hi Admin,

    Please send a script for Performance scripts or commands for concurrent request.

    Thanks,
    Pratyush

    Reply

  28. Hi Admin,

    Can you please help me and give us the consolidate command(.sh) or .sql file if I enter request id,it will fetch all the details.
    Request timings,
    Request history(sysdate-30),
    session details and its statistics,
    sql id,sql_text,
    progress details
    plan details(running with same or plan change).
    etc.

    Reply

      1. request id stands for if user submit concurrent request from Oracle EBS Applications front END and he will to share a request id to monitor the request.
        That is why we need a consolidate script.

        Thanks
        Pratyush

        Reply

  30. What is the input for DB Growth script per month :
    select to_char(creation_time, ‘MM-RRRR’) “Month”, sum(bytes)/1024/1024/1024 “Growth in GB
    from sys.v_$datafile
    where to_char(creation_time,’RRRR’)=’&YEAR_IN_YYYY_FORMAT’
    group by to_char(creation_time, ‘MM-RRRR’)
    order by to_char(creation_time, ‘MM-RRRR’);

    Reply

      1. i have give year as : 2018 getting error
        select to_char(creation_time, ‘MM-RRRR’) “Month”, sum(bytes)/1024/1024/1024 “Growth in GB
        from sys.v_$datafile
        2 3 where to_char(creation_time,’RRRR’)=’&YEAR_IN_YYYY_FORMAT’
        group by to_char(creation_time, ‘MM-RRRR’)
        order by to_char(creation_time, ‘MM-RRRR’); 4 5
        Enter value for year_in_yyyy_format: year,2018
        old 3: where to_char(creation_time,’RRRR’)=’&YEAR_IN_YYYY_FORMAT’
        new 3: where to_char(creation_time,’RRRR’)=’year,2018′
        ERROR:
        ORA-00972: identifier is too long

        Reply

  32. I am using 12c pluggable DB’s on Exadata with 44 cores (88 cpu’s). I can’t seem to find a convenient method for finding out how much CPU resources are being used using AWR statistics. AWR gives me the load stats as if all of the cores are available, but of course I am using the resource manager so those stats are not reflecting the resource manager settings for the CPU resource.

    Please suggest what I need to do.

    Thanks-
    Mark

    Reply

  33. My Application table owner granted DML privs to PUBLIC as Grantee, I want to rollback all DML pivs from PUBLIC and need to allocate it to newly created ROLE. Could you please provide me the script for this. Unable to get it from Google. Please help.

    I need to assing privs to ROLE same as Public. Public user having nearly 10k+privs.

    Reply

    1. Dear Gopal,

      Use similar below dynaimc script to generate required sql file.

      spool revoke_public.sql
      select ‘REVOKE ‘||PRIVILEGE||’ from ‘||OWNER||’.’||TABLE_NAME||’ from PUBLIC;’ from dba_tab_privs where grantee=’PUBLIC’ and owner=’APP’ and PRIVILEGE in (‘INSERT’,’UPDATE’,’DELETE’);
      spool off;

      For quick response , Please post in the Q&A Forum.

      Regards
      Admin

      Reply

    1. what is your problem in visiting the site and accessing the scripts. You don’t have internet in you pc or mobile. These days many telecom operators are providing internet service at very low cost. Go and buy them. And better access scripts from website only.

      Reply

  36. Hi Admin,

    Need your help to create blocking script and also kept in shell script which create a output in
    in html tabular format to get information of all waiter session at how much time with object_type,pid,sid,client information,module,action,program,last_call_et,status etc .

    Regards,
    Prasoon

    Reply

  37. Hi Admin,

    These blocking commands are not working showing no rows but blocking is there..

    Can you please send the command and also add detail how much time holding/blocking session is blocked waiter session.

    Thanks,
    Prasoon

    Reply

  38. set lines 120
    col sess format a15
    SELECT DECODE(request,0,’Holder: ‘,’ Waiter: ‘)||sid sess,id1,id2, lmode,inst_id, request, type
    FROM GV$LOCK WHERE (id1, id2, type)
    IN
    (SELECT id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1, request
    /

    Hi team,

    I have used this command to find out blocking session it is showing session is there but suggested commands is shoing no rows.
    Can you please send the command and also add detail how much time holding/blocking session is blocked waiter session?

    SESS ID1 ID2 LMODE INST_ID REQUEST TY
    ————— ———- ———- ———- ———- ———- —
    Holder: 2397 524319 548489 6 1 0 TX
    Waiter: 474 524319 548489 0 6 6 TX
    Holder: 2981 6815776 391294 6 1 0 TX
    Waiter: 542 6815776 391294 0 5 6 TX
    Holder: 1918 10223642 429877 6 2 0 TX
    Waiter: 1754 10223642 429877 0 2 6 TX
    Waiter: 2565 10223642 429877 0 1 6 TX
    Holder: 1924 11927575 573769 6 2 0 TX
    Waiter: 1918 11927575 573769 0 2 6 TX
    Holder: 2895 28180490 1078657 6 5 0 TX
    Waiter: 1905 28180490 1078657 0 2 6 TX

    SESS ID1 ID2 LMODE INST_ID REQUEST TY

    SQL> SELECT
    s.inst_id,
    s.blocking_session,
    s.sid,
    s.serial#,
    s.seconds_in_wait,
    s.event
    FROM
    gv$session s
    WHERE
    blocking_session IS NOT NULL and s.seconds_in_wait > 10; 2 3 4 5 6 7 8 9 10 11

    no rows selected

    SQL>

    Please clarify and help..

    Thanks,
    Prasoon

    Reply

    1. Hi Pablo,

      Thanks for suggestions, However we dont have expertise on exadata. We would be glad, if you can help us.

      Regards
      Admin

      Reply

  49. Great portal and collection Dear brother Good job …..exellent yar …very usful for all DBA

    Reply

  50. Please add some RDS related queries also, rdsadmin.packages*, which will be help of the peoples working on AWS cloud RDS oracle

    Reply

    1. Hi Suresh,

      We dont have expertise on amazon RDS. If you have set of scripts on RDS, share with us, we will happily publish the same.

      Regards
      Admin

      Reply

  55. Hi Admin, i was searching for the dba scripts and came across this website, this has all the scripts for day to day dba tasks, i really appreciate your effort to gather all the scripts at one place.

    if you dont mind, can you please email me all the scripts to pradeep13923dba@gmail.com

    Reply

    1. Dear,

      All the scripts are embedded in the website page. I don’t have it in one place with me.

      Admin

      Reply

  57. Awesome web congrats, I realized people requesting those script, like send to me to my email,exuse me …
    you already have those script in the website .

    Reply

  61. Hello,
    Add this script for tablespace utilization an it calculate the autoextendable size.

    set pages 50000 lines 32767
    col tablespace_name format a30
    col TABLESPACE_NAME heading “Tablespace|Name”
    col Allocated_size heading “Allocated|Size(GB)” form 99999999.99
    col Current_size heading “Current|Size(GB)” form 99999999.99
    col Used_size heading “Used|Size(GB)” form 99999999.99
    col Available_size heading “Available|Size(GB)” form 99999999.99
    col Pct_used heading “%Used (vs)|(Allocated)” form 99999999.99

    select a.tablespace_name
    ,a.alloc_size/1024/1024/1024 Allocated_size
    ,a.cur_size/1024/1024/1024 Current_Size
    ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
    ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
    ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
    from dba_tablespaces t
    ,(select t1.tablespace_name
    ,nvl(sum(s.bytes),0) used
    from dba_segments s
    ,dba_tablespaces t1
    where t1.tablespace_name=s.tablespace_name(+)
    group by t1.tablespace_name) u
    ,(select d.tablespace_name
    ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
    ,sum(d.bytes) cur_size
    ,count(*) file_count
    from dba_data_files d
    group by d.tablespace_name) a
    where t.tablespace_name=u.tablespace_name
    and t.tablespace_name=a.tablespace_name
    order by t.tablespace_name
    /

    Reply

  62. Hi Team,

    Can you please add scripts for Golden gate like(Start/stop, Monitor,Replicate, Monitor)

    Also please add scripts for PSU patch RAC database when GG is there and when DG is configured.

    Thanks

    Reply

  65. SELECT le.leseq “Current log sequence No”,
    100*cp.cpodr_bno/le.lesiz “Percent Full”,
    cp.cpodr_bno “Current Block No”,
    le.lesiz “Size of Log in Blocks”
    FROM x$kcccp cp, x$kccle le
    WHERE le.leseq =CP.cpodr_seq
    AND bitand(le.leflg,24) = 8
    /

    i have executed the above script. i’m unable to process the output process. Can you guide me.

    Reply

  66. Dear Admin,

    Thanks for great stuff, if time permits please add performance administration scripts like plan fix, etc.

    Many thanks

    Reply

  69. one particular tablespace in my database is growing rapidly and i want to know why is this happening. Is there a way i can check how much data has grown in last few months and the table which consumes more space belongs to which owner(user).

    Reply

  71. Hi Team,

    Can you please add scripts for Golden gate like(Start/stop, Monitor,Replicate, Monitor)

    Also please add scripts for PSU patch RAC database when GG is there and when DG is configured.

    Please add script for relinking CRS and Oracle binary .

    Thanks

    Reply

  72. Hello Blogger ,

    Issue in this script .

    select module,parsing_schema_name,inst_id,sql_id,plan_hash_value,child_number,sql_fulltext,
    to_char(last_active_time,’DD/MM/YY HH24:MI:SS’ ),sql_plan_baseline,executions,
    elapsed_time/executions/1000/1000,rows_processed from gv$sql
    where sql_id in (‘&sql_id’);

    SQL>
    SQL> select module,parsing_schema_name,inst_id,sql_id,plan_hash_value,child_number,sql_fulltext,
    to_char(last_active_time,’DD/MM/YY HH24:MI:SS’ ),sql_plan_baseline,executions,
    elapsed_time/executions/1000/1000,rows_processed from gv$sql
    where sql_id in (‘&sql_id’); 2 3 4
    Enter value for sql_id: 5573pf9s2vwmh
    old 4: where sql_id in (‘&sql_id’)
    new 4: where sql_id in (‘5573pf9s2vwmh’)
    ERROR:
    ORA-01476: divisor is equal to zero

    no rows selected

    Reply

    1. Try below one:

      select module,parsing_schema_name,inst_id,sql_id,plan_hash_value,child_number,sql_fulltext,
      to_char(last_active_time,’DD/MM/YY HH24:MI:SS’ ),sql_plan_baseline,executions,
      elapsed_time/executions/1000/1000,rows_processed from gv$sql
      where sql_id in (‘&sql_id’) and executions >0

      Reply

  74. Hi Admin,first of all i wanna say kudos for the great job put out here,I was
    wondering if you guys can come up with something this great in Golden gate.
    I have a personal issues,can i please get help with a monitoring script,precisely
    setting an alert on table increment…i received the below ticket and i have been working on it

    The issue is whenever there is a LOCK in INCREMENT table users will not be able to access the application and we will reach out to DBA team to get the information under which session the increment table got locked and once we receive the information we provide our confirmation to kill the session. Now we are looking to set up an proactive mechanism which will alert us before user reports the issue. Once the issue has been occurred we will expect the alert to provide us the session name and the server name details.
    Thanks in Advance Admin

    Reply

  77. excellent information…you made all our days easier with the scripts…….Thank you so much and much appreciated.

    Reply

  78. Hi Admin,

    Thank you very much for providing such great information in one place.
    Could you please let me know how can I download all these scripts to my desktop at once as we are not having permission to access the google at client place,is there any link like that if yes could you please send me @sp.pasha930@gmail.com.

    Reply

  79. Hi Admin,

    How to find what bind value with input parameter passing in SQL query from Oracle database backend. Can you provide any other option suggest me.

    ” Application team insert,update,delete or select from third party applications. I need to see what bind (:b1) variables with input parameter values from Oracle database.” How to enable trace particular SQL ID, session, sid in Database.

    Reply

  82. Thank you for your dedication in IT. I am new to database and this site has everything a newbies need.

    Reply

    1. This scripts are popup scripts and embeded in the html page. Please let me know for which scripts you are facing issue?

      Reply

  89. Hi Admin,

    Can you please share a script which we can use to check the ASM diskgroup growth
    having below columns.. grownth of 1-month, growth from last month and growth in last 6 months

    Reply

  90. Hi Admin,

    Is it possible to share a script which can be used for monitoring the ASM diskgroup growth rate?

    it would be good if it will have below columns for reference.

    1). 1-month growth
    2). Growth from last month.
    3). 6-months growth

    Reply

  93. Dear Admin,

    Would you please make these scripts downloadable as a .sql file.

    It will provide much more help for DBAs.

    Thank you.

    Reply

      1. Hi Admin,

        Can any one please share script (SQL Query ) for tablespace report for Oracle RDS(AWS) to run from OEM EM repository database.
        I’m trying to generate one OEM report for all our rds target dbs to list tablespace ,datafiles growth and %uasge , Free space .
        Please help on sql qeury . Thank you.

        Reply

  96. Hi Admin,

    Can any one please share script (SQL Query ) for tablespace report for Oracle RDS(AWS) to run from OEM EM repository database.
    I’m trying to generate one OEM report for all our rds target dbs to list tablespace ,datafiles growth and %uasge , Free space .
    Please someone help on oem repo sql qeury . Thank you.

    Reply

  100. Very handy , appreciate for sharing , can you plz add some scripts on the table fragmentation and reorg on them.
    Moving into same tablespace or different tablespace with online and advance compressions etc.

    Reply

  102. Dear Team,

    Is there a way we can monitor Tablespace usage and if the Tablespace growth is more than 20 or 30 GB then an alert is triggered

    Reply

  103. I am trying to create one new schema… I need to give grant insert to that new schema.. can u suggest on the same.
    Request is like create schema and grant insert privilege to it.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.