Hows my tablespaces?

select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

Produces something like this:

TABLESPACE_NAME BYTES_USED BYTES_FREE LARGEST PERCENT_USED
—————————— ———- ———- ———- ————
SYSTEM 241172480 10289152 9371648 95.73
SYSAUX 157286400 11206656 7274496 92.88
USERS 220200960 22609920 17367040 89.73
MTD_DATA 104857600 25624576 19398656 75.56
UNDO 10485760 6750208 3145728 35.63
SQL>

from http://vsbabu.org/oracle/sect03.html

Dates in Microsoft Access

Every now & then I’m forced to deal with databases in Access, the worst case of this is when the database started life in something else, ‘cos that when Dates usually get really screwy.

I recently had one with dates stored as 3 seperate fileds, I know I should have transformed the data, but I didn’t, so I came up witha few queries that tried to munge the data into something usable, my first attempt was this:

PurchaseDate: LTrim(RTrim(day))+’/’+LTrim(RTrim(month))+’/’+LTrim(RTrim(year))

But then I found a better way:

PDate: DateFromDMY([day],[month],[year])

Where DateFromDMY is a VB function, something akin to:

Public Function DateFromDMY(a_Day As Integer, a_Month As Integer, a_Year As Integer) As Date

If (a_Day = 0) Then
a_Day = 1
End If

If (a_Month = 0) Then
a_Month = 1
End If

If (a_Month > 12) Then
a_Month = 12
End If

DateFromDMY = CDate(CStr(a_Day) + “/” + CStr(a_Month) + “/” + CStr(a_Year))

End Function

Now that’s a crude function, but you get the idea, you can fix up data easily.

Thank you Martin Green for his pointers here.

SQL*Plus

Here is a list of some of the most frequently used SQL*Plus commands:
• ACCEPT – Get input from the user
• DEFINE – Declare a variable (short: DEF)
• DESCRIBE – Lists the attributes of tables and other objects (short: DESC)
• EDIT – Places you in an editor so you can edit a SQL command (short: ED)
• EXIT or QUIT – Disconnect from the database and terminate SQL*Plus
• GET – Retrieves a SQL file and places it into the SQL buffer
• HOST – Issue an operating system command (short: !)
• LIST – Displays the last command executed/ command in the SQL buffer (short: L)
• PROMPT – Display a text string on the screen. Eg prompt Hello World!!!
• RUN – List and Run the command stored in the SQL buffer (short: /)
• SAVE – Saves command in the SQL buffer to a file. Eg “save x” will create a script file called x.sql
• SET – Modify the SQL*Plus environment eg. SET PAGESIZE 23
• SHOW – Show environment settings (short: SHO). Eg SHOW ALL, SHO PAGESIZE etc.
• SPOOL – Send output to a file. Eg “spool x” will save STDOUT to a file called x.lst
• START – Run a SQL script file (short: @)