Pragmas

PRAGMAs are processed at compile time, not at run time.

source for objects

user_views for views

user_source for
- procedure , function , package , package body, type

Packages

When a PL/SQL-packaged construct is referenced for the first time, the whole package is loaded
into memory.
You can code and compile a specification without its body.
The package specification may also include PRAGMAs, which are directives to the compiler.
If a package specification does not contain subprogram declarations, then there is no
requirement for a package body.
Variables declared in the package specification are initialized to NULL by default.

definition of package level data: variable,cursor etc declared either in spec or body
but outside the procedure/function.

Package level data is visible only during the user session but across transactions. Eg an open
cursor either defined in package body or spec will remain open for the whole
session and different programs (proc/functions) can access the open cursor. You could open in one program , fetch from second and close in third.

use DBMS_PIPE or Oracle Advanced Queueing to share data across sessions.


Place items in the declaration part of the package body
when you must maintain them throughout
a session or across transactions.
The fine-grain dependency management reduces the
need to recompile referencing subprograms when a
package specification changes.

package variables good example http://www.dba-oracle.com/plsql/t_plsql_global_data.htm

Visibility of Package Components
The visibility of a component describes whether that component can be seen, that is, referenced
and used by other components or objects. The visibility of components depends on whether they
are locally or globally declared.
Local components are visible within the structure in which they are declared, such as:
• Variables defined in a subprogram can be referenced within that subprogram, and are not
visible to external components—for example, local_var can be used in procedure A.
• Private package variables, which are declared in a package body, can be referenced by other
components in the same package body. They are not visible to any subprograms or objects
that are outside the package. For example, private_var can be used by procedures A
and B within the package body, but not outside the package.
Globally declared components are visible internally and externally to the package, such as:
• A public variable, which is declared in a package specification, can be referenced and
changed outside the package (for example, public_var can be referenced externally).
• A package subprogram in the specification can be called from external code sources (for
example, procedure A can be called from an environment external to the package).


Overloading Subprograms
Restrictions
You cannot overload:
• Two subprograms if their formal parameters differ only in data type and the different data
types are in the same family (NUMBER and DECIMAL belong to the same family.)
• Two subprograms if their formal parameters differ only in subtype and the different
subtypes are based on types in the same family (VARCHAR and STRING are PL/SQL
subtypes of VARCHAR2.)
• Two functions that differ only in return type, even if the types are in different families

What are possible side effects of using a stored function in a sql query (related to purity level)
It is important to control side effects because they can prevent the proper parallelization of a
query, produce order-dependent and therefore indeterminate results, or require impermissible
actions such as the maintenance of package state across user sessions.

Add PRAGMA SERIALLY_RESUABLE to the package
specification, then the database stores package variables in the System Global Area (SGA)
shared across user sessions. In this case, the package state is maintained for the life of a
subprogram call or a single reference to a package construct. The SERIALLY_REUSABLE
directive is useful if you want to conserve memory and if the package state does not need to
persist for each user session.

This PRAGMA is appropriate for packages that declare large temporary work areas that are used
once and not needed during subsequent database calls in the same session.
You can mark a bodiless package as serially reusable. If a package has a spec and body, you
must mark both. You cannot mark only the body.
The global memory for serially reusable packages is pooled in the System Global Area (SGA),
not allocated to individual users in the User Global Area (UGA). That way, the package work
area can be reused. When the call to the server ends, the memory is returned to the pool. Each
time the package is reused, its public variables are initialized to their default values or to NULL.
Note: Serially reusable packages cannot be accessed from database triggers or other PL/SQL
subprograms that are called from SQL statements. If you try, the Oracle server generates an
error.

Initializing Packages
1) The block at the end of the package body executes once
and is used to initialize public and private package
variables.
2) Also any package level data (such as number variable or string constant).
package level data are of 2 types , public ie declared in the package spec outside of any subprogram
and private ie declared in the package body outside any stored proc/function.
3) Also default values to variable and constants as specified in their declarations

The first time the package is used/referenced the package data (above 3) is initialized.

Functions

functions
- deterministic
- parallel_enable
- authid
- pipelined
- result_cache
- dml inside functions

good examples here http://blogs.oracle.com/sysdba/2009/07/workaround_the_dmls_plsql_func.html

SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
NAME VARCHAR2(10)


SQL>
1 create or replace function test_fn(a number)
2 return number
3 is
4 begin
5 insert into test(a) values (a);
6 return 100;
7* end;
SQL> /

Function created.

SQL> select test_fn(20) from dual;
select test_fn(20) from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "NEETU.TEST_FN", line 5


SQL> insert into test(a) values (20);

1 row created.

SQL> commit;

Commit complete.

SQL> update test set a = test_fn(2000)
2 where a = 100;

0 rows updated.

SQL> select * from test;

A NAME
---------- ----------
20

SQL> update test set a = test_fn(2000)
2 where a = 20;
update test set a = test_fn(2000)
*
ERROR at line 1:
ORA-04091: table NEETU.TEST is mutating, trigger/function may not see it
ORA-06512: at "NEETU.TEST_FN", line 5


Also cannot perform a commit / rollback inside a function.




- function can be used in
pl/sql expression, sql query in where,having, connect by , start with , order by
values of insert and set of update

Restrictions When Calling Functions
from SQL Expressions
• User-defined functions that are callable from SQL
expressions must:
– Be stored in the database
– Accept only IN parameters with valid SQL data types, not
PL/SQL-specific types
– Return valid SQL data types, not PL/SQL-specific types
• When calling functions in SQL statements:
– Parameters must be specified with positional notation
– You must own the function or have the EXECUTE privilege

The following restrictions apply when calling a function in a SQL statement:

• Parameters must use positional notation. Named notation is not supported. (This is not true in 11g. In 11g can use mixed notation)
• You must own or have the EXECUTE privilege on the function.

Other restrictions on a user-defined function include the following:
• It cannot be called from the CHECK constraint clause of a CREATE TABLE or ALTER TABLE statement.
• It cannot be used to specify a default value for a column.


Controlling Side Effects When
Calling Functions from SQL Expressions
Functions called from:
• A SELECT statement cannot contain DML statements
• An UPDATE or DELETE statement on a table T cannot
query or contain DML on the same table T
• SQL statements cannot end transactions (that is,
cannot execute COMMIT or ROLLBACK operations)
- A session control statement (such as SET ROLE)
- A system control statement (such as ALTER SYSTEM)

PL/SQL Notes

The PL/SQL engine resides in:
r
• The Oracle database for executing stored subprograms

• The Oracle Forms client when running client/server applications, or in the Oracle

Application Server when using Oracle Forms Services to run Forms on the Web

I
The PL/SQL engine is a virtual machine that resides in memory and processes the PL/SQL
m-code instructions. When the PL/SQL engine encounters a SQL statement, a context switch is
made to pass the SQL statement to the Oracle server processes. The PL/SQL engine waits for the
SQL statement to complete and for the results to be returned before it continues to process
subsequent statements in the PL/SQL block.

The Oracle Forms PL/SQL engine runs in the client for the client/server implementation, and in
the application server for the Forms Services implementation. In either case, SQL statements are
typically sent over a network to an Oracle server for processing.


A procedure is compiled and stored in the database as a schema object. If you are using the
procedures with Oracle Forms and Reports, then they can be compiled within the Oracle Forms
or Oracle Reports executables.

compiled procedure is stored as m-code in the database;
if errors exist then m-code is not stored until all errors are fixed.


The OUT parameter in a Procedure must be actually passed as a variable (actual parameter).
It cannot be assigned a default value. It is an uninitialized variable.
IN OUT parameter also cannot be assigned a default value. I must also be a variable being passed
so that it can take the value back to the caller. It is an initialized variable.

The IN parameter is passed by reference and IN OUT or OUT are passed by value.
passed by value means that a copy is made of the actual parameter to the formal parameter
and the procedure only modifies the local copy until the procedure exits successfully
then the local modified copy is copied to the actual parameter.
If a large collection is passed as IN OUT then it requires double the memory resources
because it copies the collection and makes a local copy in the procedure. To avoid this
declare IN OUT as NOCOPY
eg procedure test (abc IN OUT NOCOPY integer)
source : http://www.dba-oracle.com/t_pl_sql_nocopy_data_structures.htm

EXCEPTION
If not handled DML is rolled back in that block/procedure and if the caller block/procedure also does not handle the exception its DML is also rolled back.


Functions
You cannot reference host or bind variables in the PL/SQL block of a stored function.

Note: Although the OUT and IN OUT parameter modes can be used with functions, it is not good

programming practice to use them with functions. However, if you need to return more than one

value from a function, consider returning the values in a composite data structure such as a

PL/SQL record or a PL/SQL table.
If IN OUT parameters are used in a function then that function cannot be part of an sql statement
it can be executed as
execute :x := fn(:param);
but not select fn(:param) from dual;

Procedures Functions
Execute as a PL/SQL statement Invoke as part of an expression
Do not contain RETURN clause in Must contain a RETURN clause in the
the header header
Can pass values (if any) using Must return a single value
output parameters
Can contain a RETURN statement Must contain at least one RETURN
without a value statement




CURSOR

When you open a cursor, PL/SQL executes the query for that cursor. It also identifies
the active set of data—that is, the rows from all involved tables that meet the criteria
in the WHERE clause and join conditions. The OPEN does not actually retrieve any of
these rows; that action is performed by the FETCH statement.

Regardless of when you perform the first fetch, however, the read consistency model
in the Oracle database guarantees that all fetches will reflect the data as it existed when
the cursor was opened. In other words, from the moment you open your cursor until
the moment that cursor is closed, all data fetched through the cursor will ignore any
inserts, updates, and deletes performed by any active sessions after the cursor was
opened.

TRIGGERS

Trigger types
- Before
- After
- Row level
- Statement level

Triggers can be for
-DML statements
-DDL statements
-Database events
-INSTEAD of
-Suspended Statements


Row level triggers will give mutating table error even if u read from the tigger table, updating is of course not allowed. This is not the case with Statement level trigger. Statement level trigger can both read and modify the triggering table.

Triggers can have commit within them if used as a part of AUTONOMOUS TRANSACTION.


11g has now compound triggers ie mix both row level and statement level triggers in the same trigger. The keyword is COMPOUND TRIGGER
To distinguish between statement level code and row level code
use
BEFORE STATEMENT
BEFORE EACH ROW
AFTER EACH ROW
AFTER STATEMENT

Triggers can have a max size of 32K but can call stored procedures or functions.

If a trigger raises an error then that part of transaction is rolled back.

can combine update/insert/delete in the same trigger like
AFTER INSERT or UPDATE or DELETE or MERGE -- statement level trigger
on ORDERS
[FOR EACH ROW] -- row level trigger


- REFERENCING old as prior

- INSERTING
- UPDATING
- DELETING
eg IF INSERTING THEN

- WHEN clause of a trigger is part of trigger header and controls if the trigger should be executed at all based on the condition specified.
- WHEN Vs IF -- IF is used within the trigger body when the trigger is being executed. IF is used to code logic within the trigger. eg IF INSERTING

- FOLLOWS another_trigger_name -- used to specify the trigger order

Perl recursion , decimal to binary

#!/usr/bin/perl

print &num2bin(10),"\n";


sub num2bin {
my $n = shift;
return $n if $n == 0 || $n == 1;

my $div = int($n / 2);
my $rem = $n % 2;
my $E = num2bin($div);

return $E . $rem;
}


the order of $E . $rem is very important.


Look at the code below. It prints in reverse. Not an elegant solution , incorrect solution rather. ohh its an infinite loop.

The above code has exit condition (base case) in the beginning and the one below has in the end.
The above code prints the bit in the code itself without using a return value.
order of $rem and $div also needs to be reversed.
The wrong code below highlights the importance of breaking down the problem into simpler sub units, and thats called recursion.

sub num2bin_reverse {
my $n = shift;

my $rem = $n % 2;
my $div = int($n / 2);
print $rem;
num2bin_reverse($div) if ($div > 0);
}

books / material to read in order

Oracle Student guides

1. PL/SQL Vol 1
2. PL/SQL Vol 2
3. PL/SQL Additional Practices
4. Advanced PL/SQL
5. SQL Fundamentals I
6. SQL Fundamentals II
   
7. SQL Tuning Guide
8. SQL Tuning Workshop
9. SQL Tuning Workshop - Practices and Solutions (Appendix A)

Random Notes

• Run profload.sql as sys and proftab.sql as the profile user to install DBMS_PROFILER to measure execution time of a pl/sql program unit.
• Oracle implicitly converts the BLOB data type to RAW

DBMS_OUTPUT to log progress or debugging messages are :

1. Each line is limited to 255 bytes
   
2. Output is limited to 1,000,000 bytes maximum
3. The output only appears at the end of the script being run

UTL_FILE.PUTF to print formatted output to a file without newline formatting.


Which one of the following built-in packages do you use to work around the previous 1,000,000 hard limit on the buffer size of DBMS_OUTPUT for any session when directly writing output from PL/SQL?

• UTL_FILE

SQL> desc user_object_size
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
TYPE VARCHAR2(18)
SOURCE_SIZE NUMBER
PARSED_SIZE NUMBER
CODE_SIZE NUMBER
ERROR_SIZE NUMBER

SOURCE_SIZE

Description of USER_OBJECT_SIZE.SOURCE_SIZE: "Size of the source, in bytes. Must be in memory during compilation, or dynamic recompilation"

PARSED_SIZE

Description of USER_OBJECT_SIZE.PARSED_SIZE: "Size of the parsed form of the object, in bytes. Must be in memory when an object is being compiled that references this object"

CODE_SIZE

Description of USER_OBJECT_SIZE.CODE_SIZE: "Code size, in bytes. Must be in memory when this object is executing"

ERROR_SIZE

Description of USER_OBJECT_SIZE.ERROR_SIZE: "Size of error messages, in bytes. In memory during the compilation of the object when there are compilation errors"


Use \1 \2 \3 .... for backreferences in regex

set serveroutput on format wrapped
the above sqlplus command will enable to print blank lines as given below.
begin
dbms_output.put_line('Line 1');
dbms_output.put_line('');
dbms_output.put_line('Line 3');
end;
/

PL/SQL Fundamentals test

Test:	Oracle PL/SQL Fundamentals
Date:	19-Feb-2010
Score:	3.68
Weights:	100% Oracle PL/SQL Fundamentals
Elapsed time:	38 min 5 sec

Oracle PL/SQL Fundamentals
Score:	3.68
Percentile:	Scored higher than 86% of previous examinees
Demonstrates a clear understanding of many advanced concepts within this topic. Appears capable of mentoring others on most projects in this area.
Strong Areas	SQL Operations Program Structure Data Manipulation Tuning
Weak Areas	Input and Output Packages Debugging

Oracle developer interview questions

1. Name few dynamic performance views and their uses
2. What would be typical values of "status" column in v$session
3. feature wise comparison of Oracle 9i/10g/11g versions pl/sql related (developer)
   

Good Developer/DBA Oracle Skills to have

1. 2 plus years as a DB Performance Analyst and/or SQL Tuner
2. Proven expertise in tuning Oracle 9i/10g for high transaction, multi-TB sized Databases
3. Proven expertise in optimization of Oracle SQL Queries
4. Strong skills in troubleshooting user specific SQL queries and recommending solutions
5. Sound knowledge of business application development and support
6. well versed in Test Data Obfuscation (data masking), specifically the Data Masking Module
   
7. oracle xml db
8. advanced queing
   

PL/SQL related interview questions

1. difference between procedure and function
2. why packages are used
3. You are the tech lead and you need to suggest some code changes to a team member , how will u do it. Will you do it yourself or assign it to them
4. How do u do a code release
5. how will you suggest design changes to database architect if you think your design is better
6. rate yourself 1 to 5 in shell scripting skills
7. asks about education , overall experience
8. give an example of challenge faced in development project
9. error handling in oracle pl/sql
10. how will u tune a slow performing query
    
11. have u used pragma's and where in what scenario
12. how to run a sql query in lets say 10 instances running on the same machine (using shell scripting). How to check if the instance is up from command line.
    

Cognos 8 BI

IBM Cognos 8 BI

• Report Studio (Professional report authoring tool formatted for the web)
• Query Studio (Ad hoc report authoring tool with instant data preview)
• Analysis Studio (Explore multi-dimensional cube data to answer business questions)
• Metric Studio (Monitor, analyze, and report on KPIs)
• Metric Designer (Define, load, and maintain metrics to be available in Metric Studio)
• Event Studio (Action based agents to notify decision makers as events happen)
• Framework Manager (Semantic metadata layer tool which creates models or packages)
• PowerPlay Studio (formerly PowerPlay Web)

source: http://en.wikipedia.org/wiki/Cognos

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)

My Dimensions and Facts

F_PRJ_TRAN
F_PC_DIST
F_LEDGER
F_JOURNAL
F_PF_JRNL_MGMT
F_PRJ_ACTIVITY

D_DEPT
D_CUSTOMER
D_BANK
D_CUSTOMER_ADDRESS
D_COUNTRY
D_COLLECTOR
D_PAYMENT
D_DEPOSIT
D_ADV_PMNT
D_DISTPUTE
D_DISPUTE_HIST

-- Some dimensions above may not be dimensions. This is what I recollect from my previous job.