Oracle OCA Video Notes

Oracle OCA video series.


Completed videos:
1.sqlplus
2.installing oracle s/w
3.database and instance architecture
4.startup and shutdown
5.Managing tables,views , constraints
6.data moving utilities
7.pl/sql basics
8.triggers,procedures & functions
9.packages.
10.Managing Undo
11.tablespaces
12.Managing users
13.Oracle Net Services
14.Shared Server
15.Oracle Security
16.Locks, latches and waits

SQLPLUS

SQL> desc tab
Name Null? Type
----------------------------------------- -------- ----------------------------
TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER

column tname format A30
column tname heading “Rakesh|Prajapati”
column tabtype like tname heading “Table Name”

To clear above formatting
clear columns (all columns clear formatting)
col tname clear (clear formatting)

sql> define (shows all variables defined)


define sets a user variable or displays its value.
SQL>define varname=foo


Bind variables
SQL> variable x varchar2(10)

&x - will prompt for value of x every time.

&&x – will prompt only once if &&x is used serveral times in the same query.

Now to reset or undefine the value of && x do
sql> undefine x


var[iable] x number

print x

prompt “Enter a value”
accept d prompt “I will now put your value in d”

update ttt
set a = 1
where x = &&d

SQL> variable deptno number
SQL> exec :deptno := 10
SQL> select * from emp where deptno = :deptno;

set pause on
set page size 35 (35 lines per page I think , try it)
login.sql and glogin.sql (glogin.sql like /etc/.profile vs login.sql $HOME/.profile)
ttitle , btitle (top title and bottom title for each report page)
help index (all sqlplus commands)
/opt/oracle/product/10gR2/oc4j/j2ee/isqlplus/config/http-web-site.xml (to change port # here in this file)





UNDO Video

active undo segments vs inactive undo segments.

Tasks:
create undo tablespace with create database
create undo tablespace with reuse
add a datafile to undo tablespace
Turn RETENTION GUARANTEE for a tablespace
Turn off RETENTION GUARANTEE for a tablespace
rename datafile for a tablespace , then try for undo tablespace.
Switch undo tablespace.
Drop undo tablespace.

When switching undo tablespaces the old tablespace remains in “pending offline” mode until the pending transaction using that undo tablespace finish.

Call undo advisor package
1 begin
2 :n := dbms_undo_adv.rbu_migration;
3* end;
SQL>
SQL> var n number
SQL> /

PL/SQL procedure successfully completed.

SQL> print n

N
----------
16

tells us how big the undo tablespace should be.

if there is only one undo tablespace in the database then undo_tablespace parameter will default to the only one that exists.

To check if there is any active undo in the database

select distinct owner , tablespace_name,bytes,status
from dba_undo_extents
where status = 'ACTIVE';





Oracle Installation

Oracle Editions:
Lite edition
Express edition
Standard edition One
Standard edition
Enterprise edition


grep MemTotal /proc/meminfo
grep SwapTotal /proc/meminfo
grep “model name” /proc/cpuinfo

check kernel parameters
sysctl -a
sysctl -p (to apply changes)

must set
ORACLE_HOME
ORACLE_SID

Tasks
Try creating several databases/instances on the same machine
and try switching between them.
Create database manually , create data dictionary manually etc.

To create response file

./runInstaller -record -destinationFile /home/oracle/database/response/rakesh.rsp

To run from response file

./runInstaller -silent -responseFile /home/oracle/database/response/rakesh.rsp

./runInstaller -help

create database in nomount stage will automatically open the database when the create database is complete.

-windows – oradim utility to run before creating database manually.







Database and Instance Architecture

Tasks
Make a table use non standard block size (need to configure its sized cache etc)
Add log file group , log file members
rename log file members etc
drop groups , log group members.

datafiles (*.dbf)

dba_data_files
v$datafiles (creation time etc)

log files (*.log)

v$log; (log sequence , status , archived? etc)
v$logfile;


control files (*.ctl)

v$controlfile


logical structures:

Temp segments can exist both in temp tablespace as well as individual tablespaces. 29:47 in the video.

pmon starts the server process.
Server process reads the datafile?

Startup and Shutdown database:

users/ authentication needed to startup and shutdown database
os authentication , oracle user or user in dba group
SYSDBA privilege
SYSOPER privilege

startup restricted
alter system disable restricted session

select logins from v$instance;
logins will show “RESTRICTED” for restricted session and
“ALLOWED” for normal session when restricted is disabled

alter system suspend;
select database_status from v$instance;
shows “SUSPENDED”
alter system resume.
The database_status shows ACTIVE.


issys_modifiable values are ('DEFERRED','IMMEDIATE','FALSE)

v$spparameter.update_comment shows comment specified during alter system eg.
Alter system set background_dump_dest=' ' comment='modified by Rakesh'

create pfile='path' from spfile;

when creating spfile from pfile , make sure that the database is down when u do it , acording to nuggets video. (pos 18:42 in video)

create spfile='path/name' from pfile='path/name';


v$parameter , v$parameter2 (shows instance related)
vs
v$system_parameter , v$system_parameter2 (shows system related)
multiple value parameters are displayed better in the 2 counterpart views.
Eg of system parameter vs session parameter.

Alter session set statistics_level=basic – will show up in v$parameter as 'BASIC'
whereas in v$system_parameter it will still show as 'TYPICAL'

v$parameter2.ismodified values can be
'MODIFIED' - modified at session level
'SYSMODIFIED' – modified at system level
'FALSE' – not modied at system or session level.

v$parameter2.isadjusted – whether oracle itself adjusted the parameter.

v$spparameter – shows parameter from spfile.

v$parameter_valid_values – shows all valid values for parameters.

v$obsolete_parameter


TASKS

create pk for a table with specifying the index name and also the tablespace
during create table
during alter table

create table orders (
order_id number,
order_dt date,
constraint pk_orders primary key(order_id) using index tablespace neetu
);


default tablespace can be found in
user_users or dba_users view.

…..
Foreign key (username) references person(username)
deferrable initially deferrable

other choices deferrable initially immediate


alter session set constraint = deferred;

set constraint constraint_name deferred;



Data Moving Utilities

exp_full_database role;

export and import can be useful to remove fragmentation by exporting and then importing the tables back.

Export command has options for
parfile (parameter file)
index=n
log=logfile
file=exp1.dmp,exp2.dmp
tables=neetu,rakesh
filesize=4096 (multiple files split, specify enough files for file parameter or else will be prompted at the prompt) etc....
log=logfile.log (for log file)
tables=table1,table2
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE

imp_full_database role


dba_datapump_jobs



PL/SQL

PL – Procedural Language

Q)What is anchor datatype?
A) eg. Eid employee.id%TYPE;

USER_SOURCE is the table/view that contains all the source code for procedure/function/packages;


to execute procedure

exec procedure;
or
begin
procedure;
end;
/

scalar
string , binary_integer, integer
composite data types
tables (nested), varray, record (eg %rowtype)
reference (ref cursor?)

Q) how to declare nested tables?
A) actual code below.
DECLARE
type num_array_type is table of number;
eids num_array_type;
BEGIN
eids := num_array_type(); – constructor
for x in 1..5 loop
eids.extend; – eids.extend(5) would mean declare array with 5 elements else eids.extend is same as – eids.extend(1) I think
eids(x) := x * 10;
dbms_output....
end loop;
end;

property of table is eids.count – study it and other properties.

varray vs table type (nested table)
varray exists for backward compatibility
varray has to declare size in advance unlike table type.


To hide the implementation/source of a procedure/function
use $ORACLE_HOM/bin/wrap -iname=.sql file which has the complete source code
and it creates a .plb file which can be shipped to the client etc.

SQL> @wrapped_file_name.plb;



To have the ability to create DDL triggers one must have
“administer database trigger” privilege.
Eg
connect / as sysdba
grant administer database trigger to hr;


ORA_DICT_OBJ_NAME – name of the object
ORA_DICT_OBJ_OWNER – owner of the object.

Can be used in DDL triggers
create trigger....
AFTER DROP on {DATABASE|SCHEMA|???}
rest of trigger code.

above 2 functions just like USER.


sys_context('USERENV','host') - gives the OS or user env from where the host is connecting.

SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.

USERENV returns information about the current session.

AFTER LOGON on DATABASE – trigger specification type



Packages

STANDARD package is the default/ implicit package

eg select round(9.2333,2) from dual
is same as
select sys.standard.round(9.2333,2) from dual

desc sys.standard
desc sys.dbms_output

exec dbms_stats.gather_table_stats('SCOTT','PERSON')
gather_schema_stats
gather_database_stats for sysdba's


user_tables.last_analyzed.

dbms_scheduler ~ cron job

to schedule job need “CREATE JOB” privelige.

dbms_scheduler.create_job(..,..,..)


dba_objects.status ('VALID' )

dbms_utility.compile_schema('SCOTT');


Managing Undo

The old style rollback segments for a table would exist in the very tablespace that the table exists.

UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=UNDOTBSe need not be specified if there's only one tablespace that exists.
UNDO_RETENTION=600 - defaults to 900
RETENTION_GUARANTEE (10.2 or later) – specified without any seconds/argument, its a specification done during create database undo tablespace clause or in the create tablesPACE clause.

Alter tablESPACE undotbs retention [no]guarantee

Alter database rename file ' ' to ' ' – done in 'MOUNT' mode.

Pending offline mode – an undo tableSPACE remains in this state when an undo tablespace is switched
and the old one still has transactions running.

dba_tablespaces.contents has values like 'PERMANENT','UNDO' indicating the type.

Active Vs Inactive Undo

Any transaction before commit is performed has Active undo so that rollback is possible
once the commit is done it becomes inactive undo.
The Inactive undo might still be necessary for read consistency or even flashback queries.

Inactive Undo is reused in FIFO method.


dbms_undo_adv.rbu_migration returns a number saying how large the tabLESPACE should be to migrate a rollback segment to an undo tablespace.


How to find out if there is any active undo in the tablespace?
Select distinct tableSPACE_name,owner,BYTES,status
from dba_undo_extents
where status = 'ACTIVE';

cannot drop undo tablespace until status is EXPIRED from above table (dba_undo_extents).




Tablespace

Unlimited Tablespace ROLE
dba_ts_quotas – shows quotas on tablespaces for users. (max_bytes of -1 means unlimited)
alter user scott quota 5m on users;
resetting quota does not effect existing objects , just the future ones.
dba_sys_privs to see what privs a user has.

How to convert dictionary managed tablespaces to locally managed?
First convert non system tablespaces to locally managed and then the system tablespace.
There is a system utility/package which can help us do that.
Exec dbms_space_admin.tablespace_migrate_to_local('USERS');

bigfile tablespace can have upto 4G of blocks and if the block size is 32 then the tablespace can be of size 32 * 4G bytes.


alter database default temporary tablespace temp2;

select * from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE'
gives the default temp tablespace for the database;
to override for a user

alter user scott temporary tablespace temp1;

select * from dba_users
where username = 'SCOTT' – will give that the default temporary tablespace is.

Select temporary_tablespace from dba_users;

dba_temp_files
dba_data_files


Temporary tablespaces

alter tablespace temp tablespace group rakesh_temp_tablespace_group;
alter tablespace temp1 tablespace group rakesh_temp_tablespace_group;

alter user scott temp tablespace rakesh_temp_tablespace_group;

To remove a tablespace from a temp tablespace group

alter tablespace temp1 tablespace group ''

select * from dba_tablespace_groups;

alter database default temporary tablespace rakesh_temp_tablespace_group;

create tablespace …
…
[compress]

compress improves performance , usually used in DW environments
where small number of large tables, what it does is it eliminates repeating values of data in columns.


Creating non default block sized tablespace

alter system set db_16k_cache_size = 10M;

create tablespace ..
…
block size 16k;


Rename tablespaces;

alter tablespace old_one rename to new_one;

drop tablespace name1 [ including contents and datafiles ]

if tablespace has objects then must specify including clause

renaming datafiles?

Have the database in mount mode or
take the tablespace offline , do the modification and then bring it online.

Alter tablespace users offline;

go to os and rename the datafile

then do
alter database rename file ` ` to ` `;

then
bring back tablespace online;


temp tablespace is automatically set to nologging;


Managing Users


no default tablespace or default temp tablespace then
the user will be defaulted to SYSTEM.

Only one profile can be assigned to user and if none specified then defaults to DEFAULT profile.

In a profile we have
kernel limitations
password limitations

select * from dba_profiles
where profile = 'DEFAULT'

alter user scott profile profile1;

password_verify_function is the name of the resource parameter which can be set to verify_function() from below.

$ORACLE_HOME/rdbms/admin/utlpwdmg.sql contains verify_function()






SQL> select * from resource_cost;

RESOURCE_NAME UNIT_COST
-------------------------------- ----------
CPU_PER_SESSION 0
LOGICAL_READS_PER_SESSION 0
CONNECT_TIME 0
PRIVATE_SGA 0

The above comprise the composite limit resource parameter.




Problem:

Alter resource_cost
set cpu_per_session 8, – hundreths of a second.
connect_time 5;


User connected for 30 minutes and has used 300 seconds of cpu time
(30 * 5) + (30000 * 8) = 2550

so 2550 is composite time. If composite limit is set to 2500 then this session will terminate.

Roles : connect, resource

connect role just has connect session privilege
connect resource has :

CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE


killing sessions:

select sid,serial#,osuser,username,,logon_time
from v$session;

alter session
kill session `sid,serial#' ;

Auditing

alter system set audit_trail=db scope=spfile; --- DB | OS
DB means audit trail info stored in the DB vs OS
alter system set audit_file_dest='/path' scope=spfile;

DBA_STMT_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS

above views tell us which things we are auditing.

[no]audit create session
[no]audit create table by scott whenever unsuccessful;

select * from dba_audit_session;

dba_session_privs



Oracle Net Services

$TNS_ADMIN can point to whatever dir the tnsnames.ora files exist if the default $ORACLE_HOME/network/admin needs to be overridden

listener.ora is used to configure the listener.

Sqlnet.ora purpose? Authentication methods, available naming methods. Etc

multiple listeners on the same host should have
different names
different ports

In RAC environments
one listener for each instance.


Listener.ora has in the tutorial example

SID_LIST_LISTENER=
(
…
..
) – shows what oracle sids the listener is listening for. Ie listener can be listening for multiple databases.

LISTENER=
(
– specifies protocol like TCP (1521 port) , TCPS (2484 port)
)
The order of protocols determines what the listener will try first.

Running listener configuration via netca automatically stops and starts the listener.


Changing listener password for security , so that not everybody can start and stop the listener.
Process is to
1)change password
2)set password
3)save the configuration

Lsnrctl> change_password
old_password: press enter when no earlier password exists.
New_password: key in new password.


Listener can allow/deny access to particular hosts. This setting is done in sqlnet.ora
TCP.VALIDNODE_CHECKING =YES turns on this feature. Put in sqlnet.ora
TCP.INVITED_NODES = (mycompany,192.168.0.200)
TCP.EXCLUDED_NODES = (,)
above 2 are mutually exclusive.

In sqlnet.ora

NAMES.DIRECTORY_PATH= (EZCONNECT,TNSNAMES)
The above entry comes after local naming configuration in netca.

Debugging:
if tnsping does not work then try ping , if ping hostname does not work then try ping ip , if that does not work then try ping www.google.com etc


Debugging the listener via log files or trace files.

lsnrctl> set trc_level support

setting listener log file
lsnrctl> set log_directory path
lsnrctl> set log_file filename
lsnrctl> show log_directory
lsnctl> save_config to save permanently the above changes.

Distributed query

set ORACLE_SID=orcl

sqlplus / as sysdba

select name from v$database – will show the side connected to

dba_db_link;



Shared server architecture

One request queue but multiple response queue for each dispatcher.

Can configure
number of dispatchers
max no of dispatchers
initial and max no of server processes
how large the request queue can get

need to calculate
the size of memory structures
number of user processes

UGA – Information about a user process (User Global Area)

In dedicated server configuration the user process information/UGA is stored in server processes's PGA

In shared server configuration the user process info/UGA or info about session is stored in virtual circuit

virtual circuit is in the SGA and its a piece of memory accessible to all shared server processes.

Shared Server Paramaters

Setting shared_servers parameter to non zero value will enable shared server mode configuration.
Dispatchers
max_dispatchers
max_shared_servers
circuits

v$sesstat
v$statname
v$open _cursor – open cursors per session

how to configure LARGE_POOL (its complex calculation) check video at about 70 % position.
Also finds the size of UGA.

Tnsnames.ora has
server = shared or
server = dedicated

monitoring shared servers
v$shared_server
v$dispatcher
v$queue



Oracle Security


v$pwfile_users

Fine Grained Auditing

to enable it have execute priv on package dbms_fga;

exec dbms_fga.add_policy('SCOTT','EMP','high_salary','SAL > 2000');
...drop_policy;
enable_policy;
disable_policy;


high_salary is the policy name. Anytime a query on scott.emp returns rows where sal > 2000 then
the query and user details will go into audit trail table dba_fga_audit_trail.


Locks , Latches and Waits

latches: will only allow exclusive access, not even read access allowed. Latches for about a second or less. (spinning and sleeping )
locks: will allow shared access , even exclusive access allows reading.

Latches are for memory structures


Simple objects: eg. Buffer cache.
Compound objects: eg table , sub object within a table is rows.


dba_locks
dba_dml_locks
dba_ddl_locks
dba_blockers
dba_waiters

Oracle does not have locking manager unlike other RDBMS. Oracle handles locking at database block level.

Inside a data block oracle keeps track of transactions using ITL (Interested Transaction List)