Oracle: Sql script to check tablespace size

Every once in a while one of my test jobs fails, throwing this error that the table-space its created in has no further space to allocate for this table. And then comes the next sets of steps. Find out the other table space which is free. Here is a very basic SQL scripts which I use to list down all the table-spaces and their size in human readable format.

Update: Replacing with Github Gist

Oracle : Delete duplicate records from a table

This is a very simple script to perform a task which almost every other database developer faces in his day to day job. To finding and deleting duplicate records. I would try to answer it in 3 simpler steps

Step1: Lets say you want to find the duplicate records for a pair of attributes c1 and c2
Find all duplicate records:


SELECT c1, c2,count(1) FROM MyTable GROUP BY c1,c2

Step 2: Finding the row that you want to keep. Lets say  you want to keep only the maximum value of some indentifier column




SELECT c1, c2,max(id) FROM MyTable GROUP BY c1,c2



Step 3: keep that and delete others
Delete from mytable
where id not in (
SELECT c1, c2,max(id)


FROM MyTable
GROUP BY c1,c2


)