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