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.

The database will refuse to start if datafiles are removed from the os/storage without first removing them from within the DB. Dropping the table(s) stored on the datafiles won’t cut it.

1. connect / as sysdba
2. startup mount
3. alter databsae drop datafile ‘/filename’ offline drop;

The next part requires the DB to be open so…

4. alter database open;
5. drop tablespace including contents;

You can now recreate your datafiles, rebuild the tablespace and so on.

I’ve been thinking for a while that I should wean myself off of expensive hotels especially since I am now paying for them myself more often than not these days. So it was um, ‘handy’ that our lovely travel agents have me checked into what can only be described as hotel-retro. Boasting a pre bubble-era design aesthetic it is… the ‘anti-W’. Instead of psuedo-groovy deep house in the lobby we have at hotel-retro a pleasing blend of take-away food and industrial cleaner (Mmmm clean-but-greasy). In keeping with the 80′s chic The Quality-Inn Sunnyvale (Persian Dr) provides the pampered traveller the nostalgic feel of 56K modems via the “Worlds Slowest Broadband”.

How can you not love a country that has….

Snowflake shaped Ritz?

From super-user Bl…

Don’t ride your bike without gloves when there’s frost on the cars. It was so cold I could only stay out for a few minutes. By the time I got back to the car my hands could hardly open the car. It reminded me of cross country runs at school in the middle of winter.

Current weather : Clear Skys -1 C.