Friday 14 December 2007

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'

Wednesday 12 December 2007

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.

Monday 3 December 2007

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

Saturday 1 December 2007

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.