6. Next step is to start ASM instance. Do not forget to set SID to be +ASM when you try to login with sqlplus in to the instance
$ export ORACLE_SID=+ASM
$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 5 09:28:21 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ASM instance started
Total System Global Area 284565504 bytes
Fixed Size 1336036 bytes
Variable Size 258063644 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL>
7. Lets check the status of our diskgroups
SQL> SELECT name, type, total_mb, free_mb FROM V$ASM_DISKGROUP;
DATA NORMAL 32756 32575
8. Check the list of available disks
SQL> select name, path from V$ASM_DISK;
NAME
------------------------------
PATH
--------------------------------------------------------------------
ORCL:DFDISK4
ORCL:DFDISK5
DFDISK0
ORCL:DFDISK0
DFDISK1
ORCL:DFDISK1
DFDISK2
ORCL:DFDISK2
DFDISK3
ORCL:DFDISK3
FBDISK0
ORCL:FBDISK0
FBDISK1
ORCL:FBDISK1
8 rows selected.
9. And create new diskgroup for flashback
SQL> CREATE DISKGROUP FBA NORMAL REDUNDANCY FAILGROUP fba_fb1 disk 'ORCL:FBDISK0' FAILGROUP fba_fb2 disk 'ORCL:FBDISK1';
Diskgroup created.
10. and see the new list of diskgroups
SQL> select name,total_mb from V$ASM_DISKGROUP;
NAME TOTAL_MB
------------------------------ ----------
DATA 32756
FBA 16378
11. Add two new disks to diskgroup DATA
SQL> ALTER DISKGROUP data ADD DISK 'ORCL:DFDISK4', 'ORCL:DFDISK5';
Diskgroup altered.
SQL> select name,total_mb from V$ASM_DISKGROUP where name='DATA';
NAME TOTAL_MB
------------------------------ ----------
DATA 49134
12. Remove one of the disks from diskgroup
SQL> ALTER DISKGROUP data DROP DISK DFDISK5;
Diskgroup altered.
SQL> select name,total_mb from V$ASM_DISKGROUP where name='DATA';
NAME TOTAL_MB
------------------------------ ----------
DATA 40945
and size is already 40GB
For further information about migration of database to ASM, filepaths, etc, please consult official Oracle documentation about ASM: Oracle® Database Storage Administrator's Guide
Oracle ASM - yet another LVM (system configuration)
4. Its time for some system administrator tasks. Oracle ASM need special mark of disks will work with
4.1. Because of some reason (i don't know why) ASM can work only with partitions, but not with entire disks. So it is need to create one big partition on each disk
[root@rh-asm-ora ~]# fdisk /dev/sda
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
The number of cylinders for this disk is set to 1044.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1044, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1044, default 1044):
Using default value 1044
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
And the similar for the rest of the disks /dev/sdb, /dev/sdc, /dev/sdd, /dev/sde and /dev/sdf
4.2. Next step is to configure the ASMlib. This is done via init script
[root@rh-asm-ora ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]
If you do not get OK on the last line check /var/log/messages. Usual reason is you do not install the correct version of ASMlib. Check on this site Oracle ASMLib. If you cant find modules for your version of kernel you should compile them from source you can get from here: http://oss.oracle.com/projects/oracleasm/
4.3. And check if the kernel module is loaded
[root@rh-asm-ora ~]# lsmod |grep ora
oracleasm 46356 1
4.4. The module is loaded so lets label the disks to be recognized by Oracle ASM. I will dedicate 4 disks for tablespace and 2 disks for flashback
[root@rh-asm-ora ~]# /etc/init.d/oracleasm createdisk dfdisk0 /dev/sda1
Marking disk "dfdisk0" as an ASM disk: [ OK ]
[root@rh-asm-ora ~]# /etc/init.d/oracleasm createdisk dfdisk1 /dev/sdb1
Marking disk "dfdisk1" as an ASM disk: [ OK ]
[root@rh-asm-ora ~]# /etc/init.d/oracleasm createdisk dfdisk2 /dev/sdc1
Marking disk "dfdisk2" as an ASM disk: [ OK ]
[root@rh-asm-ora ~]# /etc/init.d/oracleasm createdisk dfdisk3 /dev/sdd1
Marking disk "dfdisk3" as an ASM disk: [ OK ]
[root@rh-asm-ora ~]# /etc/init.d/oracleasm createdisk fbdisk0 /dev/sde1
Marking disk "fbdisk0" as an ASM disk: [ OK ]
[root@rh-asm-ora ~]# /etc/init.d/oracleasm createdisk fbdisk1 /dev/sdf1
Marking disk "fbdisk1" as an ASM disk: [ OK ]
4.5. Check the ASM volumes
[root@rh-asm-ora ~]# /etc/init.d/oracleasm listdisks
DFDISK0
DFDISK1
DFDISK2
DFDISK3
FBDISK0
FBDISK1
5. The next step is install Oracle ASM software. This is mostly straight-forward process, so just read the installation guide and do it. Please do not forget in version 11gR2 Oracle ASM is part of Grid installation package and not standard Oracle Database installation package
4.1. Because of some reason (i don't know why) ASM can work only with partitions, but not with entire disks. So it is need to create one big partition on each disk
[root@rh-asm-ora ~]# fdisk /dev/sda
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
The number of cylinders for this disk is set to 1044.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1044, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1044, default 1044):
Using default value 1044
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
And the similar for the rest of the disks /dev/sdb, /dev/sdc, /dev/sdd, /dev/sde and /dev/sdf
4.2. Next step is to configure the ASMlib. This is done via init script
[root@rh-asm-ora ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]
If you do not get OK on the last line check /var/log/messages. Usual reason is you do not install the correct version of ASMlib. Check on this site Oracle ASMLib. If you cant find modules for your version of kernel you should compile them from source you can get from here: http://oss.oracle.com/projects/oracleasm/
4.3. And check if the kernel module is loaded
[root@rh-asm-ora ~]# lsmod |grep ora
oracleasm 46356 1
4.4. The module is loaded so lets label the disks to be recognized by Oracle ASM. I will dedicate 4 disks for tablespace and 2 disks for flashback
[root@rh-asm-ora ~]# /etc/init.d/oracleasm createdisk dfdisk0 /dev/sda1
Marking disk "dfdisk0" as an ASM disk: [ OK ]
[root@rh-asm-ora ~]# /etc/init.d/oracleasm createdisk dfdisk1 /dev/sdb1
Marking disk "dfdisk1" as an ASM disk: [ OK ]
[root@rh-asm-ora ~]# /etc/init.d/oracleasm createdisk dfdisk2 /dev/sdc1
Marking disk "dfdisk2" as an ASM disk: [ OK ]
[root@rh-asm-ora ~]# /etc/init.d/oracleasm createdisk dfdisk3 /dev/sdd1
Marking disk "dfdisk3" as an ASM disk: [ OK ]
[root@rh-asm-ora ~]# /etc/init.d/oracleasm createdisk fbdisk0 /dev/sde1
Marking disk "fbdisk0" as an ASM disk: [ OK ]
[root@rh-asm-ora ~]# /etc/init.d/oracleasm createdisk fbdisk1 /dev/sdf1
Marking disk "fbdisk1" as an ASM disk: [ OK ]
4.5. Check the ASM volumes
[root@rh-asm-ora ~]# /etc/init.d/oracleasm listdisks
DFDISK0
DFDISK1
DFDISK2
DFDISK3
FBDISK0
FBDISK1
5. The next step is install Oracle ASM software. This is mostly straight-forward process, so just read the installation guide and do it. Please do not forget in version 11gR2 Oracle ASM is part of Grid installation package and not standard Oracle Database installation package
Oracle ASM - yet another LVM (Instalation)
1. OS installation
For OS I will use CentOS with config:
- 1.5 GB RAM
- 20 GB harddisk for OS and software
- 6*8GB harddisks for ASM
I install only server cluster and exclude some packages like PCMCIA support, smartcard, power management, etc. They are not need for server.
1.1. After installation the first task is to update your system. For this I will use command
yum update yum* rpm* kernel*
At the moment I do not need more updates. After update the kernel its need to reboot server.
1.2. Install prerequisites packages. Here is the list of the packages:
xdpyinfo
libXmp
binutils
compat-libstdc++-33
elfutils-libelf
elfutils-libelf-devel
elfutils-libelf-devel-static
gcc
gcc-c++
glibc
glibc-common
glibc-devel
glibc-headers
kernel-headers
ksh
libaio
libaio-devel
libgcc libgomp
libstdc++
libstdc++-devel
make
sysstat
unixODBC
unixODBC-devel
Here and below the information is get directly from Oracle Technical Network, Documentation section.
First and second packages are not need according to the documentation, but my personal experience show me they are need.
2. Configure OS
2.1. Set kernel parameters
this is the list of parameters you should set in /etc/sysctl.conf to make software run. If some ot the parameters exist in original file just set recommended values
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
Do not forget to execute sysctl -p or reboot the server to get parameters set.
2.2. Add groups
oinstall, oper, dba, asmadmin, asmdba, asmoper
2.3. Create oracle user to have primary group oinstall and member of dba and oper and shell KornShell
2.4. Set in /etc/security/limits.conf number of files and number of processes for oracle user as follow
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
2.5. Add in /etc/profile the settings for ulimits for oracle user
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -u 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi
On my CentOS option, provided by Oracle documentation (ulimit -p 16384) because of version of ksh, so if you are with RHEL 5.x and if the version of ksh is like ksh-20080202-14.
_4.2 use ulimit -u 16384
3. Install Oracle ASM libraries and kernel modules. The packages should be downloaded from https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle18c-linux-180000-5022980.html
Search for "Oracle Database XXX Grid Infrastructure"
Do not forget you should download and install libraries for your distribution and version of kernel. You can use this command to check the version of kernel
[root@rh-asm-ora rpm]# uname -r
2.6.18-164.15.1.
For OS I will use CentOS with config:
- 1.5 GB RAM
- 20 GB harddisk for OS and software
- 6*8GB harddisks for ASM
I install only server cluster and exclude some packages like PCMCIA support, smartcard, power management, etc. They are not need for server.
1.1. After installation the first task is to update your system. For this I will use command
yum update yum* rpm* kernel*
At the moment I do not need more updates. After update the kernel its need to reboot server.
1.2. Install prerequisites packages. Here is the list of the packages:
xdpyinfo
libXmp
binutils
compat-libstdc++-33
elfutils-libelf
elfutils-libelf-devel
elfutils-libelf-devel-static
gcc
gcc-c++
glibc
glibc-common
glibc-devel
glibc-headers
kernel-headers
ksh
libaio
libaio-devel
libgcc libgomp
libstdc++
libstdc++-devel
make
sysstat
unixODBC
unixODBC-devel
Here and below the information is get directly from Oracle Technical Network, Documentation section.
First and second packages are not need according to the documentation, but my personal experience show me they are need.
2. Configure OS
2.1. Set kernel parameters
this is the list of parameters you should set in /etc/sysctl.conf to make software run. If some ot the parameters exist in original file just set recommended values
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
Do not forget to execute sysctl -p or reboot the server to get parameters set.
2.2. Add groups
oinstall, oper, dba, asmadmin, asmdba, asmoper
2.3. Create oracle user to have primary group oinstall and member of dba and oper and shell KornShell
2.4. Set in /etc/security/limits.conf number of files and number of processes for oracle user as follow
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
2.5. Add in /etc/profile the settings for ulimits for oracle user
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -u 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
umask 022
fi
On my CentOS option, provided by Oracle documentation (ulimit -p 16384) because of version of ksh, so if you are with RHEL 5.x and if the version of ksh is like ksh-20080202-14.
_4.2 use ulimit -u 16384
3. Install Oracle ASM libraries and kernel modules. The packages should be downloaded from https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle18c-linux-180000-5022980.html
Search for "Oracle Database XXX Grid Infrastructure"
Do not forget you should download and install libraries for your distribution and version of kernel. You can use this command to check the version of kernel
[root@rh-asm-ora rpm]# uname -r
2.6.18-164.15.1.
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)