#1
select 'alter system kill session '''||s.sid||', '||s.serial#||''';' as cmd
,to_char(s.logon_time, 'dd.mm.yyyy
hh24:mi:ss') as logon_time, s.osuser
,s.*, p.*
from v$session s, v$process p
where p.addr(+) = s.paddr
and p.background is null
order by s.USERNAME,status, s.osuser, s.logon_time
;
alter system kill session '152, 36573';
You want to
drop a schema, but someone is connected? Someone started a statement that slows
down your database?
Kill the
session! This is the most important command you need as a DBA.
#2
Extract DDL
With this command, you can get many commands.
But you need a tool to start ist, e.g. the SQL Navigator. Right-click on a
databse object and choose “Extract DDL”.
So you can get command to drop a user, to
create the user as it was before you dropped it, to create a table, a primary
key and everything else.
#3
sqlplus user/pwd@tnsalias
However, there are more comfortable ways to connect to the database (e.g. SQL Developer which is free and doesn't need an Oracle client).
On the server where the database is installed, you can start sqlplus also
when the listener is down, and when you have no Oracle client configuration, see my page about SQL+.
#4
declare
i_cnt number;
begin
select value into i_cnt from v$parameter where name = 'cpu_count';
dbms_output.put_line( 'cpu_count='||i_cnt );
UTL_RECOMP.recomp_parallel( i_cnt );
end;
/
You have made some imports and objects are invalid? Start
this command. It compiles all objects that are invalid. You only have to start
it one time. If you see invalid objects after this command, you can be shure
that they really are invalid.
#5
exec dbms_stats.gather_schema_stats(ownname=>'SCOTT', method_opt=>'FOR ALL COLUMNS
SIZE AUTO', cascade=>true);
Since 11g, statistics are generated every night
automatically. However, if you need to generate statistics, you can use this
command.
#6
select l.*,s.AUDSID, s.OSUSER, s.MACHINE, s.TERMINAL , s.BLOCKING_SESSION_STATUS, s.BLOCKING_INSTANCE, s.BLOCKING_SESSION , s.EVENT,sb.MACHINE, sb.TERMINAL , sb.BLOCKING_SESSION_STATUS, sb.BLOCKING_INSTANCE, sb.BLOCKING_SESSION
, sb.EVENT
, sql.SQL_TEXT, psql.SQL_TEXT prev_sql_text
from dba_dml_locks l, v$session s, v$session sb, v$sql sql, v$sql psql
where l.SESSION_ID=s.sid
and sb.SID(+)=s.BLOCKING_SESSION
and s.SQL_ID=sql.SQL_ID(+)
and s.PREV_SQL_ID=psql.SQL_ID(+);
You wait, nothing happens? Maybe, there is a
lock. Close everything. If that doesn’t help, try this command.
#7
select d.*
, round((d.bytes)/(1024*1024)) as used_mb
, round((d.maxbytes - d.bytes)/(1024*1024)) as free_mb
from dba_data_files d;
Use this command in order to see how much space
is left on your datafiles.
#8
alter database datafile '/u01/oradata/tcdppm/ts_tcdppm_datapool_d_02.dbf' resize 21000M;
Generated much data, deleted it and now your
datafiles are full of empty space? Not much space left on the disk? With this
command, you can shrink your datafiles.
#9
alter system set db_recovery_file_dest_size=20G;
If your Backup is not set up properly, archive logs may not be deleted automatically. After weeks or months, your database will stop working an you get:
ORA-19809: limit exceeded for recovery files
ORA-03113: end-of-file on communication channel
ORA-19809: limit exceeded for recovery files
On Windows, you may only get:
ORA-03113: end-of-file on communication channel
Start sql+, then:
shutdown abort
startup nomount
show parameter db_recovery_file_dest_size;
Now, increase this value:
alter system set db_recovery_file_dest_size=20G;
(If the disk is full, you have to delete archive logs, described in http://myoraclecollection.blogspot.de/p/string-in-number-feld-fur-die.html)
shutdown abort
startup nomount
show parameter db_recovery_file_dest_size;
Now, increase this value:
alter system set db_recovery_file_dest_size=20G;
(If the disk is full, you have to delete archive logs, described in http://myoraclecollection.blogspot.de/p/string-in-number-feld-fur-die.html)
After that, you will be able to start the database:
However, you still have to change your backup so that archive logs will be deleted automatically.
shutdown
startup
startup
However, you still have to change your backup so that archive logs will be deleted automatically.
#10
/*+ GATHER_PLAN_STATISTICS */
You’ve a SQL-Statement that is slow? Don’t waste your time looking at the execution plan. It doesn’t show the information you need. Use this hint in your SQL-Statement and you’ll get the information you need to make the statement fast. See http://myoraclecollection.blogspot.de/p/beschreibung-warum-ist-dieses-sql.html for more information.