ORA-00845: MEMORY_TARGET not supported on this system

Problem Description
While creating a startup database using dbca the database creation GUI gives error message in a pop up window,
ORA-00845: MEMORY_TARGET not supported on this system
from where you can ignore the error message.
The similar scenario also occur whenever you try to start your database then startup shows error message like below.

SQL> STARTUP
ORA-00845: MEMORY_TARGET not supported on this system

Cause of the Problem
•Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMMORY_MAX_TARGET.

•On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system.

•And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET.

•The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process.

•The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g.

•And MEMORY_MAX_TARGET parameter in 11g comes instead of SGA_MAX_TARGET parameter which was in 10g.

•The ORA-00845:can arises for the following two reasons on linux system.

1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET.
or,
2)If the shared memory is not mapped to /dev/shm directory.

Solution of the Problem
Make sure /dev/shm is properly mounted. You can see it by,
#df -h or #df -k command.
The output should be similar like

$ df -k
Filesystem            Size  Used Avail Use% Mounted on
...
shmfs                 1G    512M 512M  50% /dev/shm

We see here for /dev/shm we have assigned 1G memory. Now if you set MEMORY_TARGET more than 1G then above ORA-845 will arise. For example if you have MEMORY_TARGET or MEMORY_MAX_TARGET set to 12G then you can mount shared memory to 13g like below.
As a root user,
# mount -t tmpfs shmfs -o size=13g /dev/shm
In order to make the settings persistence so that it will affect after restarting machine add an entry in /etc/fstab similar to the following:

shmfs /dev/shm tmpfs size=13g 0

Adding a default value to a column on a table

In oracle there is two ways by which you can add a default value to a column. This means if you don't provide any value to the column that column will get automatically a default value. With ALTER TABLE ... ADD statement and ALTER TABLE ... MODIFY statement you can provide a default value to a column. First one is for adding a new column and then assigned it to a default value and second one is modify existing column to a default value. Below is the description along with examples.

1)ALTER TABLE ... ADD Statement:
With
ALTER TABLE table_name ADD column_name data_type DEFAULT default_value
you can add a column provided a default value to the column. If you add a default value in this way Oracle Database updates each row in the new column with the value you specify for DEFAULT. As it is an update operation so it, in turn, fires any AFTER UPDATE triggers defined on the table.

SQL> create table test_default (col1 number);
Table created.

SQL> insert into test_default values(1);
1 row created.

SQL> insert into test_default values(2);
1 row created.

SQL> select * from test_default;
COL1
----------
1
2

SQL> alter table test_default add col2 varchar2(10) default 'Dhaka';
Table altered.

SQL> select * from test_default;
COL1 COL2
---------- ----------
1 Dhaka
2 Dhaka
We see every column of col2 gets a default value which means every column is updated.

You can see the default value of a column from user_tab_columns view or from user_tab_cols or cols synonym.

SQL> select data_default,column_name from cols where table_name='TEST_DEFAULT';

DATA_DEFAULT COLUMN_NAME
---------------------------------------- ------------------------------
COL1
'Dhaka' COL2

Always remember once you assign a default value to a column you can never remove the default value of the column completely. You can assign the default value to NULL but still data dictionary will show it NULL. Below is the example.

The following statement has no effect in changing the default value and hence following statement is useless.

SQL> alter table test_default modify col2 varchar2(10);

Table altered.

In the data dictionary it will display the default value.
SQL> select data_default,column_name from cols where table_name='TEST_DEFAULT';

DATA_DEFAULT COLUMN_NAME
---------------------------------------- ------------------------------
COL1
'Dhaka' COL2

In order to assign default value to NULL issue,
SQL> alter table test_default modify col2 varchar2(10) default NULL;

Table altered.

Now data dictionary will display NULL in the data_default field.
SQL> select data_default,column_name from cols where table_name='TEST_DEFAULT';

DATA_DEFAULT COLUMN_NAME
---------------------------------------- ------------------------------
COL1
NULL COL2

In the column NULL value will be inserted if no value is given.
SQL> insert into test_default(col1) values(3);

1 row created.

SQL> select * from test_default;

COL1 COL2
---------- ----------
1 Dhaka
2 Dhaka
3
2)ALTER TABLE ... MODIFY statement:
With,
ALTER TABLE table_name MODIFY column_name DEFAULT default_value
you can modify a column value to the default one. But like ALTER TABLE ... ADD, MODIFY will not update the all the column values in the table. Subsequent insert will be assigned to default value if no value is provided.

SQL> alter table test_default modify col2 DEFAULT 'Jhenidah';

Table altered.

SQL> select * from test_default;

COL1 COL2
---------- ----------
1 Dhaka
2 Dhaka
3

SQL> insert into test_default(col1) values(4);

1 row created.

SQL> select * from test_default;

COL1 COL2
---------- ----------
1 Dhaka
2 Dhaka
3
4 Jhenidah
Related Documents
http://arjudba.blogspot.com/2008/09/how-to-disable-and-enable-all.html
http://arjudba.blogspot.com/2008/05/create-user-in-oracle.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
Create, Alter , Rename, Modify Table SQL
http://arjudba.blogspot.com/2008/06/drop-table-in-oracle.html

Resolve of DIM-00019: create service error

Problem Scenario
You have cold backup of oracle database directory structure along with oracle software. Now your Windows OS got corrupted. So you reinstall your windows operating system and you try to restore the database.

After installing software you set the ORACLE_SID and ORACLE_HOME environmental variable and whenever you create oracle service with oradim it fails with below message.
C:\>oradim -new -sid orcl -intpwd orcl -startmode manual -pfile 'F:\oracle10g\pr
oduct\10.2.0\db_1\database\initorcl.ora'
Instance created.
DIM-00019: create service error
O/S-Error: (OS 2) The system cannot find the file specified.

Solution of the Problem
On linux environment you don't need these things. But in windows environment you need to do a lot of things more than restoring files. Whenever a windows OS gets corrupted you loose the Oracle Universal Installer repository and the regedit entries along with oracle home structure. So to solve the problem it is better to install new oracle software in your windows machine without any database and then create oracle instance and service using oradim.