Use asmcmd to see what's in the ASM database

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

See what SQL is executing in Oracle 'right now'.

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'

Useful Oracle scripts

space.sql Display tablespace size and current utilisation.

SQL> @space

										  %
								 %		     MaxPoss	Max
Tablespace Name 	   KBytes	  Used	       Free   Used	Largest       Kbytes   Used
------------------- ------------- ------------ ------------ ------ ------------ ------------ ------
 m SYSTEM		1,048,576      206,976	    841,600   19.7	841,488   0	 .0
*a SYSAUX		1,048,576      200,192	    848,384   19.1	848,384   0	 .0
*a TS_DATA	      503,316,480  384,733,696	118,582,784   76.4    8,257,536   0	 .0
*a TS_DEFAULT		2,097,152	   704	  2,096,448	.0    2,096,448   0	 .0
*m TS_TEMP	      265,420,800  265,420,800		  0  100.0	      0   0	 .0
*m TS_UNDO1		8,388,608      775,232	  7,613,376    9.2    7,482,368   0	 .0
*m TS_UNDO2		8,388,608	 1,344	  8,387,264	.0    8,256,256   0	 .0
		    ------------- ------------ ------------
sum		      789,708,800  651,338,944	138,369,856

7 rows selected.

table_sizes.sql Display #rows, row length #blocks used per schema

SQL> @table_sizes
Enter value for 1: tpch

TABLE_NAME		       TABLESPACE_NAME	      NUM_ROWS AVG_ROW_LEN     BLOCKS EMPTY_BLOCKS
------------------------------ -------------------- ---------- ----------- ---------- ------------
CUSTOMER					      45045500	       159     440887		 0
LINEITEM					    1799937800	       128    8800930		 0
NATION			       TS_DEFAULT		    25	       102	    4		 0
ORDERS						     449975200	       112    2797625		 0
PART						      60001700	       132     341698		 0
PARTSUPP					     240135800	       144    2484286		 0
PLAN_TABLE		       TS_DEFAULT		     0		 0	    0		 0
REGION			       TS_DEFAULT		     5		97	    4		 0
SUPPLIER					       3006700	       145	28679		 0

How to restart DB after deleting datafiles.

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.

Welcome to Hotel Retro.

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

It's cold.

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.