Finding Oracle Tablespace Utilization (Free v/s Consumed space)

-------------------------------------------------------
Goto blog home
Visit my new blog dedicated to Internet of Things, Embedded Programming & Automation
-------------------------------------------------------

Last week I was working on my project when we discovered that space monitoring was not active in our application's database. During the process of addressing this problem, I figured out how we can easily query the database to get a report on various tablespaces and their free space. The query I used is provided below (the results are in MBs), the Oracle version I used was 10g.

SELECT
tablespace_name,
SUM(space)/1024/1024 "Size MB",
SUM(free)/1024/1024 "Free MB",
(1-(sum(free)/1024/1024)/(sum(space)/1024/1024))*100 "% Used"
FROM
(
SELECT
tablespace_name,
sum(bytes) space,
0 free
FROM dba_data_files
GROUP BY tablespace_name
UNION
SELECT
tablespace_name,
0 space,
sum(bytes) free
FROM dba_free_space
GROUP BY tablespace_name
)
WHERE TABLESPACE_NAME like UPPER('%')
GROUP BY tablespace_name
order by (1-(sum(free)/1024/1024)/(sum(space)/1024/1024))*100;

Auto Incrementing Primary Key in ORACLE

-------------------------------------------------------
Goto blog home
Visit my new blog dedicated to Internet of Things, Embedded Programming & Automation
-------------------------------------------------------
You will have to do the following in order to achieve an auto incrementing primary key in oracle. First, create a table with a primary key.

CREATE TABLE OwnerSchema.Users
(
UserId NUMBER NOT NULL,
UserName VARCHAR(30) NOT NULL
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

COMMIT;

ALTER TABLE OwnerSchema.Users ADD (
CONSTRAINT Users_PK
PRIMARY KEY
(UserId));

COMMIT;

Then create a sequence, this will be used to read the primary key values from.

CREATE SEQUENCE Users_Seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

COMMIT;

Finally, create a trigger to auto increment the primary key using the sequence.

CREATE TRIGGER OwnerSchema.Users_Trigger
BEFORE INSERT ON OwnerSchema.USERS FOR EACH ROW
BEGIN
SELECT Users_Seq.NEXTVAL INTO :NEW.UserId FROM Dual;
END;

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater - Resolved!

-------------------------------------------------------
Goto blog home
Visit my new blog dedicated to Internet of Things, Embedded Programming & Automation
-------------------------------------------------------
If this .NET exception happens without any reasonable cause, then it might be resolved by re-mapping permissions, at leasyt in my case it did. This error can happen if you have recently upgraded to a newer version of Oracle client (in my case it happenned after I upgraded to 9.2). To resolve this issue follow the following steps.

1. Browse to the Oracle root folder (my case, d:\oracle\Ora92)

2. In this folder's properties window, goto the Security tab and check whether 'Authenticated Users' is present. If not then add this user.

3. Now check the following properties for 'Authenticated Users'. If they are already checked then you must uncheck them first and then re-check them again - this is part of the trick.
-Read & Execute
-List Folder Contents
-Read

4. Next part of the trick is to ensure that in the Advanced permissions section, the 'Authenticated Users' have 'This folder, Sub folder and files' in the Apply To column. Be very careful with what you do in the advanced permissions window as flawed changes might have undesirable effects on rest of the folder hierarchy permissions.

That is all. A system restart was not needed in my case.