Oracle Cannot retain data

I have oracle database installed on my system

I access it through command line.

win+r > cmd > sqlplus

I had created a user

system

and

password system

I created a table

create table users ( username varchar(20));

Inserted few records.

insert into users (username ) values ('user1');
insert into users (username ) values ('user2');

then I exit

>exit

Problem is that when I try to login again.

table is still there. But the data which I had inserted is gone.

select * from users;

OUTPUT

no rows selected

Answer

SQL*Plus has option to auto-commit:

SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}

From documentation:

• ON commits pending changes to the database after Oracle Database executes each successful INSERT, UPDATE, or DELETE, or PL/SQL block.
• OFF suppresses automatic committing so that you must commit changes manually.
• SET AUTOCOMMIT does not alter the commit behavior when SQL*Plus exits. Any uncommitted data is committed by default.

To control the commit on EXIT, you need to set the EXITCOMMIT parameter in SQL*Plus:

SQL> show exitcommit
exitcommit ON
SQL> set exitcommit off

If it’s et to OFF, then you need to explicitly COMMIT after the insert statement which is a DML transaction. Create table is a DDL statement which does an implicit commit.

insert into users (username ) values ('user1');
insert into users (username ) values ('user2');
COMMIT; --> add this

I have created a user system

SYSTEM is an administrative user account which is automatically created when you install Oracle database. It is meant for administration tasks and not to store application data etc. Create a separate user for your application.