LinuxQuestions.org

- Ubuntu (https://www.linuxquestions.org/questions/ubuntu-63/)

- - sql*plus (https://www.linuxquestions.org/questions/ubuntu-63/sql%2Aplus-595192/)

Hi all,

I have recently installed oracle database 10g express edition on
ubuntu dapper. I am running sql*plus via

Applications ->Oracle database 10g express edition -> Run sql command line

now my question is how to run sql*plus in virtual console window??
or which is the executable file for sql*plus ???

As I understand the question, you want to run sqlplus from the command line.

So long as your environment variables are set up correctly, all you should need is to type sqlplus.

However, if you want to run this from another machine, that machine would need to have the Oracle client components installed on it. Additionally you would need to have tnsnames.ora file configured with the information about the database instance you want to connect to.

Environment variables to consider are:
ORACLE_HOME (location Oracle has been installed to)
ORACLE_SID (database that you want to connect to)
NLS_DATE_FORMAT (Format Oracle will display dates in)

Ensure that the PATH variable contains $ORACLE_HOME/bin

Quote:

Originally Posted by Disillusionist (Post 2939734)

how to change the path variable???.
which file i have to edit??
(is it in /etc/rc2.d ???)

if i remember correctly i think in windows we have to edit autoexec.bat file and add the the line path=%path%;ORACLE_HOME/bin
(correct me if i am wrong)

When you installed Oracle 10g Express Edition the install will have created a user called oracle

Initial setup:
1. Change / set the password for that account: sudo passwd oracle

2. Log in as the oracle account: su - oracle

3. vi .profile:

Code:

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

export ORACLE_SID=XE

export PATH=$ORACLE_HOME/bin:$PATH

Then when you login as oracle (su - oracle) your environment will be set

Not sure what level of experience you have with Oracle so forgive me if I am explaining things that you already know.

To gain admin access the the XE database (created by default with Express Edition):

Code:

sqlplus '/ as sysdba'

you can then modify the sys password (example to set the sys account to "newpassword"):

Code:

alter user sys identified by newpassword;

Thanks a lot. your information was quite useful

Quote:

Originally Posted by sulekha (Post 2943002)

Thanks a lot. your information was quite useful

what about stopping oracle database from command line???

Code:

su - oracle

sqlplus '/ as sysdba'

shutdown

There are variations on the shutdown command
shutdown
shutdown immediate
shutdown abort

"shutdown" waits for any active processes to finish before stopping the database.

"shutdown immediate" asks active processes to finish and then stops the database once they have completed.

"shutdown abort" forces the database to stop and kills any active processes

The first two options are much cleaner as active processes are closed cleanly.

The last option is occasionally necessary, when a process is not stopping.

Oracle is pretty good at cleaning up any mess left from a shutdown abort, but it's not good practice to just go straight to shutdown abort.

Hope this helps:)

need a simple SQL script run on Oracle

Once under Oracle this script resulted in empty tables

but not under mysql or MS SQL Server 2005

client for mysql is 100000 times better than sql*plus
I once threatened to rewrite sql*plus
_____________________________

alter table pets_like_boys
drop constraint pets_like;

alter table girls_like_pets
drop constraint girls_like;

alter table boys_like_girls
drop constraint boys_like;

drop table pets_like_boys;
drop table boys_like_girls;
drop table girls_like_pets;
- .............start here the first time

create table pets_like_boys (pets_name varchar(32), boys_name varchar(32),primary key (pets_name));
create table boys_like_girls (boys_name varchar(32), girls_name varchar(32),primary key (boys_name));
create table girls_like_pets (girls_name varchar(32), pets_name varchar(32),primary key (girls_name));

insert into pets_like_boys values('fluffy', 'sam');
insert into pets_like_boys values('rover', 'bob');
insert into pets_like_boys values('skippy', 'joe');

insert into boys_like_girls values('bob','jill');
insert into boys_like_girls values('sam','betty');
insert into boys_like_girls values('joe','sue');
insert into boys_like_girls values('george','jill');

insert into girls_like_pets values('sue','fluffy');
insert into girls_like_pets values('jill','skippy');
insert into girls_like_pets values('betty','rover');

alter table pets_like_boys
add constraint pets_like
foreign key (boys_name )
references boys_like_girls
on delete cascade;

alter table boys_like_girls
add constraint boys_like
foreign key (girls_name )
references girls_like_pets
on delete cascade;

alter table girls_like_pets
add constraint girls_like
foreign key (pets_name )
references pets_like_boys
on delete cascade;

select * from pets_like_boys;
select * from boys_like_girls;
select * from girls_like_pets;
delete from boys_like_girls where boys_name = 'bob';
select * from pets_like_boys;
select * from boys_like_girls;
select * from girls_like_pets;

select table_name from user_tables;

on Oracle the tables are emptied...example of the dangers of cascade

from the otn.oracle.com forum
Hi,

TEST@XE> create table pets_like_boys (pets_name varchar(32), boys_name varchar(32),primary key (pets_name));

Table created.

TEST@XE> create table boys_like_girls (boys_name varchar(32), girls_name varchar(32),primary key (boys_name));

Table created.

TEST@XE> create table girls_like_pets (girls_name varchar(32), pets_name varchar(32),primary key (girls_name));

Table created.

TEST@XE> insert into pets_like_boys values('fluffy', 'sam');

1 row created.

TEST@XE> insert into pets_like_boys values('rover', 'bob');

1 row created.

TEST@XE> insert into pets_like_boys values('skippy', 'joe');

1 row created.

TEST@XE> insert into boys_like_girls values('bob','jill');

1 row created.

TEST@XE> insert into boys_like_girls values('sam','betty');

1 row created.

TEST@XE> insert into boys_like_girls values('joe','sue');

1 row created.

TEST@XE> insert into boys_like_girls values('george','jill');

1 row created.

TEST@XE> insert into girls_like_pets values('sue','fluffy');

1 row created.

TEST@XE> insert into girls_like_pets values('jill','skippy');

1 row created.

TEST@XE> insert into girls_like_pets values('betty','rover');

1 row created.

TEST@XE> alter table pets_like_boys add constraint pets_like foreign key (boys_name) references boys_like_girls on delete cascade;

Table altered.

TEST@XE> alter table boys_like_girls add constraint boys_like foreign key (girls_name) references girls_like_pets on delete cascade;

Table altered.

TEST@XE> alter table girls_like_pets add constraint girls_like foreign key (pets_name) references pets_like_boys on delete cascade;

Table altered.

TEST@XE> select * from pets_like_boys;

PETS_NAME BOYS_NAME
-------------------------------- --------------------------------
fluffy sam
rover bob
skippy joe

TEST@XE> select * from boys_like_girls;

BOYS_NAME GIRLS_NAME
-------------------------------- --------------------------------
bob jill
sam betty
joe sue
george jill

TEST@XE> select * from girls_like_pets;

GIRLS_NAME PETS_NAME
-------------------------------- --------------------------------
sue fluffy
jill skippy
betty rover

TEST@XE> delete from boys_like_girls where boys_name = 'bob';

1 row deleted.

TEST@XE> select * from pets_like_boys;

no rows selected

TEST@XE> select * from boys_like_girls;

no rows selected

TEST@XE> select * from girls_like_pets;

no rows selected

Cheers

I was wrong..this does work under mysql

drop table pets_like_boys;
drop table boys_like_girls;
drop table girls_like_pets;

create table pets_like_boys (pets_name varchar(32), boys_name varchar(32),primary key (pets_name)) type=innodb;
create table boys_like_girls (boys_name varchar(32), girls_name varchar(32),primary key (boys_name)) type=innodb;
create table girls_like_pets (girls_name varchar(32), pets_name varchar(32),primary key (girls_name)) type=innodb;

insert into pets_like_boys values('fluffy', 'sam');
insert into pets_like_boys values('rover', 'bob');
insert into pets_like_boys values('skippy', 'joe');

insert into boys_like_girls values('bob','jill');
insert into boys_like_girls values('sam','betty');
insert into boys_like_girls values('joe','sue');
insert into boys_like_girls values('george','jill');

insert into girls_like_pets values('sue','fluffy');
insert into girls_like_pets values('jill','skippy');
insert into girls_like_pets values('betty','rover');

alter table pets_like_boys add constraint pets_like foreign key (boys_name) references boys_like_girls(boys_name) on delete cascade;
alter table boys_like_girls add constraint boys_like foreign key (girls_name) references girls_like_pets(girls_name) on delete cascade;
alter table girls_like_pets add constraint girls_like foreign key (pets_name) references pets_like_boys(pets_name) on delete cascade;

select * from pets_like_boys;
select * from boys_like_girls;
select * from girls_like_pets;
delete from boys_like_girls where boys_name = 'bob';
select * from pets_like_boys;
select * from boys_like_girls;
select * from girls_like_pets;