I decide to continue the serie with some ideas about the security. For the test purposes security is not so important, but for production and related (preproduction for example) this is considerable point.
On the previous post i use just plain command line and all the valued parameters as username and password are visible in command line:
[oracle@rh-or ~]$ sqlplus -S romeo/pass1234@orcl @date.sql "Today is "
So everyone who have access to the server can see them. If I try to use other technique to try to hide them with environment variable like this:
[oracle@rh-or ~]$ O_USER=romeo
[oracle@rh-or ~]$ O_PASS=pass1234
[oracle@rh-or ~]$ sqlplus -S $O_USER/$O_PASS@orcl @date.sql "Today is "
this will hide information from command line, but it is still visible via environment parameters of the process. For example in Solaris command pargs will provide such information
(https://unixswing.blogspot.com/2018/09/solaris-commands-for-process-management_22.html)
Lets rewrite the script with some inline code
sqlplus -S /nolog << EOD
connect romeo/pass1234@orcl
@date.sql
exit
EOD
The above script will expose in process list only fact of run sqlplus and nothing more. The rest of the information will be "entered" like in interactive session w/o any traces for other users.
Be aware that in the last line of code (word EOD) you should have no other symbols except mentioned word. Otherwise the script will not work on expected manner.
The rights of such script should be set to 700 to avoid disclosure of sensitive information to the extraneous. The only issue is you cant hide the information in script from the user will run it because in UNIX/Linux shells if you want to execute script you should have right to read it.
Showing posts with label RDBMS. Show all posts
Showing posts with label RDBMS. Show all posts
Oracle DB for system administrators, part8 (shell scripts)
In this part I will discuss some shell scripts you can create/use for work automation in Oracle database.
36. One very important, but very dangerous think is to create start and stop script for oracle.
Its important because if you want to stop and start the machine (and database) graceful you should execute serie of commands and always exist possibility to forget one of them. Because of this its good to have some script to stop and start Oracle DB.
36.1. Very important is to edit /etc/oratab file (here and below i will use locations of the files as they are in RHEL) and to have your instance(s) on this way:
[root@rh-or ~]# grep -v ^# /etc/oratab
orcl:/home/oracle/11g2:Y
First value is name of the instance i.e. SID, second is the value of ORACLE_HOME and third one is Y - to start this instance and N do not start it. Lines, starting with # are comments
36.2. Next I will create sample start/stop script
[root@rh-or ~]# more /etc/init.d/oracle
#!/bin/bash
#
# oracle Startup script for start oracle database server
#
ORACLE_HOME=/home/oracle/11g2
ORACLE_SID=orcl
PATH=$PATH:/$ORACLE_HOME/bin
ORACLE=oracle # user to run oracle DB
export ORACLE_HOME ORACLE_SID PATH ORACLE
#
start() {
echo -n $"Starting oracle"
su - -c "dbstart" $ORACLE
su - -c "lsnrctl start" $ORACLE
}
stop() {
echo -n $"Stopping oracle"
su - -c "lsnrctl stop" oracle
su - -c "dbshut" oracle
}
# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
*)
echo $"Usage: $prog {start|stop|restart}"
exit 1
esac
I put my script in /etc/init.d because this is the place for such scripts in Linux, but do not create softlinks in rc.d directories to avoid auto start/restart because this is not a good idea. If You want You can do it
Of course I can use only dbstart and dbshut, but for this i should change those scripts and I personalty prefer do not touch them. And this give me better control over the precedence of start listener and database itself. Actually the only change is to define variable ORACLE_HOME_LISTNER (which in my case is equiv to ORACLE_HOME).
37. the next point is to automate execution of sql scripts from command line w/o starting sqlplus in interactive mode
I prepare one sample script:
[oracle@rh-or ~]$ cat date.sql
set head off
select sysdate from dual;
exit
and I will execute it on this way:
[oracle@rh-or ~]$ sqlplus -S romeo/pass1234@orcl @date.sql
24-MAR-10
[oracle@rh-or ~]$
As you see execution is not different from any shell script. Here I mention in command line explicitly the name of the SID, because this is very helpful in situation you execute script from server with installed oracle client against some oracle server
38. If Your sql script need some parameters to be entered you can enter them as parameters of command line. For script:
[oracle@rh-or ~]$ cat date.sql
set head off
set verify off
select '&1', sysdate from dual;
exit
I need to enter one parameter (usually by hand), but I will set it from command line
[oracle@rh-or ~]$ sqlplus -S romeo/pass1234@orcl @date.sql "Today is "
Today is 24-MAR-10
[oracle@rh-or ~]$
36. One very important, but very dangerous think is to create start and stop script for oracle.
Its important because if you want to stop and start the machine (and database) graceful you should execute serie of commands and always exist possibility to forget one of them. Because of this its good to have some script to stop and start Oracle DB.
36.1. Very important is to edit /etc/oratab file (here and below i will use locations of the files as they are in RHEL) and to have your instance(s) on this way:
[root@rh-or ~]# grep -v ^# /etc/oratab
orcl:/home/oracle/11g2:Y
First value is name of the instance i.e. SID, second is the value of ORACLE_HOME and third one is Y - to start this instance and N do not start it. Lines, starting with # are comments
36.2. Next I will create sample start/stop script
[root@rh-or ~]# more /etc/init.d/oracle
#!/bin/bash
#
# oracle Startup script for start oracle database server
#
ORACLE_HOME=/home/oracle/11g2
ORACLE_SID=orcl
PATH=$PATH:/$ORACLE_HOME/bin
ORACLE=oracle # user to run oracle DB
export ORACLE_HOME ORACLE_SID PATH ORACLE
#
start() {
echo -n $"Starting oracle"
su - -c "dbstart" $ORACLE
su - -c "lsnrctl start" $ORACLE
}
stop() {
echo -n $"Stopping oracle"
su - -c "lsnrctl stop" oracle
su - -c "dbshut" oracle
}
# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
*)
echo $"Usage: $prog {start|stop|restart}"
exit 1
esac
I put my script in /etc/init.d because this is the place for such scripts in Linux, but do not create softlinks in rc.d directories to avoid auto start/restart because this is not a good idea. If You want You can do it
Of course I can use only dbstart and dbshut, but for this i should change those scripts and I personalty prefer do not touch them. And this give me better control over the precedence of start listener and database itself. Actually the only change is to define variable ORACLE_HOME_LISTNER (which in my case is equiv to ORACLE_HOME).
37. the next point is to automate execution of sql scripts from command line w/o starting sqlplus in interactive mode
I prepare one sample script:
[oracle@rh-or ~]$ cat date.sql
set head off
select sysdate from dual;
exit
and I will execute it on this way:
[oracle@rh-or ~]$ sqlplus -S romeo/pass1234@orcl @date.sql
24-MAR-10
[oracle@rh-or ~]$
As you see execution is not different from any shell script. Here I mention in command line explicitly the name of the SID, because this is very helpful in situation you execute script from server with installed oracle client against some oracle server
38. If Your sql script need some parameters to be entered you can enter them as parameters of command line. For script:
[oracle@rh-or ~]$ cat date.sql
set head off
set verify off
select '&1', sysdate from dual;
exit
I need to enter one parameter (usually by hand), but I will set it from command line
[oracle@rh-or ~]$ sqlplus -S romeo/pass1234@orcl @date.sql "Today is "
Today is 24-MAR-10
[oracle@rh-or ~]$
Oracle DB for system administrators, part7
In previous posts I use only commands to retrieve information. Now i time to change something in our database.
31. Insert new employee - long way
SQL> INSERT INTO HR (NOM, FNAME, LNAME, HIREDATE, JOB, SAL, MGR, DEPTNO)
VALUES (501, 'Romeo', 'Ninov', to_date('2010-01-01', 'YYYY-MM-DD'), 'sysadmin', 9000, 999, 210); 2
1 row created.
SQL>
Here I explicitly define mapping between column and value and fill all the columns.
32. So next is to insert new employee - short way
SQL> INSERT INTO HR VALUES (501, 'Test1', 'User1', NULL, NULL, to_date('2010-01-01', 'YYYY-MM-DD'), 9000, 210);
INSERT INTO HR VALUES (501, 'Test1', 'User1', NULL, NULL, to_date('2010-01-01', 'YYYY-MM-DD'), 9000, 210)
*
ERROR at line 1:
ORA-00001: unique constraint (ROMEO.SYS_C0011794) violated
Ha, i get a error, so lets try to investigate whats happen. If you go back to the creation of table HR you will see NOM column is a primary key and should be unique, so lets rewrite this insert and try again
SQL> INSERT INTO HR VALUES (502, 'Test1', 'User1', NULL, NULL, to_date('2010-01-01', 'YYYY-MM-DD'), 9000, 210);
1 row created.
SQL>
Voila, we insert another user.
33. Next we will create user similar to existing one. I will use user Test1 as template
SQL> INSERT INTO hr (nom, fname, lname, job, mgr, hiredate, sal, deptno) select 503 , 'Test2', 'User2', job, mgr, hiredate, sal, deptno from hr where nom=502;
1 row created.
And lets check what is the content of the table
SQL> select * from hr where nom>501;
NOM FNAME LNAME JOB MGR HIREDATE
---------- --------------- --------------- ---------- ---------- ---------
SAL DEPTNO
---------- ----------
502 Test1 User1 01-JAN-10
9000 210
503 Test2 User2 01-JAN-10
9000 210
So the work is done.
34. But I want to increase salary of employee Romeo by 3000
SQL> UPDATE hr SET sal = (select sal from hr where nom=501)+3000 WHERE nom=501;
1 row updated.
and check the new salary
SQL> select sal from hr where nom=501;
SAL
----------
12000
35. And I want to delete the employee Test1 and Test2
SQL> delete hr where nom=502 or nom=503;
2 rows deleted.
36. In case of usage of operations change the information to use command COMMIT or ROLLBACK to finish the transaction or return to previous status respectively. Be aware when you exit from sqlplus the commit command will be executed automatically. If you loose connection to the machine i.e broke connection to sqlplus rollback command will be executed. So I want to confirm changes and store the information
SQL> commit;
Commit complete.
31. Insert new employee - long way
SQL> INSERT INTO HR (NOM, FNAME, LNAME, HIREDATE, JOB, SAL, MGR, DEPTNO)
VALUES (501, 'Romeo', 'Ninov', to_date('2010-01-01', 'YYYY-MM-DD'), 'sysadmin', 9000, 999, 210); 2
1 row created.
SQL>
Here I explicitly define mapping between column and value and fill all the columns.
32. So next is to insert new employee - short way
SQL> INSERT INTO HR VALUES (501, 'Test1', 'User1', NULL, NULL, to_date('2010-01-01', 'YYYY-MM-DD'), 9000, 210);
INSERT INTO HR VALUES (501, 'Test1', 'User1', NULL, NULL, to_date('2010-01-01', 'YYYY-MM-DD'), 9000, 210)
*
ERROR at line 1:
ORA-00001: unique constraint (ROMEO.SYS_C0011794) violated
Ha, i get a error, so lets try to investigate whats happen. If you go back to the creation of table HR you will see NOM column is a primary key and should be unique, so lets rewrite this insert and try again
SQL> INSERT INTO HR VALUES (502, 'Test1', 'User1', NULL, NULL, to_date('2010-01-01', 'YYYY-MM-DD'), 9000, 210);
1 row created.
SQL>
Voila, we insert another user.
33. Next we will create user similar to existing one. I will use user Test1 as template
SQL> INSERT INTO hr (nom, fname, lname, job, mgr, hiredate, sal, deptno) select 503 , 'Test2', 'User2', job, mgr, hiredate, sal, deptno from hr where nom=502;
1 row created.
And lets check what is the content of the table
SQL> select * from hr where nom>501;
NOM FNAME LNAME JOB MGR HIREDATE
---------- --------------- --------------- ---------- ---------- ---------
SAL DEPTNO
---------- ----------
502 Test1 User1 01-JAN-10
9000 210
503 Test2 User2 01-JAN-10
9000 210
So the work is done.
34. But I want to increase salary of employee Romeo by 3000
SQL> UPDATE hr SET sal = (select sal from hr where nom=501)+3000 WHERE nom=501;
1 row updated.
and check the new salary
SQL> select sal from hr where nom=501;
SAL
----------
12000
35. And I want to delete the employee Test1 and Test2
SQL> delete hr where nom=502 or nom=503;
2 rows deleted.
36. In case of usage of operations change the information to use command COMMIT or ROLLBACK to finish the transaction or return to previous status respectively. Be aware when you exit from sqlplus the commit command will be executed automatically. If you loose connection to the machine i.e broke connection to sqlplus rollback command will be executed. So I want to confirm changes and store the information
SQL> commit;
Commit complete.
Oracle DB for system administrators, part6
26. Next I want to see all employees, having salary over average. This will be done using subquery. The idea is to create some selection from table and use the result as source for other select
SQL> SELECT fname, lname, sal FROM hr WHERE sal > (SELECT AVG(sal) FROM hr);
FNAME LNAME SAL
--------------- --------------- ----------
Michael Hartstein 13000
Susan Mavris 6500
Hermann Baer 10000
Shelley Higgins 12008
... snip ...
Alyssa Hutton 8800
Jonathon Taylor 8600
Jack Livingston 8400
50 rows selected.
As you see the subquery is placed within parentheses. You can use 255 levels ob subquery which is very big number for any kind of data.
27. I want to see the list of departments having average salary under maximum of average salary for departments
SQL> SELECT deptno, AVG(sal) FROM hr GROUP BY deptno HAVING AVG(sal) < (SELECT MAX(AVG(sal)) FROM hr GROUP BY deptno) ORDER BY deptno;
DEPTNO AVG(SAL)
---------- ----------
10 4400
20 9500
30 4150
40 6500
50 3475.55556
60 5760
70 10000
80 8955.88235
100 8601.33333
110 10154
10 rows selected.
and to be sure let see the maximum average salary of department.
SQL> select max(avg(sal)) from hr group by deptno;
MAX(AVG(SAL))
-------------
19333.3333
I you can see i use HAVING clause to filter group of rows. It is used because I mix multirow select (SELECT deptno) with single row function (AVG(sal)). If you try w/o this clause you will receive error
Wrong:
SQL> select deptno, avg(sal) from hr where avg(sal) >10000 group by deptno;
select deptno, avg(sal) from hr where avg(sal) >10000 group by deptno
*
ERROR at line 1:
ORA-00934: group function is not allowed here
Correct:
SQL> select deptno, avg(sal) from hr group by deptno having avg(sal) > 10000;
DEPTNO AVG(SAL)
---------- ----------
90 19333.3333
110 10154
29. Next I will try to use wildcard to select only people having TH combination of symbols in first name
SQL> select fname, lname from hr where fname like '%th%';
FNAME LNAME
--------------- ---------------
Matthew Weiss
Sarath Sewall
Elizabeth Bates
Jonathon Taylor
Martha Sullivan
Anthony Cabrio
Timothy Gates
7 rows selected.
SQL>
30. And as last from the points of retrieve information i want to get average salary by department plus average for the company
SQL> SELECT deptno, to_char(avg(sal), 99999.99) FROM hr GROUP BY ROLLUP(deptno) ORDER BY deptno;
DEPTNO TO_CHAR(A
---------- ---------
10 4400.00
20 9500.00
30 4150.00
40 6500.00
50 3475.56
60 5760.00
70 10000.00
80 8955.88
90 19333.33
100 8601.33
110 10154.00
6456.75
12 rows selected.
As you see the last line have no department number and actually is average salary for entire company
SQL> SELECT fname, lname, sal FROM hr WHERE sal > (SELECT AVG(sal) FROM hr);
FNAME LNAME SAL
--------------- --------------- ----------
Michael Hartstein 13000
Susan Mavris 6500
Hermann Baer 10000
Shelley Higgins 12008
... snip ...
Alyssa Hutton 8800
Jonathon Taylor 8600
Jack Livingston 8400
50 rows selected.
As you see the subquery is placed within parentheses. You can use 255 levels ob subquery which is very big number for any kind of data.
27. I want to see the list of departments having average salary under maximum of average salary for departments
SQL> SELECT deptno, AVG(sal) FROM hr GROUP BY deptno HAVING AVG(sal) < (SELECT MAX(AVG(sal)) FROM hr GROUP BY deptno) ORDER BY deptno;
DEPTNO AVG(SAL)
---------- ----------
10 4400
20 9500
30 4150
40 6500
50 3475.55556
60 5760
70 10000
80 8955.88235
100 8601.33333
110 10154
10 rows selected.
and to be sure let see the maximum average salary of department.
SQL> select max(avg(sal)) from hr group by deptno;
MAX(AVG(SAL))
-------------
19333.3333
I you can see i use HAVING clause to filter group of rows. It is used because I mix multirow select (SELECT deptno) with single row function (AVG(sal)). If you try w/o this clause you will receive error
Wrong:
SQL> select deptno, avg(sal) from hr where avg(sal) >10000 group by deptno;
select deptno, avg(sal) from hr where avg(sal) >10000 group by deptno
*
ERROR at line 1:
ORA-00934: group function is not allowed here
Correct:
SQL> select deptno, avg(sal) from hr group by deptno having avg(sal) > 10000;
DEPTNO AVG(SAL)
---------- ----------
90 19333.3333
110 10154
29. Next I will try to use wildcard to select only people having TH combination of symbols in first name
SQL> select fname, lname from hr where fname like '%th%';
FNAME LNAME
--------------- ---------------
Matthew Weiss
Sarath Sewall
Elizabeth Bates
Jonathon Taylor
Martha Sullivan
Anthony Cabrio
Timothy Gates
7 rows selected.
SQL>
30. And as last from the points of retrieve information i want to get average salary by department plus average for the company
SQL> SELECT deptno, to_char(avg(sal), 99999.99) FROM hr GROUP BY ROLLUP(deptno) ORDER BY deptno;
DEPTNO TO_CHAR(A
---------- ---------
10 4400.00
20 9500.00
30 4150.00
40 6500.00
50 3475.56
60 5760.00
70 10000.00
80 8955.88
90 19333.33
100 8601.33
110 10154.00
6456.75
12 rows selected.
As you see the last line have no department number and actually is average salary for entire company
Oracle DB for system administrators, part5
On previous parts I use only sample selects from single table. But this is just the begin, so lets create another table and link the tables
22. create new table
CREATE TABLE "ROMEO"."DEPT"
(
"DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14 BYTE),
"LOC" VARCHAR2(13 BYTE)
)
TABLESPACE "TEST01" ;
and fill it with data so will have name and location of departments
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
23. So lets select first name, last name of employee and name of department
SQL> SELECT hr.fname, hr.lname, dept.dname FROM hr, dept;
FNAME LNAME DNAME
--------------- --------------- --------------
Donald OConnell ACCOUNTING
Douglas Grant ACCOUNTING
Jennifer Whalen ACCOUNTING
...snip...
Kevin Feeney OPERATIONS
424 rows selected.
Hm, somethings wrong, we want to see only names of the person and name of department where he or she work. But see too many lines and very strange output. In this case we create so named Cartesian product or search. This is full combination of all rows from both tables. Lets imagine we have two tables, 1st with 100000 lines and second with 1000 lines. Cartesian search will produce 100 million of lines and we will overload the server. So be careful :)
Here I demonstrate how to use the exact column from table. The format is [Name of table].[Name of column] This help when you have columns with the same name in different tables.
24, So lets restrict the search and join both tables, mapping number of the department from table HR to the department number from table DEPT
SQL> SELECT hr.fname, hr.lname, dept.dname FROM hr, dept WHERE hr.deptno = dept.deptno;
FNAME LNAME DNAME
--------------- --------------- --------------
Jennifer Whalen ACCOUNTING
Michael Hartstein RESEARCH
Pat Fay RESEARCH
Susan Mavris OPERATIONS
Den Raphaely SALES
Alexander Khoo SALES
Shelli Baida SALES
Sigal Tobias SALES
Guy Himuro SALES
Karen Colmenares SALES
10 rows selected.
Hm, strange again, I see only 10 employees, but I have much more records in HR table
SQL> select count(*) from hr;
COUNT(*)
----------
106
SQL>
So lets check the DEPTNO form table HR and the same from table DEPT
SQL> select distinct deptno from hr;
DEPTNO
----------
100
30
20
70
90
110
50
40
80
10
60
11 rows selected.
SQL> select distinct deptno from dept;
DEPTNO
----------
30
20
40
10
SQL>
Aha, in table DEPT I have information only for few departments, not all. Here as you see I use distinct keyword to get only unique department ID.
25. And at the end complicate the select, searching for employee with salary > 10000 (from known departments) and get name of employee, name of department and location
SQL> SELECT hr.fname || ' ' || hr.lname "Employee", dept.dname "Department", dept.loc "Location" FROM hr, dept WHERE hr.deptno = dept.deptno AND sal > 10000;
Employee Department Location
------------------------------- -------------- -------------
Michael Hartstein RESEARCH DALLAS
Den Raphaely SALES CHICAGO
Here I "rename" the names of columns to make output a little bit pretty and make it easy readable
22. create new table
CREATE TABLE "ROMEO"."DEPT"
(
"DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14 BYTE),
"LOC" VARCHAR2(13 BYTE)
)
TABLESPACE "TEST01" ;
and fill it with data so will have name and location of departments
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
23. So lets select first name, last name of employee and name of department
SQL> SELECT hr.fname, hr.lname, dept.dname FROM hr, dept;
FNAME LNAME DNAME
--------------- --------------- --------------
Donald OConnell ACCOUNTING
Douglas Grant ACCOUNTING
Jennifer Whalen ACCOUNTING
...snip...
Kevin Feeney OPERATIONS
424 rows selected.
Hm, somethings wrong, we want to see only names of the person and name of department where he or she work. But see too many lines and very strange output. In this case we create so named Cartesian product or search. This is full combination of all rows from both tables. Lets imagine we have two tables, 1st with 100000 lines and second with 1000 lines. Cartesian search will produce 100 million of lines and we will overload the server. So be careful :)
Here I demonstrate how to use the exact column from table. The format is [Name of table].[Name of column] This help when you have columns with the same name in different tables.
24, So lets restrict the search and join both tables, mapping number of the department from table HR to the department number from table DEPT
SQL> SELECT hr.fname, hr.lname, dept.dname FROM hr, dept WHERE hr.deptno = dept.deptno;
FNAME LNAME DNAME
--------------- --------------- --------------
Jennifer Whalen ACCOUNTING
Michael Hartstein RESEARCH
Pat Fay RESEARCH
Susan Mavris OPERATIONS
Den Raphaely SALES
Alexander Khoo SALES
Shelli Baida SALES
Sigal Tobias SALES
Guy Himuro SALES
Karen Colmenares SALES
10 rows selected.
Hm, strange again, I see only 10 employees, but I have much more records in HR table
SQL> select count(*) from hr;
COUNT(*)
----------
106
SQL>
So lets check the DEPTNO form table HR and the same from table DEPT
SQL> select distinct deptno from hr;
DEPTNO
----------
100
30
20
70
90
110
50
40
80
10
60
11 rows selected.
SQL> select distinct deptno from dept;
DEPTNO
----------
30
20
40
10
SQL>
Aha, in table DEPT I have information only for few departments, not all. Here as you see I use distinct keyword to get only unique department ID.
25. And at the end complicate the select, searching for employee with salary > 10000 (from known departments) and get name of employee, name of department and location
SQL> SELECT hr.fname || ' ' || hr.lname "Employee", dept.dname "Department", dept.loc "Location" FROM hr, dept WHERE hr.deptno = dept.deptno AND sal > 10000;
Employee Department Location
------------------------------- -------------- -------------
Michael Hartstein RESEARCH DALLAS
Den Raphaely SALES CHICAGO
Here I "rename" the names of columns to make output a little bit pretty and make it easy readable
Oracle DB for system administrators, part4
15. I will use command describe to see how I defined the table hr
SQL> describe hr;
Name Null? Type
----------------------------------------- -------- ----------------------------
NOM NOT NULL NUMBER(5)
FNAME NOT NULL VARCHAR2(15)
LNAME VARCHAR2(15)
JOB VARCHAR2(10)
MGR NUMBER(5)
HIREDATE DATE
SAL NOT NULL NUMBER(7,2)
DEPTNO NOT NULL NUMBER(3)
SQL>
this give me the name of columns, null status of column (can be empty or not), type (number, char, LOB, date, etc.) and length
16. I want to examine one special table, named dual
SQL> desc dual
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
SQL>
looks very strange, but i will get system date and time using select from this table
17. get system date and time
SQL> set head off
SQL> select to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS') from dual;
18-Mar-2010 19:34:42
SQL>
Here i set do not see head (set head off) information, only data
18. lets complicate our select and get only few columns and people with salary > 10000
SQL> set head on
SQL> select nom, fname, lname, deptno, sal from hr where sal > 10000;
NOM FNAME LNAME DEPTNO SAL
---------- --------------- --------------- ---------- ----------
201 Michael Hartstein 20 13000
205 Shelley Higgins 110 12008
100 Steven King 90 24000
101 Neena Kochhar 90 17000
102 Lex De Haan 90 17000
108 Nancy Greenberg 100 12008
114 Den Raphaely 30 11000
145 John Russell 80 14000
146 Karen Partners 80 13500
147 Alberto Errazuriz 80 12000
148 Gerald Cambrault 80 11000
NOM FNAME LNAME DEPTNO SAL
---------- --------------- --------------- ---------- ----------
149 Eleni Zlotkey 80 10500
162 Clara Vishney 80 10500
168 Lisa Ozer 80 11500
174 Ellen Abel 80 11000
15 rows selected.
SQL>
19. I will resort the result by salary and want to see the bigger salary on the top
SQL> select nom, fname, lname, deptno, sal from hr where sal > 10000 order by sal desc;
NOM FNAME LNAME DEPTNO SAL
---------- --------------- --------------- ---------- ----------
100 Steven King 90 24000
101 Neena Kochhar 90 17000
102 Lex De Haan 90 17000
145 John Russell 80 14000
146 Karen Partners 80 13500
201 Michael Hartstein 20 13000
108 Nancy Greenberg 100 12008
205 Shelley Higgins 110 12008
147 Alberto Errazuriz 80 12000
168 Lisa Ozer 80 11500
174 Ellen Abel 80 11000
NOM FNAME LNAME DEPTNO SAL
---------- --------------- --------------- ---------- ----------
148 Gerald Cambrault 80 11000
114 Den Raphaely 30 11000
149 Eleni Zlotkey 80 10500
162 Clara Vishney 80 10500
15 rows selected.
SQL>
20. let me get the average salary for people, who have salary >10000
SQL> select avg(sal) from hr where sal >10000;
AVG(SAL)
----------
13334.4
SQL>
21. And let me get average salary, but by department
SQL> select to_char( avg(sal), '99999.99') , deptno from hr group by deptno;
TO_CHAR(A DEPTNO
--------- ----------
8601.33 100
4150.00 30
9500.00 20
10000.00 70
19333.33 90
10154.00 110
3475.56 50
6500.00 40
8955.88 80
4400.00 10
5760.00 60
11 rows selected.
SQL>
SQL> describe hr;
Name Null? Type
----------------------------------------- -------- ----------------------------
NOM NOT NULL NUMBER(5)
FNAME NOT NULL VARCHAR2(15)
LNAME VARCHAR2(15)
JOB VARCHAR2(10)
MGR NUMBER(5)
HIREDATE DATE
SAL NOT NULL NUMBER(7,2)
DEPTNO NOT NULL NUMBER(3)
SQL>
this give me the name of columns, null status of column (can be empty or not), type (number, char, LOB, date, etc.) and length
16. I want to examine one special table, named dual
SQL> desc dual
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
SQL>
looks very strange, but i will get system date and time using select from this table
17. get system date and time
SQL> set head off
SQL> select to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS') from dual;
18-Mar-2010 19:34:42
SQL>
Here i set do not see head (set head off) information, only data
18. lets complicate our select and get only few columns and people with salary > 10000
SQL> set head on
SQL> select nom, fname, lname, deptno, sal from hr where sal > 10000;
NOM FNAME LNAME DEPTNO SAL
---------- --------------- --------------- ---------- ----------
201 Michael Hartstein 20 13000
205 Shelley Higgins 110 12008
100 Steven King 90 24000
101 Neena Kochhar 90 17000
102 Lex De Haan 90 17000
108 Nancy Greenberg 100 12008
114 Den Raphaely 30 11000
145 John Russell 80 14000
146 Karen Partners 80 13500
147 Alberto Errazuriz 80 12000
148 Gerald Cambrault 80 11000
NOM FNAME LNAME DEPTNO SAL
---------- --------------- --------------- ---------- ----------
149 Eleni Zlotkey 80 10500
162 Clara Vishney 80 10500
168 Lisa Ozer 80 11500
174 Ellen Abel 80 11000
15 rows selected.
SQL>
19. I will resort the result by salary and want to see the bigger salary on the top
SQL> select nom, fname, lname, deptno, sal from hr where sal > 10000 order by sal desc;
NOM FNAME LNAME DEPTNO SAL
---------- --------------- --------------- ---------- ----------
100 Steven King 90 24000
101 Neena Kochhar 90 17000
102 Lex De Haan 90 17000
145 John Russell 80 14000
146 Karen Partners 80 13500
201 Michael Hartstein 20 13000
108 Nancy Greenberg 100 12008
205 Shelley Higgins 110 12008
147 Alberto Errazuriz 80 12000
168 Lisa Ozer 80 11500
174 Ellen Abel 80 11000
NOM FNAME LNAME DEPTNO SAL
---------- --------------- --------------- ---------- ----------
148 Gerald Cambrault 80 11000
114 Den Raphaely 30 11000
149 Eleni Zlotkey 80 10500
162 Clara Vishney 80 10500
15 rows selected.
SQL>
20. let me get the average salary for people, who have salary >10000
SQL> select avg(sal) from hr where sal >10000;
AVG(SAL)
----------
13334.4
SQL>
21. And let me get average salary, but by department
SQL> select to_char( avg(sal), '99999.99') , deptno from hr group by deptno;
TO_CHAR(A DEPTNO
--------- ----------
8601.33 100
4150.00 30
9500.00 20
10000.00 70
19333.33 90
10154.00 110
3475.56 50
6500.00 40
8955.88 80
4400.00 10
5760.00 60
11 rows selected.
SQL>
Oracle DB for system administrators, part3
11. Lets login as user romeo and check from other console the connection
[oracle@rh-or ~]$ sqlplus romeo/pass1234
SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 14 07:31:14 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
and check from other console the connection
SQL> select username,status, saddr from v$session where username is not null;
USERNAME STATUS SADDR
------------------------------ -------- --------
ROMEO INACTIVE 38A9DDA8
SYS ACTIVE 38A88A48
SQL>
12. Next step is to create one table.
I will make HR table (usual example) with fields:
NOM - unique identification number of employee
FNAME - his or her first name (cant be empty)
LNAME - his or her last name
JOB - job description
MGR - ID of his or her manager (see NOM)
HIREDATE - date on which this employee is hired. By default will be get system date when record is inserted
SAL - salary of employee, cant be empty
DEPTNO - number of department where employee work, cant be empty (similar to NOM)
SQL> CREATE TABLE hr (
nom NUMBER(5) PRIMARY KEY,
fname VARCHAR2(15) NOT NULL,
lname VARCHAR2(15),
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2) NOT NULL,
deptno NUMBER(3) NOT NULL)
TABLESPACE TEST01; 2 3 4 5 6 7 8 9 10
Table created.
SQL>
and check the status of this database
SQL> select * from hr;
no rows selected
SQL>
13. The next step will be to fill this table with data
Here are possible two ways (actually many, but i try to simplify and not go indeep)
13.1. Using direct enter of data via INSERT commands
INSERT INTO HR (NOM, FNAME, LNAME, HIREDATE, JOB, SAL, MGR, DEPTNO)
VALUES (198.0, 'Donald', 'OConnell', to_date('2007-06-21', 'YYYY-MM-DD'), 'SH_CLERK', 2600.0, 124.0, 50.0);
INSERT INTO HR (NOM, FNAME, LNAME, HIREDATE, JOB, SAL, MGR, DEPTNO)
VALUES (199.0, 'Douglas', 'Grant', to_date('2008-01-13', 'YYYY-MM-DD'), 'SH_CLERK', 2600.0, 124.0, 50.0);
... snip....
but this method can be boring if you have more that 10 records.
13.2. Other way is to use SQL*Loader to create mass import of data
Here is how data looks like:
[oracle@rh-or ~]$ cat EMPLOYEES.csv
NOM,FNAME,LNAME,HIREDATE,JOB,SAL,MGR,DEPTNO
198,Donald,OConnell,2007-06-21,SH_CLERK,2600,124,50
199,Douglas,Grant,2008-01-13,SH_CLERK,2600,124,50
... snip ...
I prepare two files, one shell file to start the program
[oracle@rh-or ~]$ cat EMPLOYEES.sh
sqlldr USERID=romeo/pass1234 CONTROL=EMPLOYEES.ctl skip=1
where sqlldr is the name of the program, USERID is username and password CONTROL is the name of control file and skip is becasue i do not want to insert header ot CSV file in to the table.
This is control file
[oracle@rh-or ~]$ cat EMPLOYEES.ctl
load data
infile 'EMPLOYEES.csv' "str '\r\n'"
append
into table HR
fields terminated by ','
OPTIONALLY ENCLOSED BY '"'
trailing nullcols
( NOM CHAR(4000),
FNAME CHAR(4000),
LNAME CHAR(4000),
HIREDATE DATE "YYYY-MM-DD",
JOB CHAR(4000),
SAL CHAR(4000),
MGR CHAR(4000),
DEPTNO CHAR(4000)
)
It just define serie of commands to be executed be SQL*Loader as filename from where to get the date, name of the table where the data will be inserted, field separator, format of the date, etc.
And here is the result:
[oracle@rh-or ~]$ sh EMPLOYEES.sh
SQL*Loader: Release 11.2.0.1.0 - Production on Sun Mar 14 08:09:20 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 9
Commit point reached - logical record count 18
Commit point reached - logical record count 27
Commit point reached - logical record count 36
Commit point reached - logical record count 45
Commit point reached - logical record count 54
Commit point reached - logical record count 63
Commit point reached - logical record count 72
Commit point reached - logical record count 81
Commit point reached - logical record count 90
Commit point reached - logical record count 99
Commit point reached - logical record count 107
[oracle@rh-or ~]$
14. And lets check what is content of this table
SQL> select * from hr;
198 Donald OConnell SH_CLERK 124 21-JUN-07
2600 50
199 Douglas Grant SH_CLERK 124 13-JAN-08
2600 50
200 Jennifer Whalen AD_ASST 101 17-SEP-03
4400 10
REMARK: Data, used in above example is selection from sample database, provided by Oracle as part of Oracle 11g2 software
[oracle@rh-or ~]$ sqlplus romeo/pass1234
SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 14 07:31:14 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
and check from other console the connection
SQL> select username,status, saddr from v$session where username is not null;
USERNAME STATUS SADDR
------------------------------ -------- --------
ROMEO INACTIVE 38A9DDA8
SYS ACTIVE 38A88A48
SQL>
12. Next step is to create one table.
I will make HR table (usual example) with fields:
NOM - unique identification number of employee
FNAME - his or her first name (cant be empty)
LNAME - his or her last name
JOB - job description
MGR - ID of his or her manager (see NOM)
HIREDATE - date on which this employee is hired. By default will be get system date when record is inserted
SAL - salary of employee, cant be empty
DEPTNO - number of department where employee work, cant be empty (similar to NOM)
SQL> CREATE TABLE hr (
nom NUMBER(5) PRIMARY KEY,
fname VARCHAR2(15) NOT NULL,
lname VARCHAR2(15),
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2) NOT NULL,
deptno NUMBER(3) NOT NULL)
TABLESPACE TEST01; 2 3 4 5 6 7 8 9 10
Table created.
SQL>
and check the status of this database
SQL> select * from hr;
no rows selected
SQL>
13. The next step will be to fill this table with data
Here are possible two ways (actually many, but i try to simplify and not go indeep)
13.1. Using direct enter of data via INSERT commands
INSERT INTO HR (NOM, FNAME, LNAME, HIREDATE, JOB, SAL, MGR, DEPTNO)
VALUES (198.0, 'Donald', 'OConnell', to_date('2007-06-21', 'YYYY-MM-DD'), 'SH_CLERK', 2600.0, 124.0, 50.0);
INSERT INTO HR (NOM, FNAME, LNAME, HIREDATE, JOB, SAL, MGR, DEPTNO)
VALUES (199.0, 'Douglas', 'Grant', to_date('2008-01-13', 'YYYY-MM-DD'), 'SH_CLERK', 2600.0, 124.0, 50.0);
... snip....
but this method can be boring if you have more that 10 records.
13.2. Other way is to use SQL*Loader to create mass import of data
Here is how data looks like:
[oracle@rh-or ~]$ cat EMPLOYEES.csv
NOM,FNAME,LNAME,HIREDATE,JOB,SAL,MGR,DEPTNO
198,Donald,OConnell,2007-06-21,SH_CLERK,2600,124,50
199,Douglas,Grant,2008-01-13,SH_CLERK,2600,124,50
... snip ...
I prepare two files, one shell file to start the program
[oracle@rh-or ~]$ cat EMPLOYEES.sh
sqlldr USERID=romeo/pass1234 CONTROL=EMPLOYEES.ctl skip=1
where sqlldr is the name of the program, USERID is username and password CONTROL is the name of control file and skip is becasue i do not want to insert header ot CSV file in to the table.
This is control file
[oracle@rh-or ~]$ cat EMPLOYEES.ctl
load data
infile 'EMPLOYEES.csv' "str '\r\n'"
append
into table HR
fields terminated by ','
OPTIONALLY ENCLOSED BY '"'
trailing nullcols
( NOM CHAR(4000),
FNAME CHAR(4000),
LNAME CHAR(4000),
HIREDATE DATE "YYYY-MM-DD",
JOB CHAR(4000),
SAL CHAR(4000),
MGR CHAR(4000),
DEPTNO CHAR(4000)
)
It just define serie of commands to be executed be SQL*Loader as filename from where to get the date, name of the table where the data will be inserted, field separator, format of the date, etc.
And here is the result:
[oracle@rh-or ~]$ sh EMPLOYEES.sh
SQL*Loader: Release 11.2.0.1.0 - Production on Sun Mar 14 08:09:20 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 9
Commit point reached - logical record count 18
Commit point reached - logical record count 27
Commit point reached - logical record count 36
Commit point reached - logical record count 45
Commit point reached - logical record count 54
Commit point reached - logical record count 63
Commit point reached - logical record count 72
Commit point reached - logical record count 81
Commit point reached - logical record count 90
Commit point reached - logical record count 99
Commit point reached - logical record count 107
[oracle@rh-or ~]$
14. And lets check what is content of this table
SQL> select * from hr;
198 Donald OConnell SH_CLERK 124 21-JUN-07
2600 50
199 Douglas Grant SH_CLERK 124 13-JAN-08
2600 50
200 Jennifer Whalen AD_ASST 101 17-SEP-03
4400 10
REMARK: Data, used in above example is selection from sample database, provided by Oracle as part of Oracle 11g2 software
Oracle DB for system administrators, part2
5. lets login and see the list of tablespaces
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
6 rows selected.
SQL>
6. Next we will add new tablespace for tests
SQL> create tablespace test01 datafile '/home/oracle/base/data/orcl/test01.dbf' size 500M extent management local;
Tablespace created.
SQL>
7. And check if its created
SQL> select * from v$tablespace where name = 'TEST01';
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
8 TEST01 YES NO YES
SQL>
8. Lets create user
SQL> create user romeo profile default identified by pass1234 default tablespace TEST01 ;
User created.
SQL>
The user is named romeo, have password pass1234 and default tablespace test01
9. And unlock the user
SQL> alter user romeo account unlock;
User altered.
SQL>
10. give him/her rights to connect and other common privileges
SQL> grant resource, connect to romeo;
Grant succeeded.
SQL>
P.S. For detailed explanation of terminology and concept of Oracle database please consult appropriate documentation
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
6 rows selected.
SQL>
6. Next we will add new tablespace for tests
SQL> create tablespace test01 datafile '/home/oracle/base/data/orcl/test01.dbf' size 500M extent management local;
Tablespace created.
SQL>
7. And check if its created
SQL> select * from v$tablespace where name = 'TEST01';
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
8 TEST01 YES NO YES
SQL>
8. Lets create user
SQL> create user romeo profile default identified by pass1234 default tablespace TEST01 ;
User created.
SQL>
The user is named romeo, have password pass1234 and default tablespace test01
9. And unlock the user
SQL> alter user romeo account unlock;
User altered.
SQL>
10. give him/her rights to connect and other common privileges
SQL> grant resource, connect to romeo;
Grant succeeded.
SQL>
P.S. For detailed explanation of terminology and concept of Oracle database please consult appropriate documentation
Oracle DB for system administrators, part1
This serie of posts is intended to give kickstart overview on oracle database for system administrators. For more deep knowledge please consult oracle documentation Here I will use only command line utilities (much elegant for automation). If you prefer you can use many other tools like OEM, Toad, SQL Developer, etc.
1. Let start the database I asume you are login as oracle user, or any other user, member of DBA group
1.1 First start listener
[oracle@rh-or ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 07-MAR-2010 13:05:50 Copyright (c) 1991, 2009, Oracle. All rights reserved. Starting /home/oracle/11g2/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.1.0 - Production System parameter file is /home/oracle/11g2/network/admin/listener.ora Log messages written to /home/oracle/base/diag/tnslsnr/rh-or/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh-or)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 07-MAR-2010 13:05:50 Uptime 0 days 0 hr. 0 min. 2 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /home/oracle/11g2/network/admin/listener.ora Listener Log File /home/oracle/base/diag/tnslsnr/rh-or/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh-or)(PORT=1521))) The listener supports no services The command completed successfully
1.2 Then start the database itself
[oracle@rh-or ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 7 13:06:37 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance.
SQL> startup
ORACLE instance started. Total System Global Area 422670336 bytes Fixed Size 1336960 bytes Variable Size 318769536 bytes Database Buffers 96468992 bytes Redo Buffers 6094848 bytes Database mounted. Database opened.
SQL>
2. Check if database and listener are run
[oracle@rh-or ~]$ ps -efl|grep lsnr
0 S oracle 10324 1 0 75 0 - 10527 stext 13:05 ? 00:00:00 /home/oracle/11g2/bin/tnslsnr LISTENER -inherit
0 R oracle 10473 3892 0 79 0 - 428 - 13:07 pts/0 00:00:00 grep lsnr
[oracle@rh-or ~]$ ps -efl|grep ora_
0 S oracle 10376 1 0 75 0 - 142554 - 13:06 ? 00:00:00 ora_pmon_orcl
0 S oracle 10378 1 0 58 - - 142388 - 13:06 ? 00:00:00 ora_vktm_orcl
0 S oracle 10382 1 0 75 0 - 142388 - 13:06 ? 00:00:00 ora_gen0_orcl
0 S oracle 10384 1 0 78 0 - 142388 - 13:06 ? 00:00:00 ora_diag_orcl
0 S oracle 10386 1 0 75 0 - 142390 - 13:06 ? 00:00:00 ora_dbrm_orcl
0 S oracle 10388 1 0 75 0 - 142388 - 13:06 ? 00:00:00 ora_psp0_orcl
0 S oracle 10390 1 0 78 0 - 142516 - 13:06 ? 00:00:00 ora_dia0_orcl
0 S oracle 10392 1 0 78 0 - 142388 - 13:06 ? 00:00:00 ora_mman_orcl
0 S oracle 10394 1 0 75 0 - 143363 - 13:06 ? 00:00:00 ora_dbw0_orcl
0 S oracle 10396 1 0 75 0 - 146276 - 13:06 ? 00:00:00 ora_lgwr_orcl
0 S oracle 10398 1 0 76 0 - 142388 - 13:06 ? 00:00:00 ora_ckpt_orcl
0 S oracle 10400 1 0 75 0 - 142395 - 13:06 ? 00:00:00 ora_smon_orcl
0 S oracle 10402 1 0 75 0 - 142389 - 13:06 ? 00:00:00 ora_reco_orcl
3. Lets try to login to database
[oracle@rh-or ~]$ sqlplus user/password@SID
SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 7 13:01:43 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
where user is the username you want to login, password is the password for this user and SID is the the SID identifier of this instance of Oracle database. SID can be name of the SID, hostname or IP address. Above we are login on different way which is possible only if you are login on the same host as database and you are user Oracle DB start 4. Lets make our first retrieve of information from database
SQL> select username,user_id from dba_users;
USERNAME USER_ID ------------------------------ ---------- SYSTEM 5 SYS 0 MGMT_VIEW 74 DBSNMP 30 SYSMAN 72 OUTLN 9 OLAPSYS 61 SI_INFORMTN_SCHEMA 56
Here we select columns username and user_id from table of users, defined in database, named dba_users
1. Let start the database I asume you are login as oracle user, or any other user, member of DBA group
1.1 First start listener
[oracle@rh-or ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 07-MAR-2010 13:05:50 Copyright (c) 1991, 2009, Oracle. All rights reserved. Starting /home/oracle/11g2/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.1.0 - Production System parameter file is /home/oracle/11g2/network/admin/listener.ora Log messages written to /home/oracle/base/diag/tnslsnr/rh-or/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh-or)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 07-MAR-2010 13:05:50 Uptime 0 days 0 hr. 0 min. 2 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /home/oracle/11g2/network/admin/listener.ora Listener Log File /home/oracle/base/diag/tnslsnr/rh-or/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh-or)(PORT=1521))) The listener supports no services The command completed successfully
1.2 Then start the database itself
[oracle@rh-or ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 7 13:06:37 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance.
SQL> startup
ORACLE instance started. Total System Global Area 422670336 bytes Fixed Size 1336960 bytes Variable Size 318769536 bytes Database Buffers 96468992 bytes Redo Buffers 6094848 bytes Database mounted. Database opened.
SQL>
2. Check if database and listener are run
[oracle@rh-or ~]$ ps -efl|grep lsnr
0 S oracle 10324 1 0 75 0 - 10527 stext 13:05 ? 00:00:00 /home/oracle/11g2/bin/tnslsnr LISTENER -inherit
0 R oracle 10473 3892 0 79 0 - 428 - 13:07 pts/0 00:00:00 grep lsnr
[oracle@rh-or ~]$ ps -efl|grep ora_
0 S oracle 10376 1 0 75 0 - 142554 - 13:06 ? 00:00:00 ora_pmon_orcl
0 S oracle 10378 1 0 58 - - 142388 - 13:06 ? 00:00:00 ora_vktm_orcl
0 S oracle 10382 1 0 75 0 - 142388 - 13:06 ? 00:00:00 ora_gen0_orcl
0 S oracle 10384 1 0 78 0 - 142388 - 13:06 ? 00:00:00 ora_diag_orcl
0 S oracle 10386 1 0 75 0 - 142390 - 13:06 ? 00:00:00 ora_dbrm_orcl
0 S oracle 10388 1 0 75 0 - 142388 - 13:06 ? 00:00:00 ora_psp0_orcl
0 S oracle 10390 1 0 78 0 - 142516 - 13:06 ? 00:00:00 ora_dia0_orcl
0 S oracle 10392 1 0 78 0 - 142388 - 13:06 ? 00:00:00 ora_mman_orcl
0 S oracle 10394 1 0 75 0 - 143363 - 13:06 ? 00:00:00 ora_dbw0_orcl
0 S oracle 10396 1 0 75 0 - 146276 - 13:06 ? 00:00:00 ora_lgwr_orcl
0 S oracle 10398 1 0 76 0 - 142388 - 13:06 ? 00:00:00 ora_ckpt_orcl
0 S oracle 10400 1 0 75 0 - 142395 - 13:06 ? 00:00:00 ora_smon_orcl
0 S oracle 10402 1 0 75 0 - 142389 - 13:06 ? 00:00:00 ora_reco_orcl
3. Lets try to login to database
[oracle@rh-or ~]$ sqlplus user/password@SID
SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 7 13:01:43 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
where user is the username you want to login, password is the password for this user and SID is the the SID identifier of this instance of Oracle database. SID can be name of the SID, hostname or IP address. Above we are login on different way which is possible only if you are login on the same host as database and you are user Oracle DB start 4. Lets make our first retrieve of information from database
SQL> select username,user_id from dba_users;
USERNAME USER_ID ------------------------------ ---------- SYSTEM 5 SYS 0 MGMT_VIEW 74 DBSNMP 30 SYSMAN 72 OUTLN 9 OLAPSYS 61 SI_INFORMTN_SCHEMA 56
Here we select columns username and user_id from table of users, defined in database, named dba_users
Subscribe to:
Posts (Atom)