Get DDL for indexes

SET SERVEROUTPUT ON SIZE 1000000 

DECLARE
   V_DDL CLOB;
BEGIN
   DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false);
   DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);
   DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
   FOR I IN (SELECT OWNER,INDEX_NAME FROM DBA_INDEXES WHERE VISIBILITY='INVISIBLE') LOOP
     V_DDL := DBMS_METADATA.GET_DDL('INDEX', I.INDEX_NAME, I.OWNER);
     DBMS_OUTPUT.PUT_LINE(V_DDL);
   END LOOP;
END;
 

Invisible Unused Indexes

WITH
    x
    AS
        (SELECT do.owner,
                t.name table_name,
                io.name index_name,
                DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') monitoring,
                DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES') used,
                ou.start_monitoring,
                ou.end_monitoring
           FROM sys.obj$          io,
                sys.obj$          t,
                sys.ind$          i,
                sys.object_usage  ou,
                dba_objects       do
          WHERE i.obj# = ou.obj#
            AND io.obj# = ou.obj#
            AND t.obj# = i.bo#
            AND i.obj# = do.object_id)
SELECT 'alter index ' || x.owner || '.' || x.index_name || ' invisible;'
  FROM x, dba_ind_columns um                           
 WHERE x.used = 'NO'
   AND x.owner = um.index_owner
   AND x.owner = um.table_owner
   AND x.index_name = um.index_name
   AND x.table_name = um.table_name
   AND (um.index_owner, um.table_name, um.column_name) NOT IN
        (SELECT f2.owner, f2.table_name, f2.column_name
           FROM dba_cons_columns f2);

 

 

Document:
https://blog.zeddba.com/2018/07/17/index-monitoring-in-oracle-database/

 

پاک کردن یک یا چند ستون از جدول

اگر بخواهیم یک یا چند ستون را از یک جدول حذف کنیم، میتوانیم با دستور alter table drop column این کار را انجام دهیم که به صورت فیزیکالی جدول را حذف میکند ولی اگر سایز جدول زیاد باشد، این کار خیلی طول میکشد و ممکن است روی دیتابیس لود هم بگذارد.

یک راه حل دیگر این است که با دستور زیر ستون را از جدول حذف کنیم:

alter table R.TAB1 set unused column DATE1;

 

با این کار ستون به صورت logical از جدول حذف میشود و برای کاربرها قابل مشاهده نخواهد بود. بعدا اگر فرصت مناسبی پیش آمد، میتوان با دستور زیر ستون را به صورت فیزیکالی هم از جدول حذف کرد:

alter table R.TAB1 drop unused columns checkpoint 500;

 

برای جداول بزرگ برای اینکه حجم undo segmentها را کم کنیم، با استفاده از عبارت CHECKPOINT میتوانیم مشخص کنیم، بعد از این تعداد سطر، یک checkpoint انجام شود.

با استفاده از ویوی زیر میتوانیم به ازای هر جدول تعداد ستونهایی که unused شده اند را مشاهده کنیم:

select * from DBA_UNUSED_COL_TABS;

COUNT	TABLE_NAME	OWNER
2	TAB1	R
1	TAB_LOG	H

 

 

 

 

 

 

 

 

برای آزاد کردن فضا بعد از پاک کردن ستون میتوانیم از دستور زیر استفاده کنیم:

alter table R.TAB1 move online;

 

 

in SQL command '&'

برای حل مشکل '&' در کوئری های خود میتوانید به روشهای زیر عمل کنید:

Primary Query:

update b.otp a set a.token_type='s&online&offline' where a.user_id='7070';

 

In SQL*Plus:

SQL> set define off;

SQL> update b.otp a set a.token_type='s&online&offline' where a.user_id='7070';

 

In Toad:

update b.otp a set a.token_type='s&'||'online&'||'offline' where a.user_id='7070';

Number of Transactions (per second)

WITH
    hist_snaps
    AS
        (SELECT instance_number,
                snap_id,
                ROUND (begin_interval_time, 'MI') begin_snap_time,
                ROUND (end_interval_time, 'MI') end_snap_time,
                  (  begin_interval_time
                   + 0
                   - LAG (begin_interval_time + 0)
                         OVER (PARTITION BY dbid, instance_number
                               ORDER BY snap_id))
                * 86400                              diff_time
           FROM dba_hist_snapshot),
    hist_stats
    AS
        (SELECT dbid,
                instance_number,
                snap_id,
                stat_name,
                  VALUE
                - LAG (VALUE)
                      OVER (PARTITION BY dbid, instance_number, stat_name
                            ORDER BY snap_id)    delta_value
           FROM dba_hist_sysstat
          WHERE stat_name IN ('user commits', 'user rollbacks'))
  SELECT sn.begin_snap_time,
         sn.end_snap_time,
         sn.instance_number,
         ROUND (SUM (delta_value) / 3600)     "TRANSACTIONS/S"
    FROM hist_snaps sn, hist_stats st
   WHERE st.instance_number = sn.instance_number
     AND st.snap_id = sn.snap_id
     AND sn.diff_time IS NOT NULL
     AND sn.instance_number = 1
     AND TO_CHAR (sn.end_snap_time, 'yyyy-mm-dd') = TO_CHAR (sysdate-1, 'yyyy-mm-dd')
GROUP BY sn.begin_snap_time, sn.end_snap_time, sn.instance_number
ORDER BY 1;

Find and Delete Duplicate Records

SELECT session_user, db_unique_name, count(*)
  FROM p2.acl
 GROUP BY session_user, db_unique_name
HAVING count(*) > 1;

 

 

DELETE p2.acl
 WHERE db_unique_name = 'NIRVAN'
     AND rowid NOT IN (SELECT min (rowid)
                                    FROM p2.acl
                                  WHERE db_unique_name = 'NIRVAN'
                                  GROUP BY session_user, db_unique_name);

بدست آوردن تعداد Transactionها در ثانیه بین دو snapshot

در دیتابیس اوراکل، تعداد Transactionها در هر ثانیه را، هم میتوان از AWR Report بدست آورد، هم با استفاده از کوئری. 

 

AWR Report:

 

 

 

 

 

 

 

 

 

Query:

WITH
    hist_snaps
    AS
        (SELECT instance_number,
                snap_id,
                ROUND (begin_interval_time, 'MI') begin_snap_time,
                ROUND (end_interval_time, 'MI') end_snap_time,
                  (  begin_interval_time
                   + 0
                   - LAG (begin_interval_time + 0)
                         OVER (PARTITION BY dbid, instance_number
                               ORDER BY snap_id))
                * 86400                              diff_time
           FROM dba_hist_snapshot),
    hist_stats
    AS
        (SELECT dbid,
                instance_number,
                snap_id,
                stat_name,
                  VALUE
                - LAG (VALUE)
                      OVER (PARTITION BY dbid, instance_number, stat_name
                            ORDER BY snap_id)    delta_value
           FROM dba_hist_sysstat
          WHERE stat_name IN ('user commits', 'user rollbacks'))
  SELECT sn.begin_snap_time,
         sn.end_snap_time,
         sn.instance_number,
         ROUND (SUM (delta_value) / 3600)     "TRANSACTIONS/S"
    FROM hist_snaps sn, hist_stats st
   WHERE st.instance_number = sn.instance_number
     AND st.snap_id = sn.snap_id
     AND sn.diff_time IS NOT NULL
     AND sn.instance_number = 2
     AND TO_CHAR (sn.end_snap_time, 'yyyy-mm-dd') = to_char(sysdate, 'yyyy-mm-dd')
GROUP BY sn.begin_snap_time, sn.end_snap_time, sn.instance_number
ORDER BY 1;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Result Of above Query:

 

 

 

 

 

 

مشاهده مقدار bind_variable

SELECT *
  FROM gv$sql_bind_capture
WHERE inst_id = 1
    AND sql_id = '16h7r0vpcgvu2'
    AND child_address = '00000000ECCD5350';

 

SELECT *
  FROM DBA_HIST_SQLBIND
WHERE sql_id = '16h7r0vpcgvu2';

session_waits

کوئری زیر sessionهایی که در حال حاضر wait هستند را نمایش می دهد.

 

    SELECT s.inst_id,s.sid,s.serial#,s.username,s.status,s.osuser,s.program,s.machine,
                s.sql_id,s.sql_hash_value,s.sql_child_number,
                s.event,s.wait_class,s.p1,s.p2,s.p3,s.state,s.wait_time,s.seconds_in_wait
       FROM gv$session s
     WHERE s.inst_id = 1
         AND s.wait_class <> 'Idle'
         AND s.username NOT IN ('SYS','SYSTEM','DBSNMP','AUDSYS')
         AND s.state='WAITING'
ORDER BY seconds_in_wait DESC;

 

اگر میخواهید بدانید که P1,P2,P3 به ازای هر event چه مقداری را نمایش میدهند، کوئری زیر را اجرا کنید.

 SELECT name,parameter1,parameter2,parameter3
   FROM v$event_name
 WHERE name = 'gc current request';

 

PARAMETER3	PARAMETER2	PARAMETER1	NAME
id#	block#	file#	gc current request

 

 

 

 

پاک کردن یک سری از جداول از یک اسکیما

set serveroutput on; 
set verify off; 

declare

  sql_str varchar2(1000);
  cursor cur_syn is
    select owner, table_name
      from dba_tables
    where owner = 'MIG_ARES_3'
       and table_name not in ('T_INV_PRICING_ELEMENT','T_TMS_WEIGHING_STATION');

begin

  for rec_syn in cur_syn loop
    sql_str := 'DROP TABLE ' || rec_syn.owner || '.' || rec_syn.table_name ||
                   'CASCADE CONSTRAINTS';
    dbms_output.put_line(sql_str);
    execute immediate sql_str;
  end loop;

end;