17.07.2017
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
About Author
