There are probably other/better ways to see what datafiles are being managed by ASM, but this is one way. The command asmcmd lives in the $ORACLE_HOME/bin irectory. In this example I have created an ASM area for my database files, control files etc. By cd’ing to the DATA directory I can see the files that are created in the group.

[oracle@beefy bin]$ asmcmd
ASMCMD> ls
DATA
ASMCMD> ls DATA
10I
control_001
control_002
data_1
data_10
data_11
data_12
data_13


select sesion.sid,       sesion.username,       optimizer_mode,       hash_value,       address,       cpu_time,       elapsed_time,       sql_text  from v$sqlarea sqlarea, v$session sesion where sesion.sql_hash_value = sqlarea.hash_value   and sesion.sql_address    = sqlarea.address   and sesion.username is not null

I have called this ‘what.sql’

space.sql Display tablespace size and current utilisation.

SQL> @/u/little/mycode/sql/dba/space

												  %
								 %		     MaxPoss	Max
Tablespace Name 	   KBytes	  Used	       Free   Used	Largest       Kbytes   Used
------------------- ------------- ------------ ------------ ------ ------------ ------------ ------
 m SYSTEM		1,048,576      206,688	    841,888   19.7	841,760 	   0	 .0
*a SYSAUX		1,048,576      135,360	    913,216   12.9	906,176 	   0	 .0
*a TS_DATA	       36,065,280   32,574,848	  3,490,432   90.3    2,234,240 	   0	 .0
*a TS_DEFAULT		2,097,152	   704	  2,096,448	.0    2,096,448 	   0	 .0
*m TS_TEMP	       39,813,120   39,813,120		  0  100.0	      0 	   0	 .0
*m TS_UNDO1		8,388,608      396,352	  7,992,256    4.7    7,861,248 	   0	 .0
*m TS_UNDO2		8,388,608	 1,344	  8,387,264	.0    8,256,256 	   0	 .0
		    ------------- ------------ ------------
sum		       96,849,920   73,128,416	 23,721,504

7 rows selected.

Set the ‘EDITOR’ variable for the user ‘oracle’. Doing so will allow you to edit your sqlplus commands using a reasonable editor. After executing a piece of SQL in sqlplus, just type ‘edit’ or ‘ed’ and you’ll be transported to your editor. In vi just type :q to get back to the sqlplus prompt.

Create an oracle environment file in your home directory and have Oracle execute it every time you start sqlplus. Oracle will execute a command file called login.sql if it finds one in the path defined by $SQLPATH. For DBA activity you can format commonly used columns to display in a more user friendly way e.g.

set linesize 200col file_name for a40col tablespace_name for a20

I store this file in $HOME/env/oracle and put this directory in $SQLPATH

Other things in the login.sql

• set timing on Automatically print the duration of a sqlplus command.

You can store your DBA scripts in the $SQLPATH too.

Supplementry packages

You’ll want to add in these packages to allow you to monitor the database

• statspack – sample counters in the database then report on them. Very useful for performance analysis
• dbms_space

Statspack

Install the statspack tools

SQL>  @?/rdbms/admin/spcreate

Ensure timed statistics is set to true

SQL> show parameter timed_statistics

SQL> exec statspack.snap

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.57SQL> exec statspack.snap

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.20SQL> @$ORACLE_HOME/rdbms/admin/spreport

If you ever need to get rid of statspack (sometimes useful if the instll crashed part way through) just run the script

SQL>  @?/rdbms/admin/spdrop