Oracle Fundamental I - Chapter 5: Data Dictionary and Dynamic Performance Views

In this chapter, we will discuss the other system-built objects, that are central to the Oracle database, which provides the important information to manage the Oracle database and administration work.

Data Dictionary
One of the most important parts of an oracle database is its data dictionary, which is a read-only set of tables and views that provides information about its associated database. The data dictionary is updated by the oracle server whenever a data definition language (DDL) command is executed. In addition, data manipulation language (DML) commands, such as one that causes a table to extend, can update the data dictionary.
Not only is the data dictionary central to every oracle database, it is an important source of information for all users, from end users to application designers and database administrators.
SQL statements are  used to access the data dictionary.

The data dictionary contains two parts:

• Base tables
• stores description of the database
• Created with CREATE DATABASE
• Data dictionary views
• Used to simplify the base table information
• Accessed through public synonyms
• Created with the catalog.sql script

The data dictionary provides information about:

• Logical and physical database structures
• Definitions and space allocations of objects
• Integrity constraints
• Users
• Roles
• Privileges
• Auditing

There are three categories of Data Dictionary static views:
Distinguished by their scope:
DBA: What is in all the schemas
All: What the user can access
USER: What is in the user's schema

To query on all objects in the database, the DBA can issue the following statement:
SQL> SELECT owner, object_name, object_type FROM dba_objects;
The following example returns information about all the objects to which a user has access:
SQL> SELECT owner, object_name, object_type FROM all_objects;
The following query returns all the objects contained in the user's schema:
SQL> SELECT OBJECT_NAME, OBJECT_TYPE from users_objects;

Some Data Dictionary Examples below:

• General overview: DICTIONARY, DICT_COLUMNS
• Schema objects: DBA_TABLES, DBA_INDEXES, DBA_CONSTRAINTS
• Space allocation: DBA_SEGMEN?TS, DBA_EXTENTS
• Database structure: DBA_TABLESPACES, DBA_DATA_FILES

Dynamic Performance Tables:
Throughout its operation, the Oracle server records current database activity in a set of virtual tables called dynamic performance views. These virtual tables exist in memory only when the database is running, to reflect real-time conditions of the database operation. They point to actual sources of information in memory and the control file.
These tables are not true tables, and are not to be accessed by most users; however, DBA can query, grant the SELECT privilege, and create views on these views. These views are sometimes called fixed views because they cannot be altered or removed by the DBA.
The dynamic performance tables are owned by SYS, and their names all begin with V_$.
Views are creaetd on these tables, and then public synonyms are created for the views. The synonym names begin with V$. For example, the V$DATAFILE view contains information about the database's data files, adn the V$FIXED_TABLE view containts information about all of the dynami performance tables and views in the database.
Some examples: V$CONTROLFILE, V$DATABASE, V$DATAFILE, V$INSTANCE, V$PARAMETER, V$SESSION, V$SGA.

Summary:
There are more than hundreds of data dictionary views and dynamic views in Oracle database. It's almost impossible for any one to remember all the views in head. One general way to find out the data dictionary view is to issue the query like:
"select TABLE_NAME from DICTIONARY where TABLE_NAME like 'INDEX';"
It will return the data dictionary views which has INDEX in its name.

Oracle Fundamental I - Chapter 4: Creating a Database

In last chapter, we taked about how to manager an Oracle instance. As we discussed, the oracle DBMS is composed by two major parts, the Oracle instance and Oracle database. In this chapter, we will discuss how to manager the Oracle database.

Creation Prerequisites
To create a new database, you must have the following:

• Aprivileged account authenticated by one of the following:
• Operating system
• Password file
• Sufficient memory to start the instance
• Sufficient disk space for the planned database

Using Password File Authentication

• Create the password file using the password utility:

$ orapwd file=$ORACLE_HOME/dbs/orapwU15 password=admin entries=5

• Set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE in initialization parameter file
• Add users to the password file
• Assign appropriate privileges to each user

GRANT SYSDBA TO HR;

An Oracle database can be created by:

• Oracle Universal Installer
• Oracle Database Configuration Assistant
• Graphical user interface
• Java-based
• Launched by the Oracle Universal Installer
• Can be used as a stand-alone application
• The CREATE DATABASE command

In this chapter, we will discuss the procedure based on CREATE DATABASE command.

Before creating a database, some operating system environment must be properly configured:
ex: on the values
ORACLE_BASE: /opt/oracle
ORACLE_HOME: /opt/oracle/product/11.2
ORACLE_SID: DB1
ORA_NLS33: /$ORACLE_HOME/ocommon/nls/admin/data
PATH: /$ORACLE_HOME/bin
LD_LIBRARY_PATH: $ORACLE_HOME/bin

Creating a database manually:

• choose a unique instance and database name
• choose a database character set
• Set operating system variables
• Create the initialization parameter set
• Start the instance in NOMOUNT stage
• Create and execute the CREATE DATABASE command
• Run scripts to generate the data dictionary and accomplish post-creation steps
• catalog.sql: creates the views on the base tables and on the dynamic performance views, and their synonyms.
• catproc.sql: creates the packages and procedures required to use PL/SQL.
• pupbld.sql: creates the Product User Profile table and realted procedure, this scrip must be run as system.
• Create additional tablespaces as needed

Oracle has a feature called Oracle Managed Files (OMF) to help you easily managing the location to data files.

• OMF are established by setting two parameters:
• DB_CREATE_FILE_DEST: Set to give the default locatio for data files
• DB_CREATE_ONLINE_LOG_DEST_n: Set to give the default locations for online redo log fiels and control files
• Maximum of five locations
• Once specifying those two parameter, the create database command can be as simple as:

SQL> CREATE DATABASE dba01;

Oracle Fundamental I - Chapter 3: Managing an Oracle Instance

In this chapter, you will learn the fundamental skills of managing an Oracle instance.
An Oracle instance is the bunch of server process, which plays the core processing to manage the Oracle database. It's composed by memory segments and server processes.

The fist thing you need to manage is to create the Initialization Parameter file.
Two types of parameters:

• Explicit: Having an entry in the file
• Implicit: No entry within the file, but assuming the Oracle default values;

Multiple parameter file can exist.

Parameter file (pfile):

• pfile (parameter file): The text based parameter file to configure the Oracle instance.
• modified with an operating system editor
• Modifications made manually
• changes take effect on the next start up
• Only opened during instance start up
• Default location is $ORACLE_HOME/dbs
• Creating a PFILE
• You create a pfile from the sameple pfile:
• Sample is installed by the Oracle Universal Installer
• Copy sample using operating system copy command
• Uniquely identified by database SID. 

cp init.ora $ORACLE_HOME/dbs/initdba01.ora

• Modify the initSID.ora file
• Edit the parameters
• Specific to database needs

init.ora file example:

db_name = dba01
instance_name = dba01
control_files = ( /home/dba01/ORADATA/u01/control01dba01.ctl,
/home/dba01/ORADATA/u02/control01dba02.ctl)
db_block_size = 4096
db_cache_size = 4M
shared_pool_size = 50000000
java_pool_size = 50000000
max_dump_file_size = 10240
background_dump_dest = /home/dba01/ADMIN/BDUMP
user_dump_dest = /home/dba01/ADMIN/UDUMP
core_dump_dest = /home/dba01/ADMIN/CDUMP
undo_management = AUTO
undo_tablespace = UNDOTBS
. . .

Server Parameter File (spfile):

• spfile (server parameter file): The binary based parameter file, since 9i it's the default parameter file an Oracle instance will look at first
• Binary based file
• Maintained by the Oracle server
• Always resides on the server side
• Ability to make changes persistent across shut down and start up
• Can self-tune parameter values
• Can Have Recovery Manager support backing up to the initialization parameter file.
• Creating an spfile
• Created frmo a pfile file

CREATE SPFILE = ‘$ORACLE_HOME/dbs/spfileDBA01.ora’ FROM PFILE = ‘$ORACLE_HOME/dbs/initDBA01.ora’;

• where
• SPFILE-NAME: SPFILE to be created
• PFILE-NAME: PFILE creating the SPFILE
• This command can be executed before and after instance start up.

spfile example:

* .background_dump_dest= ‘/home/dba01/ADMIN/BDUMP’
*.compatible=’9.2.0′
*.control_files=’/home/dba01/ORADATA/u01/ctrl01.ctl’ *.core_dump_dest= ‘/home/dba01/ADMIN/CDUMP’
*.db_block_size=4096
*.db_name=’dba01′
*.db_domain= ‘world’
*.global_names=TRUE
*.instance_name=’dba01′
*.remote_login_passwordfile=’exclusive’
*.java_pool_size=50000000
*.shared_pool_size=50000000
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS’

  

If the SPFILE-NAME and PFILE-NAME are not included in the syntax, Oracle will use the default PFILE to generate an SPFILE with a system-generated name.

SQL> CREATE SPFILE FROM PFILE;

To modify a spfile, you need to issue the 'alter system set' command to change the value of an parameter through sqlplus command prompt.

ALTER SYSTEM SET parameter_name = parameter_value
  [ COMMENT 'text' ]  [ SCOPE = MEMORY|SPFILE|BOTH ]
  [ SID = 'sid'|'*']

'sid': Specific SID to be used in altering the SPFILE, usefully in RAC environment.
'*': Uses the default SPFILE.

example:

• Changing parameter values

ALTER SYSTEM SET undo_tablespace = UNDO2;

• Specifying temporary or persistent changes

ALTER SYSTEM SET undo_tablespace = UNDO2 SCOPE=BOTH;

• Deleting or resetting values

ALTER SYSTEM SET undo_tablespace = UNDO2 SCOPE=BOTH SID=’*’;

Managing an Oracle instance,
STARTUP command behavior

• Order of precedence:
• spfileSID.ora
• Default SPFILE
• initSID.ora
• Default PFILE
• Specified PFILE can override precedence.

STARTUP PFILE = $ORACLE_HOME/dbs/initDBA1.ora

There are four modes of Oracle instance:

Starting the Instance (NOMOUNT):
An instance would be started in the NOMOUNT stage only during database creation or the re-creation of control files.

Starting an instance includes the following tasks:

• Reading the initialization file from $ORACLE_HOME/dbs in the following order:
• First spfileSID.ora
• if not found then, spfile.ora
• if not found not initSID.ora
• Allocating the SGA
• Starting the background processes
• Opening the alertSID.log file and the trace files.

Mounting the database (MOUNT):
To perform specific maintenance operations, you start an instance and mount a database but do not open the database.
For example, the database must be mounted but not open during the following tasks:

• Renaming data files
• Enabling and disabling online redo log file archiving options
• Performing full database recovery

Mounting a database includes the following tasks:

• Associating a database with a previously started instance.
• Locating and opening the control files specified in the parameter file
• Reading the control files to obtain the names and status of the data files and online redo log files. However, no checkes are performed to verify the existence of the data files and online redo log files at this time.

Opening the database (OPEN):
Normal database operation means that an instance is started and the database is mounted and open. Tyicl database access and normal database operation are valid on the database at this time.
Opening the database includes the following tasks:

• Opening the online data files
• Opening the online redo log files

Open mode is the default startup command mode to start up the instance

STARTUP

STARTUP PFILE = $ORACLE_HOME/dbs/initDBA1.ora

Startup command syntax:
STARTUP [FORCE] [RESTRICT] [PFILE=filename]
     [OPEN  [RECOVER] [database]
     |MOUNT
     |NOMOUNT ]

• FORCE: Aborts the running instance before performing a normal startup.
• RESTRICT: Enablesonly users with RESTRICTED SESSION privilegeto access the dataabase.
• RECOVER: Begins media recovery when the database starts.

Shuting Down the Database:

Shutdown Mode	A	I	T	N
Allow new connections	No	No	No	No
Wait until current sessions end	No	No	No	Yes
Wait until current transactions end	No	No	Yes	Yes
Force a checkpoint and close files	No	Yes	Yes	yes

 shutdown mode:

• A = Aboort
• I  = Immediate
• T = Transactional
• N = Normal

Shutdown Normal:
Normal is the default shut down mode. Normal database shut down proceeds with the following conditions:

• No new connection can be made
• The Oracle server waits for all users to disconnect before completing the shutdown.

Shutdown Transactional:
A transactional shutdown prevents clients from losing work. A transactional database shutdown procees with the following conditions:

• No client can start a new transactino on this particular instance.
• A client is disconnected when the client ends the transaction that is in process.
• When all transactions have finished, a shut down occurs immediately.

Shutdown Immediate:
Immediate database shut down proceeds with the following conditions:

• Current SQL statements being processed by Oracle are not completed.
• The Oracle server does not wait for the users, who are currently connected to the database, to disconnect.
• Oracle rolls back active transactions and disconnencts all connected users.
• Oracle closes and dismounts the database before shutting down the instance.

Monitoring an Instance Using Diagnostic Files:

• alertSID.log file
• Background trace files
• User trace files

Alert Log File:

• Location defined by BACKGROUND_DUMP_DEST
• Must be managed  by DBA
• Each entry has a time stamp associated with it
• alertSID.ora file:
• Records the commands
• Records results of major events
• Used to day-to-day operational information
• Used for diagnosing database erros

Background Trace Files:

• Background trace files
• Log erros detected by any background process
• Are used to diagnose and troubleshoot erros
• Created when a background process encounters an error
• Location defined by BACKGROND_DUMP_DEST

User Trace Files:

• User trace files
• Produced by the user process
• Can be generated by a server process
• Contain statistics for traced SQL statements
• Contain user error messages
• Created when a user encounters user session errors
• Location is defined by USER_DUMP_DEST
• Size defined by MAX_DUMP_FILE_SIZE

Oracle Fundamental I - Chapter 2: Started with oracle Server

Database Administration Tools

Tools	Description
Oracle Universal Installer (OUI)	Used to install, upgrade, or remove software components with GUI interface.
Oracle Database Configuration Assistant	A graphical user interface tool that interacts with the OUI, or can be used independently, to create, delete, or modify a database.
SQL *Plus	A utility to access data in an oracle database. The mostly used tool by DBA to administrating the database/instance.
DB console (Oracle Enterprise Manager in previous version as 9i)	A graphical interface used to administer, monitor, and tune one or more databases.

The Oracld Universal Installer is a Java based engine, which provides the GUI interface to administrates the installation process.

To start OUI in Unix/Linux environment,  the installation program is called runInstaller,
which is located under oracle\oui\install direcotry, you issue the following command:

$ ./runInstaller

To start OUI on NT environment, the installation program is calle Setup,
which is located under Program Files/Oracle/oui/install, you issue the following steps:

Start  >  Programes  >  Oracle Installation Products  >  Universal Installer

Oracle Database Configuration Assitant can be used for the following:
- Create a database
- Configure database options
- Delete a database
- Manage templates

When creating a new database, DBCA will prompt you for setup the administrative level privilege.
To administrates an Oracle database, you need those privilege for appropriate acceses.

• Users sys and system are created automatically
• During database creation
• Granted the DBA role (DBA role is a default bunch of privilege created duing Oracle installation to provide DBA level privilege)
• User Sys
• Owner of the database data dictionary
• Default password: change_on_install
• User System
• Owner of additional internal tables and views user by Oracle tools.
• Default password: manager

If you use DBCA to create a database, you will get the chance to modify the passwords during the creation process.


SQL*Plus is an Oracle tool, which provides:

• capability to interact with and manipulate the database, which is based on the command prompt interface. SQL and PL/SQL is the languages used by SQL*PLus.
• Ability to startup and shutdown the database, create and run queries, add/update/delete/query rows, data and write reports, etc.
• Connecting to SQL*Plus:
• By default, SQL*Plus is located under $ORACLE_HOME/bin

$ sqlplus /nolog SQL> connect / as sysdba Connected to an idle instance.

                          

Oracle DB console (Oracle Enterprise Manager in 9i or previous) is the Oracle tool based on GUI interface to administrate Oracle instance and database.

• Serves as centralized systems management tool for DBAs.
• A tool to administer, diagnose, and tune multiple databases.
• A tool to administer multiple network nodes and services from many locations.
• Use to share tasks with other administrators.
• Provices tools for administering parallel services and replicated databases.

KSH - Korn Shell Tutorial

KSH - Korn Shell Tutorial

• Matching Patterns
• Conditional Statements
• Test Objects (Files, Directories, etc.)
• Format of flow control functions
• Positional Parameter
• Redirections
• Other functionalities
• Examples
• Regular Expression
• Array

---

Matching Patterns

pattern:    example:      matches:                    not matched:

------------------------------------------------------------------

*           boo*          boot,boo,booth

?           boo?          boot                        booth

[...]       [aeiou]*      ark                         bark

[!...]      boo[!st]      boor                        boot

*(cc|cc)    boo*(ze|r)    boo,boor,booze,boozer       boot

+(cc|cc)    boo+(ze|r)    boor,booze,boozer           boo

?(cc|cc)    boo?(ze|r)    boo,boor,booze              boozer

@(cc|cc)    boo@(ze|r)    booze,booth                 boo

!(cc|cc)    boo!(ze|r)    booth,boo,boot              booze,boor

{c,c,c}     a{b,c,d}e     abe,ace,ade                 axe

---

Conditional Statements

format                           "true" if:

---------------------------------------------------

(( _num1_ == _num2_ ))           numbers equal

(( _num1_ != _num2_ ))           numbers not equal

(( _num1_ < _num2_ ))            num1 < num2

(( _num1_ > _num2_ ))            num1 > num2

(( _num1_ <= _num2_ ))           num1 <= num2

(( _num1_ >= _num2_ ))           num1 >= num2

[[ _str1_ == _str2_ ]]           strings equal

[[ _str1_ != _str2_ ]]           strings not equal

[[ _str1_ < _str2_ ]]            str1 precedes str2

[[ _str1_ > _str2_ ]]            str1 follow str2

[[ _str1_ = _pattern_ ]]         str1 = pattern

[[ _str1_ != _pattern_ ]]        str1 != pattern

[[ -z _str_ ]]                   str is null

[[ -n _str_ ]]                   str is not null

[ x=y -o k=j ]                   or in expression

[ x=y -a k=j ]                   and in expression

---

Test Objects (Files, Directories, etc.)

test "true" if:                 ksh

-----------------------------------

object exist                    -a

readable                        -r

writable                        -w

executable                      -x

non-zero length                 -s

zero length

directory                       -d

plain file                      -f

symbolic link                   -h

named pipe                      -p

block special file              -b

character special file          -c

soft link                       -L

socket                          -S

owned by me                     -O

owned by my group              not

"sticky" bit set                -k

set-group-ID bit set            -g

set-user-id bit set             -u

opened on a terminal           not

---

Format of flow control functions

"if-then"               if _expr_ then

                            _cmd(s)_

                        elif _expr_

                            _cmd(s)_

                        else

                            _cmd(s)_

                        fi

"case"                  case _word_ in

                            _pattern1_)   _cmd(s)_

                            _pattern2_)   _cmd(s)_

                            *)            break ;;

                        esac

"while"                 while _expr_ do

                            _cmd(s)_

                        done

"for"                   for _variable_ in _list_

                            _cmd(s)_

                        done

"until"                 until _expr_

                        do

                            _cmd(s)_

                        done

---

POSITIONAL PARAMETER

program, function or shell                         $0

argument 1 through 9                               $1 .. $9

nth argument                                       ${n}

number of positional parameters                    $#

every positional parameter                         $@, $*

decimal value returned by last executed cmd        $?

pid of shell                                       $$

pid of last backgrounded command                   $!

---

REDIRECTIONS

0             stdin

1             stdout

2             stderr

<&-           close stdin

>&-           close stdout

<>filename    open filename for read-write

2>&1          open 2 for write and dup as 1

Examples:

  cmd 2>/dev/null

  cmd >/dev/null 2>&1

  exec 1<&-           # close descriptor 1

  exec 2<&-           # close descriptor 2

  exec 1< /dev/null   # open descriptor 1

  exec 2< /dev/null   # open descriptor 2

 

---

OTHER FUNCTIONALITIES

cmd1 || cmd2    exec cmd2 if cmd1 fail

cmd1 && cmd2    exec cmd2 if cmd1 is OK

V1=${V2:=V3}    Set V1 with the value of V2 if this is set else set the

                variable V1 with value of V3 (V3 could be a number).

                sh replacement:  if [ $V2 ] ; then

                                                V1=$V2

                                 else

                                                V1=$V3

                Example: DisplaySize=${LINES:24} ; Command=${Command:"cat"}

${V1:?word}     if V1 set  & V1!=null   ret $V1 else print word and exit

                  : ${V1:?"variable V1 not set on null"}

${V1:=word}     if V1 !set | V1==null   set V1=$word

${V1:-word}     if V1 set  & V1!=null   ret $V1 else ret word

${V1:+word}     if V1 set  & V1!=null   ret word else ret nothing

${V1##patt}

${V1#patt}      if patt are found at the begin of V1 return V1 whitout the patt

                else return V1

                V1="lspwd" ; ${V1#"ls"}  # exec pwd

${V1%%patt}

${V1%patt}      if patt are found at the end of V1 return V1 whitout the patt

                else return V1

                V1="lspwd" ; ${V1%"pwd"}  # exec ls

---

EXAMPLES

- Explode a command for use parameters counter

    set `who -r` ; [ "$8" != "0" ] && exit

- declare a variable for only uppercase/lovercase chars

    typeset -u VAR ; VAR="lower" ; echo $VAR   -> LOWER

    typeset -l VAR ; VAR="UPPER" ; echo $VAR   -> upper

- exec

- eval - esegue il comando dato come argomento

- let - esegue le operazioni matematiche che passate come argomento

 let "x = x * 5"

 ((x = x * 5))  .. altra forma di let

---

REGULAR EXPRESSION

- ritorna la prima lettera dopo il segno - all'inizio di una stringa

    VAR="-ciao"

    RESULT=`expr "$VAR" : "-\(.\)"`

    echo $RESULT        .. -c

- toglie il '-' iniziale

    VAR="-ciao"

    VAR=`expr "$VAR" : "-*\(.*\)"`

    echo $VAR           .. ciao

- ritorna la lunghezza di una stringa

    VAR="ciao"

    echo `expr length $SHELL`      .. 4

- ritorna l'indice di dove incontra una substringa

    echo `expr index abcdef de`    .. 4

- ritorna 6 caratteri a partire dall'11

    expr substr "Goodnight Ladies" 11 6     .. Ladies

---

ARRAY

- definisce un array

 set -A Week Sat Sun Mon Tue Wed Thu Fri

- ritorna un elemento dell'array

 echo ${Week[3]}       .. Tue

 id=3 ; echo ${Week[id]}     .. Tue

- stampa tutti gli elemti di un array

 echo ${Week[@]}       .. Sat Sun Mon Tue Wed Thu Fri

- scandisce un array

    for day in ${Week[@]}

    do

        echo $day

    done

- ritorna il numero di elementi in un array

 nelem=${#Week[@]} ; echo $nelem   .. 7