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