Mustafa Bektaş Tepe

SELECT c_list.CONSTRAINT_NAME AS NAME,
SUBSTR (c_src.COLUMN_NAME, 1, 20) AS SRC_COLUMN,
c_dest.TABLE_NAME AS DEST_TABLE,
SUBSTR (c_dest.COLUMN_NAME, 1, 20) AS DEST_COLUMN
FROM ALL_CONSTRAINTS c_list,
ALL_CONS_COLUMNS c_src,
ALL_CONS_COLUMNS c_dest
WHERE c_list.CONSTRAINT_NAME = c_src.CONSTRAINT_NAME
AND c_list.OWNER = c_src.OWNER
AND c_list.R_CONSTRAINT_NAME = c_dest.CONSTRAINT_NAME
AND c_list.OWNER = c_dest.OWNER
AND c_list.CONSTRAINT_TYPE = 'R'
AND c_src.OWNER = '<your-schema-here>'
AND c_src.TABLE_NAME = '<your-table-here>'
GROUP BY c_list.CONSTRAINT_NAME,
c_src.TABLE_NAME,
c_src.COLUMN_NAME,
c_dest.TABLE_NAME,
c_dest.COLUMN_NAME;

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe

808 total views, 4 views today

Merhaba Script ile ASM instance’ı kullanan uygulamaları listeleriz.

SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN disk_group_name        FORMAT a25           HEAD 'Disk Group Name'
COLUMN instance_name          FORMAT a20           HEAD 'Instance Name'
COLUMN db_name                FORMAT a9            HEAD 'Database Name'
COLUMN status                 FORMAT a12           HEAD 'Status'

SELECT
    a.name              disk_group_name
  , c.instance_name     instance_name
  , c.db_name           db_name
  , c.status            status
FROM
    v$asm_diskgroup a JOIN v$asm_client c USING (group_number)
ORDER BY
    a.name
/

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe

2,238 total views, no views today

ASM disk gruplarında ki disklerin listesi,disk alanlarının doluluk oranı vs. veren script.

SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN disk_group_name        FORMAT a25           HEAD 'Disk Group Name'
COLUMN disk_file_path         FORMAT a30           HEAD 'Path'
COLUMN disk_file_name         FORMAT a20           HEAD 'File Name'
COLUMN disk_file_fail_group   FORMAT a20           HEAD 'Fail Group'
COLUMN total_gb               FORMAT 999,999,999   HEAD 'File Size (GB)'
COLUMN used_gb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

BREAK ON report ON disk_group_name SKIP 1

COMPUTE sum LABEL ""              OF total_gb used_gb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF total_gb used_gb ON report

SELECT
    NVL(a.name, '[CANDIDATE]')                       disk_group_name
  , b.path                                           disk_file_path
  , b.name                                           disk_file_name
  , b.failgroup                                      disk_file_fail_group
  , b.total_mb/1024                                       total_gb
  , (b.total_mb - b.free_mb)/1024                         used_gb
  , ROUND((1- (b.free_mb / b.total_mb))*100, 2)      pct_used
FROM
    v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
    a.name
/

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe

1,556 total views, no views today

ASM disk gruplarının listesini,boyutunu,kullanımını,kullanım oranını vs. veren query aşagıdaki gibidir.

SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_gb               FORMAT 999,999,999   HEAD 'Total Size (GB)'
COLUMN used_gb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_gb used_gb on report

SELECT
    name                                     group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                     allocation_unit_size
  , state                                    state
  , type                                     type
  , total_mb/1024                                 total_gb
  , (total_mb - free_mb)/1024                     used_gb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
FROM
    v$asm_diskgroup
ORDER BY
    name
/

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe

1,833 total views, no views today

Bundan önce alınan rman yedeklerinin listesini veren yararlı bir script.

SET LINES 110
SET PAGES 20
COLUMN "Start Data" FOR a20
COLUMN "End Date" FOR a20
COLUMN "Time Minute" FORMAT a15
COLUMN status FORMAT a11
COLUMN input_bytes_display FORMAT a10
COLUMN output_bytes_display FORMAT a10

TITLE CENTER RMAN_Backup_List_REPORT SKIP 2

  SELECT TO_CHAR (start_time, 'YYYY.MM.DD HH24:MI:SS') "Start Data",
         TO_CHAR (end_time, 'YYYY.MM.DD HH24:MI:SS') "End Date",
         TO_CHAR (ROUND ( (end_time - start_time) * 24 * 60)) || ' dk'
            "Time Minute",
         status,
         input_type,
         input_bytes_display,
         output_bytes_display
    FROM V$RMAN_BACKUP_JOB_DETAILS
   WHERE (start_time BETWEEN (SYSDATE - 12) AND (SYSDATE))
ORDER BY start_time;

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe

1,512 total views, no views today

Merhabalar aşagıda ki sorgu veritabanımızda ki tablespaceler hakkında ayrıntılı bilgi veriyor.

Sorgu 1 :

SET PAGESIZE 100
SET LINESIZE 160
COLUMN "Tablespace Name" FORMAT a30
COLUMN "TS Used GB" FORMAT 9999
COLUMN "TS Alloc. GB" FORMAT 9999
COLUMN "TS Free GB" FORMAT 9999
COLUMN "Pct Used  %" FORMAT 9999
COLUMN "TS Max GB" FORMAT 9999
COLUMN "TS Max Free GB" FORMAT 9999
COLUMN "Pct Max. Used %" FORMAT 9999

SELECT a.tablespace_name "Tablespace Name",
       ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024 / 1024)
          "TS Used GB",
       ROUND (a.bytes_alloc / 1024 / 1024 / 1024) "TS Alloc. GB",
       ROUND (NVL (b.bytes_free, 0) / 1024 / 1024 / 1024) "TS Free GB",
       100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100)
          "Pct Used  %",
       ROUND (maxbytes / 1073741824) "TS Max GB",
       ROUND (
          (maxbytes - (a.bytes_alloc - NVL (b.bytes_free, 0))) / 1073741824)
          "TS Max Free GB",
       ROUND (
          ( (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0))))
           / ( (ROUND (maxbytes)) / 100)))
          "Pct Max. Used %"
  FROM (  SELECT f.tablespace_name,
                 SUM (f.bytes) bytes_alloc,
                 SUM (
                    DECODE (f.autoextensible,
                            'YES', f.maxbytes,
                            'NO', f.bytes))
                    maxbytes
            FROM dba_data_files f
        GROUP BY tablespace_name) a,
       (  SELECT f.tablespace_name, SUM (f.bytes) bytes_free
            FROM dba_free_space f
        GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+)
UNION ALL
  SELECT h.tablespace_name "Tablespace Name",
         ROUND (SUM (NVL (p.bytes_used, 0)) / 1073741824) "TS Used GB",
         ROUND (SUM (h.bytes_free + h.bytes_used) / 1073741824) "TS Alloc. GB",
         ROUND (
            SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0))
            / 1073741824)
            "TS Free GB",
         100
         - ROUND (
              (SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0))
               / SUM (h.bytes_used + h.bytes_free))
              * 100)
            "Pct Used  %",
         ROUND (SUM (f.maxbytes) / 1073741824) "TS Max GB",
         ROUND (
            (SUM (f.maxbytes) - (SUM (NVL (p.bytes_used, 0)))) / 1073741824)
            "TS Max Free GB",
         ROUND (
            ( (ROUND (SUM (NVL (p.bytes_used, 0))))
             / ( (ROUND (SUM (f.maxbytes))) / 100)))
            "Pct Max. Used %"
    FROM sys.v_$TEMP_SPACE_HEADER h,
         sys.v_$Temp_extent_pool p,
         dba_temp_files f
   WHERE     p.file_id(+) = h.file_id
         AND p.tablespace_name(+) = h.tablespace_name
         AND f.file_id = h.file_id
         AND f.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name
ORDER BY 8 DESC;

Sorgu 2 : 

SET LINES 132 PAGES 66 FEEDBACK OFF
COLUMN tablespace_name        FORMAT a15             HEADING 'Tablespace|(TBS)|Name'
COLUMN autoextensible         FORMAT a6              HEADING 'Can|Auto|Extend'
COLUMN files_in_tablespace    FORMAT 999             HEADING 'Files|In|TBS'
COLUMN total_tablespace_space FORMAT 99,999,999,999 HEADING 'Total|Current|TBS|Space'
COLUMN total_used_space       FORMAT 99,999,999,999 HEADING 'Total|Current|Used|Space'
COLUMN total_tablespace_free_space FORMAT 99,999,999,999 HEADING 'Total|Current|Free|Space'
COLUMN total_used_pct              FORMAT 999.99      HEADING 'Total|Current|Used|PCT'
COLUMN total_free_pct              FORMAT 999.99      HEADING 'Total|Current|Free|PCT'
COLUMN max_size_of_tablespace      FORMAT 99,999,999,999 HEADING 'TBS|Max|Size'
COLUMN total_auto_used_pct         FORMAT 999.99      HEADING 'Total|Max|Used|PCT'
COLUMN total_auto_free_pct         FORMAT 999.99      HEADING 'Total|Max|Free|PCT'

TTITLE LEFT _date CENTER Tablespace SPACE Utilization Status REPORT SKIP 2

WITH tbs_auto
     AS (SELECT DISTINCT tablespace_name, autoextensible
           FROM dba_data_files
          WHERE autoextensible = 'YES'),
     files
     AS (  SELECT tablespace_name,
                  COUNT (*) tbs_files,
                  SUM (BYTES) total_tbs_bytes
             FROM dba_data_files
         GROUP BY tablespace_name),
     fragments
     AS (  SELECT tablespace_name,
                  COUNT (*) tbs_fragments,
                  SUM (BYTES) total_tbs_free_bytes,
                  MAX (BYTES) max_free_chunk_bytes
             FROM dba_free_space
         GROUP BY tablespace_name),
     AUTOEXTEND
     AS (  SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
             FROM (  SELECT tablespace_name, SUM (maxbytes) size_to_grow
                       FROM dba_data_files
                      WHERE autoextensible = 'YES'
                   GROUP BY tablespace_name
                   UNION
                     SELECT tablespace_name, SUM (BYTES) size_to_grow
                       FROM dba_data_files
                      WHERE autoextensible = 'NO'
                   GROUP BY tablespace_name)
         GROUP BY tablespace_name)
SELECT a.tablespace_name,
       CASE tbs_auto.autoextensible WHEN 'YES' THEN 'YES' ELSE 'NO' END
          AS autoextensible,
       files.tbs_files files_in_tablespace,
       files.total_tbs_bytes / 1024 / 1024 total_tablespace_space,
       (files.total_tbs_bytes - fragments.total_tbs_free_bytes) / 1024 / 1024
          total_used_space,
       fragments.total_tbs_free_bytes / 1024 / 1024
          total_tablespace_free_space,
       ( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
          / files.total_tbs_bytes)
        * 100)
          total_used_pct,
       ( (fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100)
          total_free_pct,
       AUTOEXTEND.total_growth_tbs / 1024 / 1024 max_size_of_tablespace,
       ( ( (AUTOEXTEND.total_growth_tbs
            - (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes))
          / AUTOEXTEND.total_growth_tbs)
        * 100)
          total_auto_used_pct,
       ( ( (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes)
          / AUTOEXTEND.total_growth_tbs)
        * 100)
          total_auto_free_pct
  FROM dba_tablespaces a,
       files,
       fragments,
       AUTOEXTEND,
       tbs_auto
 WHERE     a.tablespace_name = files.tablespace_name
       AND a.tablespace_name = fragments.tablespace_name
       AND a.tablespace_name = AUTOEXTEND.tablespace_name
       AND a.tablespace_name = tbs_auto.tablespace_name(+);

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe

1,160 total views, no views today