This is an old revision of the document!

SQL-Plus Administration

Anmelden am System

1.) Anmelden ab 10g

sqlplus / as sysdba -> Anmelden als SYSDBA
sqlplus hr/hr -> Anmelden als User hr mit PW hr
sqlplus nagios/nagadmin@ecadb.e-control.loc -> Anmelden an einen Server

2.) Anmelden unter Windows

Set ORACLE_SID=<sid>
sqlplus "sys/<pw>@<database> as sysdba"

3.) Anmelden unter Linux 9i

sqlplus "/ as sysdba"

Status Variablen der DB

1.) Welcher User bin ich

select user from dual;

2.) DB-Status Abfragen

select status from v$instance;

3.) Wieviele Sessions sind offen

set line 200;
select sid, serial# from v$session;
select SID,SERIAL#, USERNAME, OSUSER, PROGRAM, LOGON_TIME from v$session;

4.) Grund Infos über DB (Zeichensatz, Sprache usw...)

SELECT * FROM NLS_DATABASE_PARAMETERS

5.) Archiv Mode Aktiv?

select archiver from v$instance;

6.) Resourcen Limits von einer DB (processes, sessions, usw)

select * from v$resource_limit;

6.1.) Process Limit

select count(*) from v$process;

6.2.) Setzten vom Process Limit

alter system set processes=1000 scope=spfile;

7.) SID zu PID vergleich in SQLPLUS

select substr(a.spid,1,9) pid, substr(b.sid,1,5) sid, substr(b.serial#,1,5) ser#, substr(b.machine,1,6) box, substr(b.username,1,10) username, substr(b.osuser,1,8) os_user, substr(b.program,1,30) program from v$session b, v$process a where b.paddr = a.addr and type='USER' order by spid;

SGA Settings

1.) Anzeigen der SGA & Memory Target

SHOW parameter memory;
SHOW parameter sga;

2.) Setzten der Memory Target und SGA mit Target und Max-Wert

alter system set memory_max_target = 2G scope=spfile;
alter system set memory_target = 2G scope=spfile;
alter system set sga_max_size = 2G scope=spfile;
alter system set sga_target = 2G scope=spfile;

User Settings

1.) Ist ein User gelockt in der DB?

SELECT username, account_status from dba_users;

2.) User unlock

ALTER USER username ACCOUNT UNLOCK;

3.) User PW neu setzten

ALTER USER username identified by password;

Schema Settings

1.) Welche Schemas gibt es in der DB-Instanz

select distinct owner from dba_segments where owner in (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'));

2.) Hat ein Schema Objekte?

select * from dba_objects where OWNER = 'SCHEMA';

3.) Löschen eines Schemas mit allen Objekten

drop user <SCHEMA> cascade;

4.) Anlegen eines Schemas und DBA Rechte vergeben

CREATE USER <username> IDENTIFIED BY <passwort> DEFAULT TABLESPACE <tablespace> TEMPORARY TABLESPACE temp;
grant dba to <username>;

5.) Wann wurde ein Schema erstellt?

select created from dba_users where username = 'SCHEMANAME';

Archive-Modus

1.) Archive-Modus einschalten

DB muss im mount Status sein

shutdown immediate
startup mount
alter database archivelog;
alter database open;

Tabelspace

1.) Wie groß ist welcher Tablespace

set pages 200
set lines 200

select
	t.tablespace_name     tablespace_name  ,
	t.status              status           ,
	u.mbs_alloc           mbs_alloc        ,
	u.mbs_used            mbs_used         ,
	u.pct_used            pct_used         ,
	u.mbs_max             mbs_max          
from
(select  a.tablespace_name,
		round(a.bytes_alloc / 1024 / 1024, 0) mbs_alloc,
		-- round(nvl(b.bytes_free, 0) / 1024 / 1024, 0) mbs_free,
		round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 0) mbs_used,
		-- round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
		-- 100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_used,
		-- round((nvl(a.bytes_alloc, 0) / maxbytes) * 100,2) Pct_used,
		round(((a.bytes_alloc - nvl(b.bytes_free, 0)) / maxbytes) * 100,2) Pct_used,
		round(maxbytes/1048576,0) mbs_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, 0) mbs_alloc,
		-- round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576, 0) mbs_free,
		round(sum(nvl(p.bytes_used, 0))/ 1048576, 0) mbs_used,
		-- round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) Pct_Free,
		100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) pct_used,
		-- round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / maxbytes)) * 100,2) pct_used,
		round(f.maxbytes / 1048576, 0) mbs_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, f.maxbytes) u,
dba_tablespaces t
where t.tablespace_name=u.tablespace_name
order by 
-- pct_used
mbs_used 
desc
;

2.) Wieviele Datenfiles hat ein Tablespace

select 'alter database datafile '''||file_name||''' autoextend on next 1G maxsize 32767M;' from dba_data_files where tablespace_name='TABLESPACE_NAME';

3.) Welche Tablespaces sind autoexten

select tablespace_name, file_name, autoextensible from DBA_DATA_FILES;

4.) Tablespace erweitern

alter database tempfile '</oracle/SID/oradata/name01.dbf>' autoextend on next 1G maxsize 32767M;

5.) Datafile zu einem Tablespace hinzufügen

alter tablespace <tablespace> add datafile '</oracle/SID/oradata/name01.dbf>' size 1G autoextend on maxsize 32767M;

6.) Tablespace anlegen

create tablespace <NAME> datafile '<PATH>' size 500M autoextend on maxsize 1G;

7.) Autoextent aktivieren

alter database datafile '<PATH>' autoextend on maxsize <SIZE>;

8.) Open Cursors anzeigen (Welche Session hat wieviele offen)

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current' order by VALUE ASC;

Open Cursors Max und Current anzeigen

select 
   max(a.value) as hwm_open_cur, 
   p.value      as max_open_cur
from 
   v$sesstat a, 
   v$statname b, 
   v$parameter p
where 
   a.statistic# = b.statistic# 
and 
   b.name = 'opened cursors current'
and 
   p.name= 'open_cursors'
group by p.value;

9.) Open Cursors setzten

Zieht erst nach einem Reboot

ALTER SYSTEM SET OPEN_CURSORS=10000 SID='BULOG' scope=spfile;

Zieht sofort jedoch nicht persistent

ALTER SYSTEM SET OPEN_CURSORS=10000 SID='BULOG';

Zieht sofort und ist persistent

ALTER SYSTEM SET OPEN_CURSORS=10000 SID='BULOG' scope=both;

10.) Wie groß ist eine Tabelle

select table_name,round((blocks*8),2)||'kb' "size" from dba_tables where table_name = 'TABELLE';

Flash Recover Area

1.) Wo ist mein Speicher

set lines 200
col name for a40
col space_limit_mb for a20
col space_used_mb for a20
col space_available_mb for a20
col space_reclaimable_mb for a20

SELECT
NAME,
TO_CHAR(SPACE_LIMIT/1024/1024, '999,999,999,999') AS SPACE_LIMIT_MB,
TO_CHAR(SPACE_USED/1024/1024, '999,999,999,999') AS SPACE_USED_MB,
TO_CHAR((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE)/1024/1024,'999,999,999,999') AS SPACE_AVAILABLE_MB,
TO_CHAR(SPACE_RECLAIMABLE/1024/1024, '999,999,999,999') as SPACE_RECLAIMABLE_MB,
NUMBER_OF_FILES,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;

select * from V$FLASH_RECOVERY_AREA_USAGE;

2.) Vergrößern der FRA

alter system set db_recovery_file_dest_size=10G;

Job Queue

1.) Job Queue anhalten

alter system set job_queue_processes = 0

Aktivität in der DB

1.) Wer ist Angemeldet

set line 200;
column status format a10
set feedback off
set serveroutput on
select username, sid, serial#, process, status
from v$session
where username is not null;

2.) Wer ist seit wann angemeldet und welches SQL-Statement wird ausgeführt

column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
            ') ospid = ' ||  process ||
            ' program = ' || program username,
         to_char(LOGON_TIME,' Day HH24:MI') logon_time,
         to_char(sysdate,' Day HH24:MI') current_time,
         sql_address, LAST_CALL_ET
    from v$session
   where status = 'ACTIVE'
     and rawtohex(sql_address) <> '00'
     and username is not null order by last_call_et )
loop
    for y in ( select max(decode(piece,0,sql_text,null)) ||
                      max(decode(piece,1,sql_text,null)) ||
                      max(decode(piece,2,sql_text,null)) ||
                      max(decode(piece,3,sql_text,null))
                           sql_text
                 from v$sqltext_with_newlines
                where address = x.sql_address
                  and piece < 4)
    loop
        if ( y.sql_text not like '%listener.get_cmd%' and
             y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
        then
            dbms_output.put_line( '--------------------' );
            dbms_output.put_line( x.username );
            dbms_output.put_line( x.logon_time || ' ' ||
                                  x.current_time||
                                  ' last et = ' ||
                                  x.LAST_CALL_ET);
            dbms_output.put_line(
                      substr( y.sql_text, 1, 250 ) );
        end if;
    end loop;
end loop;
end;
/

Tuning

1.) Statistik einer Tabelle erneuern

analyze table <table> compute statistics;

2.) Staistik einer Tabelle wieder unlocken

exec dbms_stats.unlock_table_stats('<table#1>', '<table#2>');

Table of Contents