Saturday, 14 June 2014

The 10 most important Oracle commands

#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


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)

After that, you will be able to start the database:


shutdown
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.