Blog

What to do after ORA-01658: unable to create INITIAL extent for segment in tablespace string?

Lately I've been getting into some troubles with free space in one of Oracle's table. The reason is that I didn't use option AUTOEXTEND when I created the table. If you use AUTOEXTEND option when a table is created, Oracle will extend the table when needed, but sometimes when you need to have more control on space usage don't use AUTOEXTEND option. The only way to get rid of that error is:

1. Clean up table - remove unneeded data

2. Manualy extend database

    ALTER DATABASE DATAFILE 'full_path_to_file' RESIZE 500M;

SPFile, PFile - What is used by your running Oracle's instance?

From time to time I need to check what initialization parameters my Oracle's instances are using (init.ora or spfile). You changed your instance's parameters but after restart you can see the old ones? Have you had a similar problem? Have you got enough of it? The solution is simple.

Open sql console and enter sql command like below:
 
    sqlplus / as sysdba
    SELECT DECODE (value, NULL,'PFILE', 'SPFILE') "Init File Type" FROM v$parameter WHERE name = 'spfile';

Init F
------
PFILE
In my case init.ora (PFile) is used. You can do it also another way. I will show you second possible way to check that. Now I will ask for Oracle parameters called 'spfile' and 'pfile' (similar to previous example) In sqlplus console enter:
  SHOW PARAMETERS 'spfile';

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

The rule is simple - if 'spfile' parameter have NULL value it means that Oracle uses PFile (init.ora file) - in another case Oracle uses SPFile.

What to do after ORA-12162?

After installing Oracle XE on Debian I got into problems with connection to Oracle's instance. Every connection ended with error 'ORA-12162 TNS:net service name is incorrectly specified' Resolution is very simple - you only have to set correct environment variables:

    export ORACLE_SID=XE
    export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
 

and that's all After that you should also make this change permanent by writing those settings to shell initialization file. For Debian it will be /etc/profile (globally) or per user (.bashrc in user directory) I'm really missing /etc/env.d directory from Gentoo distro...