Trigger finden

 set line 132
 select owner,substr(object_name,0,30),object_type from dba_objects where object_type like '%TRI%';

Undo Tablespace

select file_name, tablespace_name form dba_data_files where TABLESPACE_NAME like '%UNDO%';

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2;

drop tablespace undotbs1;

CREATE UNDO TABLESPACE undotbs1 DATAFILE '/oracle/admin/$ORALCE_SID/oradata/undotbs1_01.dbf' SIZE 4096M AUTOEXTEND ON;
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs1;

CREATE UNDO TABLESPACE undotbs1 DATAFILE '/oracle/admin/$ORALCE_SID/oradata/undotbs1_01.dbf' SIZE 4096M AUTOEXTEND ON;

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs1;

Installierte Optionen

select  substr(NAME,0,30), VERSION, DETECTED_USAGES, to_char(FIRST_USAGE_DATE, 'YYYY-MM-DD HH24:MI:SS') "FIRST USAGE"
-- , to_char(LAST_USAGE_DATE, 'YYYY-MM-DD HH24:MI:SS') "LAST USAGE"
from dba_feature_usage_statistics
-- where DBID = $dbid
order by NAME, VERSION;

Segmente abfragen

select owner,substr(segment_Name,0,30), segment_type from dba_segments;

User abfragen

set line 132
select substr(username,0,20), substr(account_status,0,20)   from dba_users;

NLS_PARAMETER abfragen

set line 132
select * from nls_database_parameters;

JOBS

Job Liste anzeigen

set line 132
SELECT substr(job_name,0,20), job_class, operation, status FROM USER_SCHEDULER_JOB_LOG;


set line 132
SELECT substr(job_name,0,20), job_class, operation, status FROM USER_SCHEDULER_JOB_RUN_DETAILS;


SELECT substr(job_name,0,20),status, job_class   FROM USER_SCHEDULER_JOB_RUN_DETAILS;
SELECT substr(job_name,0,30),status, ERROR#  FROM USER_SCHEDULER_JOB_RUN_DETAILS;

SELECT to_char(log_date, 'DD-MON-YY HH24:MI:SS') TIMESTAMP, substr(job_name,0,30), substr(status,0,20), SUBSTR(additional_info, 1, 30) ADDITIONAL_INFO  FROM user_scheduler_job_run_details ORDER BY log_date;



SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;

SELECT log_id, to_char(log_date, 'DD-MON-YY HH24:MI:SS') timestamp,  substr(window_name,0,20), operation FROM DBA_SCHEDULER_WINDOW_LOG;

The DBA_SCHEDULER_WINDOWS_DETAILS view provides information about every window that was active and is now closed (completed). The following statement shows sample output from that view:

SELECT LOG_ID, substr(WINDOW_NAME,0,20), substr(ACTUAL_START_DATE,0,50), substr(ACTUAL_DURATION,0,50)  FROM DBA_SCHEDULER_WINDOW_DETAILS;

To see the contents of the window log, query the DBA_SCHEDULER_WINDOW_LOG view. The following statement shows sample output from this view:

SELECT log_id, to_char(log_date, 'DD-MON-YY HH24:MI:SS') timestamp,  substr(WINDOW_NAME,0,20), operation FROM DBA_SCHEDULER_WINDOW_LOG;

Account

Unlock

alter user <user> account unlock;

Password unlimited lifetime

select profile from dba_users where username='<user>';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Archivelog

Abschalten

SELECT LOG_MODE FROM SYS.V$DATABASE;
startup mount
alter database noarchivelog;
alter database open;

Aktivieren

startup mount
alter database archivelog;
alter database open;  

Redo log

Anzeigen

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

Gruppe löschen

ALTER DATABASE DROP LOGFILE GROUP #;

Gruppe erzeugen

ALTER DATABASE ADD LOGFILE GROUP xxx ('/pfad/redoxxx.log', '/oracle/dbs/log2c.rdo') SIZE 100M;
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2;

ALTER DATABASE ADD LOGFILE GROUP 4 ('/oracle/admin/$ORACLE_SID/oradata/redo04.log') size 200M;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/oracle/admin/$ORACLE_SID/oradata/redo05.log') size 200M;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/oracle/admin/$ORACLE_SID/oradata/redo06.log') size 200M;

Logfile switch

alter system switch logfile;

Temp Tablespace

select tablespace_name, CURRENT_USERS, TOTAL_EXTENTS,USED_EXTENTS from V$SORT_SEGMENT;

The DBA_TEMP_FREE_SPACE dictionary view contains information about space usage for each temporary tablespace. The information includes the space allocated and the free space. You can query this view for these statistics using the following command.

SELECT * from DBA_TEMP_FREE_SPACE;

You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view. The V$TEMPSEG_USAGE view identifies the current sort users in those segments.

set line 132
select substr(TABLESPACE_NAME,0,10) TS_NAME ,CURRENT_USERS,TOTAL_EXTENTS, 
-- USED_EXTENTS,FREE_EXTENTS,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from V$SORT_SEGMENT;
TABLESPACE_NAME                                    VARCHAR2(31)
SEGMENT_FILE                                       NUMBER
SEGMENT_BLOCK                                      NUMBER
EXTENT_SIZE                                        NUMBER
CURRENT_USERS                                      NUMBER
TOTAL_EXTENTS                                      NUMBER
TOTAL_BLOCKS                                       NUMBER
USED_EXTENTS                                       NUMBER
USED_BLOCKS                                        NUMBER
FREE_EXTENTS                                       NUMBER
FREE_BLOCKS                                        NUMBER
ADDED_EXTENTS                                      NUMBER
EXTENT_HITS                                        NUMBER
FREED_EXTENTS                                      NUMBER
FREE_REQUESTS                                      NUMBER
MAX_SIZE                                           NUMBER
MAX_BLOCKS                                         NUMBER
MAX_USED_SIZE                                      NUMBER
MAX_USED_BLOCKS                                    NUMBER
MAX_SORT_SIZE                                      NUMBER
MAX_SORT_BLOCKS                                    NUMBER
RELATIVE_FNO  


select * from V$TEMPSEG_USAGE;

Datendateien

Autoextendstatus

Anzeigen

set line 132
select substr(FILE_NAME,0,80),AUTOEXTENSIBLE from  dba_data_files;

Ändern

alter database datafile ' ' autoxetend on;

Für gegebenen Tablespace ermitteln

set line 132
select substr(file_name,0,60),tablespace_name from dba_data_files where TABLESPACE_NAME='<tsname>';

Memory

The dynamic performance view V$MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA. The view V$MEMORY_TARGET_ADVICE provides tuning advice for the MEMORY_TARGET initialization parameter.

select * from v$memory_target_advice order by memory_size;

vi/vim

Kommentar auf hellblau umstellen ~/.vimnrc hi Comment term=bold ctermfg=lightblue guifg=lightblue

LOBS

Move ordinary lob

alter table t move lob(<field>) store as ( tablespace <lobTablespace> )

move xmltype lob

alter table t move lob(<field>.XMLTYPE) store as ( tablespace <lobTablespace> )

Grösse und Verteilung herausfinden

spool size.txt
set pages 999
set lines 2500

column "owner" format a20
column "table" format a32
column "index" format a32
column "lob" format a32
column "db Blocks" format 9999999999
column "size" format 99999999999
column "Mb" format a4
column "tablespace" format a32

SELECT "OWNER"
  ,"TABLE"
  ,"INDEX"
  ,"DB Blocks"
  ,ROUND(DECODE(SIGN("Size"/1048576 -1 )
          , -1 , DECODE(SIGN("Size"/1024 -1)
                 , -1, "Size"
                 ,  "Size"/1024)
         , "Size"/1048576) ,2) "SIZE"
  ,DECODE(SIGN("Size"/1048576 -1 )
          , -1, DECODE(SIGN("Size"/1024 -1)
                ,-1 ,' Byte'
                , ' Kb')
          , ' Mb') " "
  ,"TABLESPACE"
 FROM (SELECT ds.owner "OWNER"
          ,di.table_name "TABLE"
          ,ds.segment_name "INDEX"
          ,SUM(ds.BYTES)   "Size"
          ,ds.blocks "DB Blocks"
          ,ds.tablespace_name "TABLESPACE"
     FROM DBA_SEGMENTS ds,dba_indexes di
    WHERE ds.segment_type='INDEX' 
      and ds.OWNER in (<Schmema>)
      and ds.segment_name=di.index_name
      and ds.owner=di.Owner
    GROUP BY ds.owner, di.table_name,ds.segment_name, ds.tablespace_name, ds.blocks
    ORDER BY ds.owner, di.table_name, ds.segment_name) 
;

SELECT 'Summe = '||
  sum (ROUND(DECODE(SIGN(sum(ds.BYTES)/1048576 -1 )
          , -1 , DECODE(SIGN(SUM(ds.BYTES)/1024 -1)
                 , -1, SUM(ds.BYTES)
                 ,  SUM(ds.BYTES)/1024)
         , SUM(ds.BYTES)/1048576) ,2))
  ||DECODE(SIGN(SUM(ds.BYTES)/1048576 -1 )
          , -1, DECODE(SIGN(SUM(BYTES)/1024 -1)
                ,-1 ,' Byte'
                , ' Kb')
          , ' Mb') " "
 FROM DBA_SEGMENTS ds,dba_indexes di WHERE ds.segment_type='INDEX' 
 and ds.OWNER in (<SCHEMA>) and ds.segment_name=di.index_name
 and ds.owner=di.Owner
 group by bytes
 ;


SELECT "OWNER"
  ,"TABLE"
  ,"DB Blocks"
  ,ROUND(DECODE(SIGN("Size"/1048576 -1 )
          , -1 , DECODE(SIGN("Size"/1024 -1)
                 , -1, "Size"
                 ,  "Size"/1024)
         , "Size"/1048576) ,2) "SIZE"
  ,DECODE(SIGN("Size"/1048576 -1 )
          , -1, DECODE(SIGN("Size"/1024 -1)
                ,-1 ,' Byte'
                , ' Kb')
          , ' Mb') " "
  ,"TABLESPACE"
  FROM (SELECT owner "OWNER"
          ,segment_name "TABLE"
          ,SUM(BYTES)   "Size"
          ,blocks "DB Blocks"
          ,tablespace_name "TABLESPACE"
     FROM DBA_SEGMENTS
    WHERE segment_type='TABLE' and OWNER in (<Schema>)
    GROUP BY owner, segment_name, tablespace_name, blocks
    ORDER BY owner, segment_name) 
;

SELECT 'Summe = '||
  sum (ROUND(DECODE(SIGN(sum(BYTES)/1048576 -1 )
          , -1 , DECODE(SIGN(SUM(BYTES)/1024 -1)
                 , -1, SUM(BYTES)
                 ,  SUM(BYTES)/1024)
         , SUM(BYTES)/1048576) ,2))
  ||DECODE(SIGN(SUM(BYTES)/1048576 -1 )
          , -1, DECODE(SIGN(SUM(BYTES)/1024 -1)
                ,-1 ,' Byte'
                , ' Kb')
          , ' Mb') " "
 FROM DBA_SEGMENTS
 WHERE segment_type='TABLE' 
  and OWNER in (<Schema>)
  group by bytes
 ;


SELECT "OWNER"
  ,"TABLE"
  ,"LOB"
  ,"DB Blocks"
  ,ROUND(DECODE(SIGN("Size"/1048576 -1 )
          , -1 , DECODE(SIGN("Size"/1024 -1)
                 , -1, "Size"
                 ,  "Size"/1024)
         , "Size"/1048576) ,2) "SIZE"
  ,DECODE(SIGN("Size"/1048576 -1 )
          , -1, DECODE(SIGN("Size"/1024 -1)
                ,-1 ,' Byte'
                , ' Kb')
          , ' Mb') " "
  ,"TABLESPACE"
  FROM (SELECT s.owner "OWNER"
          ,dl.table_name "TABLE"
          ,substr(dl.column_name,1,30) "LOB"
          ,SUM(s.BYTES)   "Size"
          ,s.blocks "DB Blocks"
          ,s.tablespace_name "TABLESPACE"
     FROM DBA_SEGMENTS s,dba_lobs dl
    WHERE s.segment_type='LOBSEGMENT' 
      and s.OWNER in (<Schema>) 
      and s.segment_name=dl.segment_name
    GROUP BY s.owner, dl.table_name,dl.column_name, s.tablespace_name, s.blocks
    ORDER BY s.owner, dl.table_name) 
 ;
 SELECT 'Summe = '||
  sum (ROUND(DECODE(SIGN(sum(s.bytes)/1048576 -1 )
          , -1 , DECODE(SIGN(SUM(s.bytes)/1024 -1)
                 , -1, sum(s.bytes)
                 ,  SUM(s.bytes)/1024)
         , sum(s.bytes)/1048576) ,2))
  ||DECODE(SIGN(SUM(s.bytes)/1048576 -1 )
          , -1, DECODE(SIGN(SUM(BYTES)/1024 -1)
                ,-1 ,' Byte'
                , ' Kb')
          , ' Mb') " "
  FROM DBA_SEGMENTS s,dba_lobs l
  WHERE s.segment_type='LOBSEGMENT' 
  and s.OWNER in (<Schema>) 
  and s.segment_name=l.segment_name
  group by bytes
  ;


  spool off;

Primary Key

Tabellen mit primary key

select owner,table_name, constraint_type from dba_constraints where constraint_type in ('P') and owner = 'owner':

Tabellen ohne primaray key

select owner,table_name from dba_tables where  owner = 'owner':
minus 
select owner,table_name  from dba_constraints where constraint_type in ('P') and owner = 'owner':

Oracle Charset

select * from v$nls_parameters where parameter='NLS_CHARACTERSET'; 
create or replace 
procedure updateSequenzen ( schemaName  in varchar )
is
counter             integer   :=0;
localNumber         integer   :=0;
remoteNumber        integer   :=0;
diff                integer   :=0;
sqlStringLocal      varchar(256);
sqlStringRemote     varchar(256);
updateLocalSequence varchar(256);
substrFound         integer  :=0;

begin

for rec in ( select last_number,sequence_name,sequence_owner  from dba_sequences where sequence_owner=upper(schemaN))
loop
 dbms_output.put_line(' ' );
end loop;
end;

Benutzer Rechte auslesen

sqlplus / as sysdba <<EOF
 set line 132
 column grantee format a10
 column owner   format a12
 column table_name  format a30
 column privilege  format a15
 select * from dba_sys_privs where grantee ='<user>';
 SELECT grantee,owner, table_name, privilege, grantable FROM dba_tab_privs WHERE grantee = '<user>';
 exit
 EOF

Datapump

Zufallskennwort erzeugen

PASSWD=`dd if=/dev/urandom bs=512 count=1 2>/dev/null | tr -dc "a-zA-Z0-9-_\.\+\$\?" | fold -w 15 | head -1` 
echo "alter user system identified by \"$PASSWD\";" | sqlplus -S "/ as sysdba"

Verzeichnis in der Datenbank definieren

sqlplus / as sysdba <<EOF
set echo on
create or replace directory DATAPUMPDIR as '$ORACLE_BASE/admin/$ORACLE_SID/dpdump';
exit
EOF

DUMP=${ORACLE_SID}_ohne_daten.dmp
LOG=${ORALCE_SID}_metadata_import.log

attach and datapump Import

Im Logfile des impdp (zB .log) die Angabe zu "Master table" heraussuchen:
Master table "<schema>"."SYS_IMPORT_TABLE_04" successfully loaded/unloaded ...
impdp <schema>  attach=SYS_IMPORT_TABLE_04 oder impdp system  attach=<schema>.SYS_IMPORT_TABLE_04

Import Ausführen

 impdp system/<password> DIRECTORY=DATAPUMPDIR   SCHEMAS=$SCHEMAS DUMPFILE=$DUMP logfile=$LOG 
TABLE_EXISTS_ACTION=REPLACE METRICS=YES PARFILE=$IMPORT_PARFILE

Datapump Daten ueber dblink holen

drop database link <dblink>;
create database link <dblink> connect to <user> identified by <password>using '<connectalias';
time impdp system/<psswd> directory=data_pump_dir logfile=dump.log network_link=<databaselink> schemas=<schema> parallel=12 flashback_time=sysdate

Sqlfile erzeugen

impdp system/<password> full=yes DIRECTORY=$DD DUMPFILE=$DUMP sqlfile=<datei>;

Top Sql

SET pages 3000 
SET LINES  120
SET trimspool ON
SET trimout   ON

COLUMN sql_text FORMAT a40 word_wrapped HEAD 'SQL|Text'
COLUMN cpu_time       HEAD 'CPU|Time'
COLUMN elapsed_time   HEAD 'Elapsed|Time'
COLUMN disk_reads     HEAD 'Disk|Reads'
COLUMN buffer_gets    HEAD 'Buffer|Gets'
COLUMN rows_processed HEAD 'Rows|Processed'


TTITLE 'SQL-Statements nach CPU-Nutzung'


SELECT * FROM 
   (SELECT sql_text, 
         cpu_time/1000000 cpu_time, 
         elapsed_time/1000000 elapsed_time, 
         disk_reads, 
         buffer_gets, 
         rows_processed 
  FROM   v$sqlarea 
  ORDER BY cpu_time DESC
) 
WHERE rownum <=25;

Object modification time

set line 132
spool objects_by_modification_time
COLUMN owner FORMAT a20
COLUMN object_name FORMAT a40
select owner, object_name, to_char(LAST_DDL_TIME,'yyyy-mm-dd:HH24:mm:ss') from dba_objects order by 3 desc;
spool off
exit

System Change Number

SCN=$(sqlplus -s -l / as sysdba <<EOF
 set echo off heading off feedback off
select dbms_flashback.get_system_change_number FROM dual;
EOF
SCN=`echo $SCN`
echo "Current SCN: $SCN"

Performance

Statspack Performance Report

connect perfstat/my_perfstat_password
SQL>  @?/rdbms/admin/spreport

V$IO_OUTLIER

Oracle Database maintains a V$IO_OUTLIER view that captures I/O operations that take a long time to complete. Long delays in I/O completions can cause serious performance degradation

Liste der Sessions mit zugehörigem OSPID

Damit kann ausgehend vom Betriebssystemprozess die Oracle Session gefunden werden

column username format a12
column osuser format a12
column process format a15
column program format a30
column state format a15
column status format a12
column spid format a12
column pid format a12
column osPid format a10



set line 256
select a.username,a.osuser,a.process,b.spid as osPid ,a.status,a.program,a.state,a.paddr  from v$session a , v$process b where a.paddr=b.addr ;

select a.username, a.spid as osPid, a.pid as oraclePId, a.program from v$process a , v$session b where a.addr=b.paddr ;

Lock wartezeiten

 Top 5 Timed Events                                                    Avg %Total
 ~~~~~~~~~~~~~~~~~~                                                   wait   Call
 Event                                            Waits    Time (s)   (ms)   Time
 ----------------------------------------- ------------ ----------- ------ ------
 enq: TX - row lock contention                      210     175,831 ######   98.6

A wait for the TX enqueue in mode 6 (P1 = 1415053318, P1RAW = 54580006) is the most common enqueue wait. (In Oracle Database 10g, the wait event name is enq: TX—row lock contention.) This indicates contention for row-level lock. This wait occurs when a transaction tries to update or delete rows that are currently locked by another transaction. This usually is an application issue. The waiting session will wait until the blocking session commits or rolls back its transaction. There is no other way to release the lock. (Killing the blocking session will cause its transaction to be rolled back.)

Session Trace

Anschalten

alter session set events '10046 trace name context forever, level 12';

Abschalten

ALTER SYSTEM SET EVENTS '10046 trace name context off';

db file scattered reads are multiblock IO's. db file sequential reads are single block IO's

scattered reads TYPICALLY result from full scans. sequential reads TYPICALLY result from single block IO (index reads)

many things can cause single block IO's (you do know by the way that your use of NOLOGGING in that query is "not useful", you could put the text HELLO_WORLD there and it would do the same thing! nologging is an attribute of a table, not a clause you use in an insert statement - append into a table that was marked as nonlogged could be "non-logged"

http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::%3AP11_QUESTION_ID:6265095774206

performace trace session

alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';

-- run the statement(s) to be traced --

select * from dual;
exit;

session finden

column line format a79
set heading off
select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s, v$process p
where p.addr = s.paddr and s.username <> ' ';

session trace activieren

connect / as sysdba
oradebug setospid <os-pid>
oradebug unlimit
oradebug event 10046 trace name context forever,level 12

you can either use sys.dbms_system to set sql_trace in your session from another session or for this user create a login database trigger that issues:

 execute immediate 'alter session set sql_trace=true';

Tracing über Login Trigger einschalten

CREATE OR REPLACE TRIGGER my_logon_trigger
after logon ON scott.SCHEMA
BEGIN
 EXECUTE IMMEDIATE ('alter session disable parallel query');
END;
/

CREATE OR REPLACE TRIGGER enable_10046_trace_trigger
after logon on database
--    10046 TRACELEVELS
--    0  - Turn off tracing.
--    1  - Basic SQL_TRACE.
--    4  - Level 1 plus Bind Variables.
--    8  - Level 1 plus wait events.
--    12 - Level 1 plus Bind Variables and Wait event information.
begin
if user ='JOHND' then
 execute immediate 'alter session set timed_statistics = true';
 execute immediate 'alter session set max_dump_file_size = unlimited';
 execute immediate 'alter session set tracefile_identifier = ''session_trace_energis''';
 execute immediate 'alter session set events ''10046 trace name context forever, level 4'' '; -- bind variables only
 end if;
end;

tracing abschalten

oradebug event 10046 trace name context off

finding hot block cache buffer chains

select P1 
 from  v$session_wait where event = 'cache buffer chains';

dbms_xplan

Global Trace

Einchalten

turn_global_trace_on.sh 
#!/bin/sh

sqlplus / as sysdba <<EOF
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12';
exit
EOF

Abschalten

turn_global_trace_off.sh
#!/bin/sh

  sqlplus / as sysdba <<EOF
  ALTER SYSTEM SET EVENTS '10046 trace name context off';
  exit  
  EOF

Trace Analyse

analyze_trace.sh
#!/bin/sh

liste=$(ls *trc)

for trace in $liste
do
    tkprof $trace ${trace}.report  SORT=\(PRSDSK, EXEDSK, FCHDSK\)
done

Locks finden

    locks.sql
select B.SID, C.USERNAME, C.OSUSER,
   DECODE(B.ID2, 0, A.OBJECT_NAME,
        'Trans-'||to_char(B.ID1)) OBJECT_NAME,
 B.TYPE,
   DECODE(B.LMODE,0,'--Waiting--',
                  1,'Null',
                  2,'Row Share',
                  3,'Row Excl',
               4,'Share',
                  5,'Sha Row Exc',
       6,'Exclusive',
                    'Other') "Lock Mode",
   DECODE(B.REQUEST,0,' ',
                  1,'Null',
                  2,'Row Share',
                  3,'Row Excl',
                  4,'Share',
                  5,'Sha Row Exc',
                  6,'Exclusive',
                 'Other') "Req Mode"
 from DBA_OBJECTS A, V$LOCK B, V$SESSION C
 where A.OBJECT_ID(+) = B.ID1 and B.SID = C.SID and C.USERNAME is not null order by B.SID, B.ID2;
 select username,
    osuser,
       v$lock.sid ,
    v$lock.type,
       trunc(id1/power(2,16)) rbs,
       bitand(id1,to_number('ffff','xxxx'))+0 slot,
       id2 seq,
       lmode,
       request
   from v$lock , v$session
   where v$lock.sid = v$session.sid;

Statistiken

To gather noworkload statistics, run DBMS_STATS.GATHER_SYSTEM_STATS() with no arguments. There is an overhead on the I/O system during the gathering process of noworkload statistics. The gathering process may take from a few seconds to several minutes, depending on I/O performance and database size.

Asynchrones IO

Global aktiviert ?

 COLUMN value  FORMAT a15
 COLUMN name   FORMAT a35
 COLUMN asynch FORMAT a10

 SELECT name,value
 FROM   v$parameter
 WHERE name IN ('timed_statistics'
               ,'filesystemio_options'
               ,'disk_asynch_io');

NAME                                VALUE
----------------------------------- ---------------
timed_statistics                    TRUE
filesystemio_options                SETALL
disk_asynch_io                      TRUE

Für Datendateien ?

SELECT f.name NAME, i.asynch_io  ASYNCH
 FROM  v$datafile f, v$iostat_file i
 WHERE f.file# = i.file_no AND i.filetype_name = 'Data File';

NAME                                ASYNCH
----------------------------------- ----------
D:\ORADATA\SNA1111\SYSTEM01.DBF     ASYNC_ON
D:\ORADATA\SNA1111\SYSAUX01.DBF     ASYNC_ON
D:\ORADATA\SNA1111\UNDOTBS01.DBF    ASYNC_ON
D:\ORADATA\SNA1111\USERS01.DBF      ASYNC_ON

Parsen/Bind Variablen

Oracle Video dass die Auswirungen von hard, soft parse und mehrfach verbindungen demonstriert

http://www.youtube.com/watch?v=1oddFEyUAjs

Statspack

Report erzeugen

 connect perfstat/<password>
 @?/rdbms/admin/spreport

Jobs

Job stoppen

dbms_scheduler.stop_job(job_name =>'<job_name>',force=>true);

Audit

Aktivieren

sqlplus / as sysdba  <<EOF
exec DBMS_FGA.ADD_POLICY(object_schema =>'<schema>',object_name=>'<tabelle>', policy_name =>'chk_<tabelle>',enable => TRUE, statement_types => 'INSERT, UPDATE,DELETE', audit_trail  =>  DBMS_FGA.XML);
/
show errors
exit  
EOF

Abschalten

sqlplus / as sysdba <<EOF
exec DBMS_FGA.DISABLE_POLICY(object_schema => '<schema>', object_name  => '<tabelle>', policy_name => 'chk_<table>');
exit
EOF

Init Cleanup

exec DBMS_AUDIT_MGMT.INIT_CLEANUP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,24);

Clean Manuell

Löscht alle Audit Einträge in Datenbank und Dateisystem

exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, use_last_arch_timestamp => FALSE);

Quelle

http://docs.oracle.com/cd/B28359_01/network.111/b28531/auditing.htm#DBSEG006

Recyle Bin

$ sqlplus / as sysdba

SQL> purge dba_recyclebin;

Import

  Master table <schema>."SYS_IMPORT_TABLE_04" successfully loaded/unloaded 
  impdp <schema>  attach=SYS_IMPORT_TABLE_04 oder impdp system  attach=<schema>.SYS_IMPORT_TABLE_04 
  Import> 

   Mit HELP werden die verfügbaren Kommandos angezeigt.

   Import> exit_client
   (Verlassen der interaktiven Sitzung, Job läuft weiter)

    Import> kill_job
    (Abbruch des Jobs)

Export

#!/bin/sh
#exec >/tmp/export.log
#exec 2>&1

DIR=/oracle/admin/$ORACLE_SID/dpdump
DD=DATAPUMPDIR

P=`dd if=/dev/urandom bs=512 count=1 2>/dev/null | tr -dc "a-zA-Z0-9-_\.\+\$\?" | fold -w 15 | head -1`
echo "alter user system identified by \"$P\";" | sqlplus -S "/ as sysdba"


sqlplus / as sysdba <<EOF
set echo on
create or replace directory $DD as '$DIR';
exit
EOF

DUMP=$ORACLE_SID_full.dmp
LOG=$ORACLE_SID_full.log

if [ -f $DIR/$DUMP ];then
 rm $DIR/$DUMP
fi

if [ -f $DIR/$LOG ];then
 rm $DIR/$LOG
fi

expdp system/"$P" full=yes DIRECTORY=$DD DUMPFILE=$DUMP logfile=$LOG

Listener

Debug

#ADR_BASE_LISTENER_umweltp=/tmp
LOGGING_LISTENER_umweltp=off
TRACE_LEVEL_LISTENER_umweltp=0

SCN

SCN=$(sqlplus -s -l / as sysdba <<EOF
 set echo off heading off feedback off
 select dbms_flashback.get_system_change_number FROM dual;
  EOF)
  SCN=`echo $SCN`
  echo "Current SCN: $SCN"

Prozessorgruppen

https://support.oracle.com/epmos/faces/SearchDocDisplay?adf.ctrl-state=tx3dv2xex_9&afrLoop=77321403510808

Oracle Wail events

https://docs.oracle.com/cd/B28359_01/server.111/b28320/waitevents003.htm#BGGIBDJI

Foreign keys finden, die nicht indiziert sind.

column columns         format a30 word_wrapped
column table_name      format a15 word_wrapped
column tablename       format a15 word_wrapped
column constraint_name format a15 word_wrapped
column owner           format a15 word_wrapped


select owner, table_name ,constraint_name,
   cname1 || nvl2(cname2,','||cname2,null) ||
   nvl2(cname3,','||cname3,null) ||
    nvl2(cname4,','||cname4,null) ||
    nvl2(cname5,','||cname5,null) ||
    nvl2(cname6,','||cname6,null) ||
    nvl2(cname7,','||cname7,null) ||
    nvl2(cname8,','||cname8,null)
    columns
    from
    (
    select  b.owner,
            b.table_name,
            b.constraint_name,
                    max(decode ( position,1,column_name,null)) cname1,
                    max(decode ( position,2,column_name,null)) cname2,
                    max(decode ( position,3,column_name,null)) cname3,
                    max(decode ( position,4,column_name,null)) cname4,
                    max(decode ( position,5,column_name,null)) cname5,
                    max(decode ( position,6,column_name,null)) cname6,
                    max(decode ( position,7,column_name,null)) cname7,
                    max(decode ( position,8,column_name,null)) cname8,
                    count(*) col_cnt
                    from
                        (
                            select  substr(table_name,1,30)         table_name,
                                    substr(constraint_name,1,30)    constraint_name,
                                    substr(column_name,1,30)        column_name,
                                    position
                                    from dba_cons_columns
                        ) a,
                        dba_constraints b
                        where a.constraint_name = b.constraint_name
                                and b.constraint_type = 'R'
                                group by b.owner,b.table_name,b.constraint_name
    )cons
where col_cnt > ALL (
        select count(*)
        from dba_ind_columns i
        where i.table_name = cons.table_name
        and i.column_name in (cname1,cname2,cname3,cname4,cname5,cname6,cname7,cname8)
    and i.column_position <=cons.col_cnt
        group by i.index_name
    )
/

Dba Constraints

Constraint Types:

C - Check constraint on a table

P - Primary key

U - Unique key

R - Referential integrity

V - With check option, on a view

O - With read only, on a view

H - Hash expression

F - Constraint that involves a REF column

S - Supplemental logging

column owner format a20
column table_name format a20
column constraint_name format a20
column column_name format a20

select a.owner,a.table_name,a.constraint_name,a.column_name
    from dba_cons_columns a,
         dba_constraints b
    where  a.constraint_name = b.constraint_name
          and b.constraint_type='R'
         order by a.owner,a.constraint_name;



TM contention -möglicherweise fehlende index auf foreign key contraint 

Db Links

### db links listen
 set line 512
 column "owner" format a15
 column "db_link" format a25
 column "username" format a25
 column "host" format a25

  select owner,db_link,host,username from dba_db_links;   

This wiki is powered by ikiwiki.