====== 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=
sqlplus "sys/@ 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  cascade;

---- ==== 4.) Anlegen eines Schemas und DBA Rechte vergeben ====


CREATE USER  IDENTIFIED BY  DEFAULT TABLESPACE  TEMPORARY TABLESPACE temp;
grant dba to ;

---- ==== 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 '' autoextend on next 1G maxsize 32767M;

---- ==== 5.) Datafile zu einem Tablespace hinzufügen ====


alter tablespace  add datafile '' size 1G autoextend on next 1G maxsize 32767M;

---- ==== 6.) Tablespace anlegen ====


create tablespace  datafile '' size 500M autoextend on maxsize 1G;

---- ==== 7.) Autoextent aktivieren ====


alter database datafile '' autoextend on maxsize ;

---- ==== 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  compute statistics;


----

2.) Staistik einer Tabelle wieder unlocken

exec dbms_stats.unlock_table_stats('', '');