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_NAME	USAGE	SIZE	FREE	USED	FREE_PCT	FREE_MAX_SIZE	MAX_SIZE	USED_PCT_OF_MAX	FILE_COUNT	MEGS_USED	MEGS_ALLOC
DATAA	0.97	363 GB	10,54 GB	352,46 GB	0.03	37,54 GB	390 GB	0.9	13	360918	371712
TEMP	0	90 GB	90 GB	3 MB	1	90 GB	90 GB	0	0	3	92160
IDXA	0.95	31 GB	1,5 GB	29,5 GB	0.05	30,5 GB	60 GB	0.49	2	30207	31744
DATAB	0.97	49 GB	1,6 GB	47,4 GB	0.03	12,6 GB	60 GB	0.79	2	48535	50176
SYSAUX	0.74	1,17 GB	315 MB	885 MB	0.26	29,14 GB	30 GB	0.03	1	885	1200
PERFSTAT	0.94	3,61 GB	235 MB	3,38 GB	0.06	26,62 GB	30 GB	0.11	1	3465	3700
UNDOTBS1	0.25	22,07 GB	16,5 GB	5,57 GB	0.75	24,43 GB	30 GB	0.19	1	5699	22600
IDXB	0.01	100 MB	99 MB	1 MB	0.99	30 GB	30 GB	0	1	1	100
USERS	0.94	1,76 GB	102 MB	1,66 GB	0.06	28,34 GB	30 GB	0.06	1	1698	1800
SYSTEM	0.91	500 MB	47 MB	453 MB	0.09	29,56 GB	30 GB	0.01	1	453	500

[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