Skip to main content


Showing posts from January, 2010

'Linux-x86_64 Error: 28: No space left on device' While trying to start the database -- Error

SQL> startup mount pfile='/tmp/initdlfasp12.ora' ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device This as you can see is on Linux x86 with 64 bit processor. We got this error after we changed SGA on 10gR2 database. So was sure that this is something to do with the OS. Parameters to check for this are shmall. shmall is the total amount of shared memory, in pages, that the system can use at one time. $cat /proc/sys/kernel/shmmax 53687091200 $ getconf PAGE_SIZE 4096 As per Oracle SHMALL should be set to the total amount of physical RAM divided by page size. Our system has 64GB memory, so change kernel.shmall = 1024 * 1024 * 1024 * 64 / 4096 = 16777216 Once this value is calculated you can modify Linux system configuration file directly. $ su - root vi /etc/sysctl.conf file: kernel.shmall=16777216 and # sysctl -p Once this is done the database was started without any problem.

Indexes -- Basics

Indexes provide faster access to rows. Indexes can be created on any column. Usually search on a column is faster if an index exists on that column. Indexes are faster means to access database objects. By this defination it is clear that indexes are or should be used for performance enhancements. But this is not always true, using indexes will not always help in improving the performance. Indexes are always independent of the physical or logical data/objects or even the database, meaning creating and deleting(dropping) an index will harm the data or the functionality of your code. It only affects your performance. Though indexes are independent they require storage space. I always get queries from my customers that we have an index but my database doesnt use it. I have created an index but after creating it my queries are slow. SYNTAX: CREATE INDEX index_name ON table_name ( column_name ); EXAMPLE: CREATE INDEX anyname ON emp (sal); DROPPING INDEX: DROP INDEX


Need to know the OS stat on your database instance run, >select * from v$osstat; STAT_NAME VALUE OSSTAT_ID ---------------------------------------------------------------- ---------- ---------- NUM_CPUS 2 0 IDLE_TIME 29455741 1 BUSY_TIME 1188689 2 USER_TIME 281504 3 SYS_TIME 907185 4 AVG_IDLE_TIME 14725324 7 AVG_BUSY_TIME 9.2234E+18 8 AVG_USER_TIME 138792 9 AVG_SYS_TIME

Confusion FY and CY

These are very important abbreviations when it comes to handling projects, so make sure that there is no confusion. FY -- Financial Year or commonly called as Budget Year CY -- Calendar Year FY is country dependent and CY probably/definitely same for all the countries if they follow Gregorian Calendar. FY for US starts from Oct, For Australia it starts from July, For India it starts from April likewise its different for different countries. CY always starts from Jan.

Database Certification

Most common questions asked about database certification: 1. Whats the next patchset level 2. When is the next release 3. When will the next release for OS. etc Answer to all these questions lies in a metalink(now Oracle Support Portal) document 161818.1 Oracle Server (RDBMS) Releases Support Status Summary which is published external so anybody who has access to Oracle Support Portal can access this. This document talks about Current Patchset for each version of Oracle database and the next inline for release. This document also contains information about extended maintenance support(EMS). Document 161818.1 points to Document 742060.1 Release Schedule of Current Database Releases, which contains information about release schedule for current releases be it patchset or a version release.