SQL Sammlung

Anzeige des User Passwords (verschlüsselt)

Übersicht der TableSpaces einer Datenbank

Belegung der Tempsegmente pro Sessions

Flashback einer Datenbank zu einem festen Zeitpunkt

Anzeige der Fragmentation von Tabellen

Anzeige der gesetzen Oracle Hidden Parameter

 

Oracle User Password (Encryted)

select
'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password
from
   dba_users
where
username = 'SYSTEM';

 
 

Oracle TableSpaces

TABLESPACE_NAMEUSAGESIZEFREEUSEDFREE_PCTFREE_MAX_SIZEMAX_SIZEUSED_PCT_OF_MAXFILE_COUNTMEGS_USEDMEGS_ALLOC
DATAA0.97363 GB10,54 GB352,46 GB0.0337,54 GB390 GB0.913360918371712
TEMP090 GB90 GB3 MB190 GB90 GB00392160
IDXA0.9531 GB1,5 GB29,5 GB0.0530,5 GB60 GB0.4923020731744
DATAB0.9749 GB1,6 GB47,4 GB0.0312,6 GB60 GB0.7924853550176
SYSAUX0.741,17 GB315 MB885 MB0.2629,14 GB30 GB0.0318851200
PERFSTAT0.943,61 GB235 MB3,38 GB0.0626,62 GB30 GB0.11134653700
UNDOTBS10.2522,07 GB16,5 GB5,57 GB0.7524,43 GB30 GB0.191569922600
IDXB0.01100 MB99 MB1 MB0.9930 GB30 GB011100
USERS0.941,76 GB102 MB1,66 GB0.0628,34 GB30 GB0.06116981800
SYSTEM0.91500 MB47 MB453 MB0.0929,56 GB30 GB0.011453500

[spoiler title=’SQL – TableSpaces Übersicht’ style=’default’ collapse_link=’true’]

/* TableSpaces */
WITH vTS AS (
SELECT  A.TABLESPACE_NAME,
       ROUND(A.BYTES_ALLOC / 1024 / 1024) MEGS_ALLOC,
       ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024) MEGS_FREE,
       ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024) MEGS_USED,
       ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100) PCT_FREE,
       100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100) PCT_USED,
       ROUND(MAXBYTES/1048576) MAX
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,
       ROUND(SUM(H.BYTES_FREE + H.BYTES_USED) / 1048576) MEGS_ALLOC,
       ROUND(SUM((H.BYTES_FREE + H.BYTES_USED) - NVL(P.BYTES_USED, 0)) / 1048576) MEGS_FREE,
       ROUND(SUM(NVL(P.BYTES_USED, 0))/ 1048576) MEGS_USED,
       ROUND((SUM((H.BYTES_FREE + H.BYTES_USED) - NVL(P.BYTES_USED, 0)) / SUM(H.BYTES_USED + H.BYTES_FREE)) * 100) PCT_FREE,
       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(DECODE(F.AUTOEXTENSIBLE, 'YES', F.MAXBYTES, 'NO', F.BYTES) / 1048576)) MAX
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
)
SELECT
  TABLESPACE_NAME
, ROUND(PCT_USED, 2) || ' %' AS "USAGE"
,  CASE
    WHEN MEGS_ALLOC < 1024
      THEN ROUND(MEGS_ALLOC, 2) || ' MB'
    WHEN (MEGS_ALLOC / 1024) < 1024
      THEN ROUND(MEGS_ALLOC / 1024, 2) || ' GB'
   WHEN (MEGS_ALLOC / 1024) >= 1024
    THEN ROUND((MEGS_ALLOC / 1024)/1024, 2) || ' TB'
  END AS "SIZE"
,  CASE
    WHEN MEGS_FREE < 1024
      THEN ROUND(MEGS_FREE, 2) || ' MB'
    WHEN (MEGS_FREE / 1024) < 1024
      THEN ROUND(MEGS_FREE / 1024, 2) || ' GB'
   WHEN (MEGS_FREE / 1024) >= 1024
    THEN ROUND((MEGS_FREE / 1024)/1024, 2) || ' TB'
  END AS "FREE"
,  CASE
    WHEN MEGS_USED < 1024
      THEN ROUND(MEGS_USED, 2) || ' MB'
    WHEN (MEGS_USED / 1024) < 1024
      THEN ROUND(MEGS_USED / 1024, 2) || ' GB'
   WHEN (MEGS_USED / 1024) >= 1024
    THEN ROUND((MEGS_USED / 1024)/1024, 2) || ' TB'
  END AS "USED"
, ROUND(PCT_FREE, 2) || ' %' AS "FREE_PCT"
,  CASE
    WHEN (MAX-MEGS_USED) < 1024
      THEN ROUND((MAX-MEGS_USED), 2) || ' MB'
    WHEN ((MAX-MEGS_USED) / 1024) < 1024
      THEN ROUND((MAX-MEGS_USED) / 1024, 2) || ' GB'
   WHEN ((MAX-MEGS_USED) / 1024) >= 1024
    THEN ROUND(((MAX-MEGS_USED) / 1024)/1024, 2) || ' TB'
  END AS "FREE_MAX_SIZE"
,  CASE
    WHEN MAX < 1024
      THEN ROUND(MAX, 2) || ' MB'
    WHEN (MAX / 1024) < 1024
      THEN ROUND(MAX / 1024, 2) || ' GB'
   WHEN (MAX / 1024) >= 1024
    THEN ROUND((MAX / 1024)/1024, 2) || ' TB'
  END AS "MAX_SIZE"
, ROUND((NVL(MEGS_USED, 0) / MAX) * 100) || ' %' AS "USED_PCT_OF_MAX"
, (SELECT COUNT(FILE_NAME) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = vTS.TABLESPACE_NAME) AS FILE_COUNT
, ROUND(MEGS_USED ,2) AS MEGS_USED
, ROUND(MEGS_ALLOC ,2) AS MEGS_ALLOC
FROM
vTS
ORDER BY LENGTH(MAX) DESC, MAX DESC

[/spoiler]

 

Belegung der Tempsegmente pro Sessions

SELECT
     B.TABLESPACE
   , B.SEGFILE#
   , B.SEGBLK#
   , ROUND (  (  ( B.BLOCKS * P.VALUE ) / 1024 / 1024 ), 2 ) SIZE_MB
   , A.SID
   , A.SERIAL#
   , A.USERNAME
   , A.OSUSER
   , A.PROGRAM
   , B.SEGTYPE
   , A.STATUS
   , A.PROCESS
   , A.LOGON_TIME
   , A.PREV_EXEC_START
FROM V$SESSION A
   , V$SORT_USAGE B
   , V$PROCESS C
   , V$PARAMETER P
WHERE P.NAME = 'db_block_size'
 AND A.SADDR = B.SESSION_ADDR
 AND A.PADDR = C.ADDR
ORDER BY A.LOGON_TIME, B.TABLESPACE, B.SEGFILE#, B.SEGBLK#, B.BLOCKS

 

 

Flashback einer Datenbank zu einem festen Zeitpunkt

/* Flaschback - Restore Database */
-- Anzeige des möglichen Flashback Zeitraums
SELECT * FROM V$FLASHBACK_DATABASE_LOG  ;
-- SCN aus dem möglichen Flashback Zeitraums
SELECT TIMESTAMP_TO_SCN(TO_TIMESTAMP('15.07.2017 13:05:00', 'DD.MM.YYYY HH24:MI:SS')) AS MY_SCN FROM DUAL;

-- Datenbank stopen
SHUTDOWN IMMEDIATE ;

-- Datenbank Exclusive mounten um ein Flashback durch zu führen
STARTUP MOUNT EXCLUSIVE ;

-- Flashback durchführen
FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('23.08.2016 13:05:00', 'DD.MM.YYYY HH24:MI:SS');

-- Datenbank nach dem Flashback mit neuen Redologs öffnen
ALTER DATABASE OPEN RESETLOGS;
SELECT
  LO.SID
, TO_CHAR (LO.START_TIME, 'DD.MM.YYYY HH24:MI:SS') AS "BEGIN"
, ROUND( (LO.TOTALWORK*8/1024/1024) ,2 ) AS TOTAL_GB
, ROUND( (LO.SOFAR*8/1024/1024) ,2 ) AS SOFAR_GB
, ROUND((LO.SOFAR/LO.TOTALWORK) * 100) AS "Fertig %"
, TO_CHAR (SYSDATE + LO.TIME_REMAINING/3600/24, 'DD.MM.YYYY HH24:MI:SS') AS FERTIG
, TRIM (
            REPLACE (
               REPLACE (
                  TO_CHAR (
                     (TO_DATE (
                         TO_CHAR ( ((SYSDATE + LO.TIME_REMAINING/3600/24)), 'DD.MM.YYYY HH24:MI:SS'),
                         'DD.MM.YYYY HH24:MI:SS')
                      - TO_DATE (
                           TO_CHAR ( (LO.START_TIME), 'DD.MM.YYYY HH24:MI:SS'),
                           'DD.MM.YYYY HH24:MI:SS')) DAY TO SECOND),
                  '+00 '),
               '.000000')
  ) AS DAUER
, LO.OPNAME
, LO.TARGET
, ( SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID = SE.ROW_WAIT_OBJ# ) AS OBJECT_NAME
, SE.CLIENT_INFO
, LO.USERNAME
, LO.SQL_PLAN_OPTIONS
, LO.TARGET_DESC
, LO.MESSAGE
FROM
  V$SESSION_LONGOPS LO
, V$SESSION SE
WHERE
LO.SID = SE.SID
AND LO.TOTALWORK > LO.SOFAR
ORDER BY 6 DESC

 

Table Fragmentation

SELECT
  OWNER
, TABLE_NAME
, ROUND ( (BLOCKS * 8) / 1024 / 1024, 2) "FRAGMENTED SIZE GB"
, ROUND ( (NUM_ROWS * AVG_ROW_LEN / 1024) / 1024 / 1024, 2) "ACTUAL SIZE GB"
, ROUND ( (BLOCKS * 8) / 1024 / 1024, 2) - ROUND ( (NUM_ROWS * AVG_ROW_LEN / 1024) / 1024 / 1024, 2) AS "RECLAIMABLE SPACE GB"
, ROUND (((ROUND((BLOCKS*8),2) - ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2)) / ROUND((BLOCKS*8),2))*100 -10, 2 ) AS "RECLAIMABLE SPACE %"
FROM DBA_TABLES
WHERE
 OWNER LIKE '%DSA' AND
 ROUND ( (NUM_ROWS * AVG_ROW_LEN / 1024), 2) > 1024
ORDER BY 5 DESC

 

Anzeige der gesetzen Oracle Hidden Parameter

/* HIDDEN ORACLE PARAMETERS*/
SELECT
  A.KSPPINM  "PARAMETER",
  DECODE(P.ISSES_MODIFIABLE,'FALSE',NULL,NULL,NULL,B.KSPPSTVL) "SESSION",
  C.KSPPSTVL "INSTANCE",
  DECODE(P.ISSES_MODIFIABLE,'FALSE','F','TRUE','T') "S",
  DECODE(P.ISSYS_MODIFIABLE,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
  DECODE(P.ISDEFAULT,'FALSE','F','TRUE','T') "D",
  A.KSPPDESC "DESCRIPTION"
FROM X$KSPPI A, X$KSPPCV B, X$KSPPSV C, V$PARAMETER P
WHERE A.INDX = B.INDX AND A.INDX = C.INDX
  AND P.NAME(+) = A.KSPPINM
  AND UPPER(A.KSPPINM) LIKE UPPER('%&1%')
ORDER BY A.KSPPINM