Samba

Samba gui : system-config-samba

/sbin/service smb start

.screenrc

$ cat .screenrc
# Status at the bottom
hardstatus alwayslastline
hardstatus string ‘%{= kG}[ %{G}%H %{g}][%= %{= kw}%?%-Lw%?%{r}(%{W}%n*%f%t%?(%u)%?%{r})%{w}%?%+Lw%?%?%= %{g}][%{B} %D %Y-%m-%d %c:%s %{g}]‘

OpenBSD 4.7 - network interfaces

steps

rebooted laptop with just wireless switch on

iwn0 wireless interface only
$ netstat -rn
Routing tables

Internet:
Destination        Gateway            Flags   Refs      Use   Mtu  Prio Iface
default            192.168.1.1        UGS        0        4     -    12 iwn0
127/8              127.0.0.1          UGRS       0        0 33200     8 lo0
127.0.0.1          127.0.0.1          UH         2        0 33200     4 lo0
192.168.1/24       link#2             UC         1        0     -     4 iwn0
192.168.1.1        00:25:9c:4d:b5:46  UHLc       1        0     -     4 iwn0
192.168.1.25       127.0.0.1          UGHS       0        0 33200     8 lo0
224/4              127.0.0.1          URS        0        0 33200     8 lo0

plugged in ethernet cable without doing a "sh /etc/netstart"

both iwn0 and msk0
$ netstat -rn
Routing tables

Internet:
Destination        Gateway            Flags   Refs      Use   Mtu  Prio Iface
default            192.168.1.1        UGS        1        1     -     8 msk0
default            192.168.1.1        UGS       18     2262     -    12 iwn0
127/8              127.0.0.1          UGRS       0        0 33200     8 lo0
127.0.0.1          127.0.0.1          UH         3        0 33200     4 lo0
192.168.1/24       link#2             UC         1        0     -     4 iwn0
192.168.1.1        00:25:9c:4d:b5:46  UHLc       2        0     -     4 iwn0
192.168.1.24       127.0.0.1          UGHS       0        0 33200     8 lo0
192.168.1.25       127.0.0.1          UGHS       0        0 33200     8 lo0
224/4              127.0.0.1          URS        0        0 33200     8 lo0

Oracle PL/SQL - Pipelined functions

Pipelined functions are

1) simplicity, elegance of PL/SQL
+ SQL Performance
= PipeLined functions

2) row based solutions to set based solutions


to be continued.

DW keywords

Conformed dimensions
conformed facts
slowly changing dimensions
factless fact tables

oracle dblink howto connect between 2 hosts

1) setup a service using netmgr on machine where dblink will be created.
in the NET service name put in cert11
in the service name put cert11.xps8100***.org
there are other screens like "hostname" , "protocol".
you should be able to test connection right here in the last screen.


2) create public database link cert11 connect to user identified by password using 'cert11'


The dblink should work now.

Centos 5, Oracle 11.1 error - sqlplus

[oracle@localhost ~]$ sqlplus
sqlplus: error while loading shared libraries: /opt/app/oracle/product/11.1.0/db_1/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied



open /etc/selinux/config
change
SELINUX=enforcing -> SELINUX=permissive

reboot

CentOS 5 making flash + wireless work

source : http://www.yqed.com/install-flash-player-10-centos-64-bits/

# yum install curl compat-libstdc++-33 glibc nspluginwrapper

# rpm -ivh /tmp/flash-plugin-10.0.12.36-release.i386.rpm
or install the plugin from adobe site.

type: about:plugins in firefox address bar

if things still dont work then locate libflashplayer.so file and then
cp libflashplayer.so $HOME/.mozilla/plugins


Wireless Installation

source : http://wiki.centos.org/HowTos/Laptops/Wireless

download iwl4965-firmware-228.57.2.23-1.el5.rf.noarch.rpm from net (google it)

rpm -Uhv iwl4965-firmware-228.57.2.23-1.el5.rf.noarch.rpm

modprobe -r iwlagn; modprobe iwlagn


Start the network manager
source : http://wiki.centos.org/HowTos/Laptops/NetworkManager

/sbin/chkconfig NetworkManager on


/sbin/service NetworkManager start


/sbin/chkconfig network off
/sbin/chkconfig wpa_supplicant off

On the up right corner you will now see a newtworking icon, right click on the icon and setup your wireless.

Vista Partimage backup

Taken a Vista backup using "partimage" (System rescue cd)
on 500 gb ext drive.
read the readme.txt file.

+

mbr backup

System Rescue CD options

net-setup eth0
/etc/init.d/sshd start

mkdir /mnt/mydir
ntfs-3g /dev/sda1 /mnt/windows

Xwindows

startx or wizard


Partition manager
gparted

Partition Image
partimage

ksh version

what `which ksh` | grep Version

wireless access on OpenBSD 4.6

source : http://www.daemonforums.org/showthread.php?p=25477

as per "man iwn"

wget http://damien.bergamini.free.fr/packages/openbsd/iwn-firmware-5.3.tgz

then as root
pkg_add -v iwn-firmware-5.3.tgz

then create files as follows.



cat /etc/hostname.iwn0
dhcp NONE NONE NONE nwid reiki wpa wpapsk \
0x0280836cc9b8f6d8524f36001e970f133a054e2e664604604d4df20ac1716384


cat /etc/mygate
192.168.1.1

CentOS vs Ubuntu

This is my personal experience. I had an issue with wireless networking on my laptop when i installed CentOS.

Basically Wireless works fine with Ubuntu right of the bat. CentOS requires some configuration. I did not try very hard to fix the problem with CentOS though.

As of now I prefer ubuntu for this reason and also I have more experience with Ubuntu. CentOS was my first install. I like it but wireless not working out of the box was a turnoff.

I would prefer to install CentOS on a server with wired networking.

concatenating multiple rows into a single string

--String Aggregation
--source : http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

1* select wm_concat(ename) employees from emp
SQL> /

EMPLOYEES
----------------------------------------------------------------------------------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER


SQL> select collect(ename) from emp;

COLLECT(ENAME)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSTPhHx4WvmCf/fgQAB/AQFNoQ==('SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER')

Oracle Hints

select /*+ first_rows(10) */ customer_id,c.first_name,last_name,city,country,order_total
from customer c
natural join orders o
natural join order_items;


returns immediately. There are millions of rows in orders and order_items tables.

SQL> select count(*) from orders;

COUNT(*)
----------
107508853

SQL> select count(*) from order_items;

COUNT(*)
----------
1028941975

SQL> select count(*) from customer;

COUNT(*)
----------
601

Ansi Joins

SQL> desc customer
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
CUSTOMER_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(40)
LAST_NAME VARCHAR2(40)
EMAIL VARCHAR2(55)
DOB DATE
CITY VARCHAR2(50)
COUNTRY VARCHAR2(50)
ZIP VARCHAR2(10)

SQL> desc orders
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
ORDER_ID NOT NULL NUMBER(12)
ORDER_DATE DATE
ORDER_MODE VARCHAR2(8)
CUSTOMER_ID NUMBER(6)
ORDER_TOTAL NUMBER(8,2)

SQL> desc order_items
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
ORDER_ID NUMBER(12)
LINE_ITEM_ID NUMBER(3)
PRODUCT_ID NUMBER(6)
UNIT_PRICE NUMBER(8,2)
QUANTITY NUMBER(8)


natural joins

select c.customer_id,c.first_name,last_name,city,country,order_total
from customer c
natural join orders o;

returns errors as the common column "customer_id" is aliased.

ORA-25155: column used in NATURAL join cannot have qualifier
25155. 00000 - "column used in NATURAL join cannot have qualifier"
*Cause: Columns that are used for a named-join (either a NATURAL join
or a join with a USING clause) cannot have an explicit qualifier.
*Action: Remove the qualifier.
Error at Line: 1 Column: 7

remove the c.customer_id alias and it works fine :)

explain plan's

explain plan
SET statement_id = 'orders' INTO plan_table FOR SELECT * FROM customer NATURAL JOIN orders;


SELECT RTRIM (LPAD (' ', 2 * LEVEL)
|| RTRIM (operation)
|| ' '
|| RTRIM (OPTIONS)
|| ' '
|| object_name) query_plan,
cost,
cardinality
FROM plan_table
CONNECT BY PRIOR id = parent_id
START WITH id = 0 ;


QUERY_PLAN COST CARDINALITY
SELECT STATEMENT 80358215 60983502947
MERGE JOIN CARTESIAN 80358215 60983502947
TABLE ACCESS FULL CUSTOMER 5 599
BUFFER SORT 80358210 101808853
TABLE ACCESS FULL ORDERS 134154 101808853

Dual Boot OpenBSD/Ubuntu/Vista using GAG

GAG is a really nice boot manager. Very easy to install and very small in size.

My configuration is as follows
- I installed Vista first then installed OpenBSD 4.6 , then I installed GAG and everything works fine.

Second configuration
- Installed Ubuntu by replacing above Vista , and then later installed GAG but GAG does not work

The trick is when u install Ubuntu , Install grub on its own root partition such as /dev/sda2 and not on the disks MBR (/dev/sda)

Then when you install GAG things will work fine. The reason for this is GAG replaces whatever boot manager (grub) in the earlier case if grub is installed on the MBR. So to keep Ubuntu intact and make it work with GAG install grub on the linux partition.

/dev/sda1 - ubuntu (/ as ext4) -- This was previously Vista
/dev/sda2 - OpenBSD
/dev/sda5 - swap

GAG works with ext4

executing sql file though perl

The code is simple , no big deal . just documenting for possible reuse next time.
The blog just does not let me put the lessthan greaterthan symbol.

#!/usr/bin/perl

use Getopt::Std;

%options=();
getopts("s:",\%options);

&usage if (! defined( $options{s} ) );

my $sqlfile = $options{s};

my $login_file = "/home/reiki/.dev_login";
open (IP,"$login_file") || die "Cannot open file $login_file $!";
my $user_pass = ;
close IP;


open ORA, "| /oracle/reikidev/app/product/10.2.0/bin/sqlplus -s $user_pass" or die "Can't pipe to sqlplus: $!";
print ORA "set trimspool on echo on pagesize 500 linesize 20000 colsep ' '\n";

open (SF,"$sqlfile") || die "Cannot open file $sqlfile $!";
while (my $line = ) {
print ORA "$line";
}

print ORA "exit\n";
close ORA;
close SF;


sub usage {
print "usage: ./ut_reiki_exec_sql.pl -s sqlfile\n";
exit;
}

ksh 93

Ultimate Ksh book/reference by the creator of ksh himself.

Book title is
The New Kornshell - command and programming language.


print vs echo
echo is system dependent and provided for compatiblity with Bourne shell. print is used in the book instead of echo.

global database name

select * from global_name;


show parameter db_domain


select name,value from v$parameter where name = 'db_domain';

byobu and screen

byobu is a nice wrapper to screen (gnu screen)

From the man pages

NAME
byobu - wrapper script for seeding a user's byobu configuration and launching screen

DESCRIPTION
byobu is a script that launches GNU screen in the byobu configuration. This enables the display of system information and status notifications within two lines
at the bottom of the screen session. It also enables multiple tabbed terminal sessions, accessible through simple keystrokes.

Calling sqlplus from shell,perl

#!/usr/bin/perl

open ORA, "| /u01/app/oracle/product/11.1.0/db_1/bin/sqlplus -s $user/$pass" or die "Can't pipe to sqlplus: $!";
print ORA "set trimspool on pagesize 500 linesize 200 colsep ' '\n";
print ORA "spool output.txt\n";
print ORA "select * from tab;\n";
print ORA "spool off\n";
print ORA "exit\n";
close ORA;


Calling from Korn shell

dblogin="`cat $HOME/.db_login`"

sqlplus -s $dblogin LESSTHANlessthanEOF | \
awk '{if(NR==1) printf("%s ", $1); \
if(NR==2) printf("%s ", $1); \
if(NR==3) printf("%s ", $1); \
if(NR==4) printf("%s ", $1); \
if(NR==5) printf("%s ", $1); \
if(NR==6) printf("%s ", $1); \
if(NR==7) printf("%s ", $1);} \
' | read t1_count \
t2_count \
t3_count \
t4_count \
t5_count \
t6_count \
t7_count

set head off pagesize 0 echo off verify off feedback off

select count(*) from t1
/
select count(*) from t2
/
select count(*) from t3
/
select count(*) from t4
/
select count(*) from t5
/
select count(*) from t6
/
select count(*) from t7
/
set head on echo on verify on feedback on
set lines 90
set pages 999
EOF

echo Table t1 has $t1_count rows
echo Table t2 has $t2_count rows

Pipes in Perl

#!/usr/bin/perl

open ORA, "| /u01/app/oracle/product/11.1.0/db_1/bin/sqlplus -s $user/$pass" or die "Can't pipe to sqlplus: $!";
print ORA "set trimspool on pagesize 500 linesize 200 colsep ' '\n";
print ORA "spool output.txt\n";
print ORA "select * from tab;\n";
print ORA "spool off\n";
print ORA "exit\n";
close ORA;

#-- list the processes running on your system
open(PS,"ps -e -o pid,stime,args |") || die "Failed: $!\n";
while ( )
{
print $_;
}

oracle redo log file sizing?

- whats the ideal size.
- what if log switch is occurring too often?
- getting message checkpoint not complete.
- getting message thread 1 cannot allocate new log , sequence ###

Oracle 11g 11.1 install on ubuntu 9.10 (both 64 bit)

sudo apt-get update
sudo apt-get upgrade

sudo apt-get install gcc make binutils libaio1 gawk ksh libc6-dev rpm libmotif3 alien lsb-rpm libtool

sudo addgroup oinstall
sudo addgroup dba
sudo addgroup nobody
sudo usermod -g nobody nobody
sudo mkdir /home/oracle
sudo useradd -g oinstall -G dba -p password -d /home/oracle -s /bin/bash oracle
sudo passwd -l oracle
sudo chown -R oracle:oinstall /home/oracle
sudo ln -s /usr/bin/awk /bin/awk
sudo ln -s /usr/bin/rpm /bin/rpm
sudo ln -s /usr/bin/basename /bin/basename
sudo mkdir /etc/rc.d
for i in 0 1 2 3 4 5 6 S ; do sudo ln -s /etc/rc$i.d /etc/rc.d/rc$i.d ; done
sudo mkdir -p /u01/app/oracle
sudo chown -R oracle:oinstall /u01

The orginal kernel parameters can be found by running the following.

sudo  /sbin/sysctl -a | grep kernel.sem
sudo  /sbin/sysctl -a | grep kernel.shm
sudo  /sbin/sysctl -a | grep file-max
sudo  /sbin/sysctl -a | grep ip_local_port_range
sudo  /sbin/sysctl -a | grep rmem_default
sudo  /sbin/sysctl -a | grep rmem_max
sudo  /sbin/sysctl -a | grep wmem_default
sudo  /sbin/sysctl -a | grep wmem_max

The output of the above command returns

kernel.sem = 250    32000    32    128
kernel.shmmax = 33554432
kernel.shmall = 2097152
kernel.shmmni = 4096
fs.file-max = 794920
net.ipv4.ip_local_port_range = 32768    61000
net.core.rmem_default = 124928
net.core.rmem_max = 131071
net.core.wmem_default = 124928
net.core.wmem_max = 131071



change the kernel parameters if the setting found above is lower than the recommendation given in the following table.

shmmni	4096
shmall	2097152
shmmax	2147483648
semmsl	250
semmns	32000
semopm	100
semmni	128
file-max	65536
ip_local_port_range	1024 - 65000
rmem_default	1048576
rmem_max	1048576
wmem_default	262144
wmem_max	262144

sudo cat >> /etc/sysctl.conf << EOF
kernel.shmmax = 2147483648
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144
EOF

sudo cat >> /etc/security/limits.conf << EOF

oracle soft nproc  2047

oracle hard nproc  16384

oracle soft nofile 1024

oracle hard nofile 65536EOF

sudo cat >> /etc/pam.d/login << EOF
session required /lib/security/pam_limits.so
session required pam_limits.so
EOF

sudo sysctl -p

sudo cat >> /etc/profile << EOF
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
export PATH=$PATH:/u01/app/oracle/product/11.1.0/db_1/bin
export ORACLE_SID=cert11
EOF

sudo cat >> /etc/init.d/oracledb << EOF
#!/bin/bash
#
# /etc/init.d/oracledb
#
# Run-level Startup script for the Oracle Listener and Instances
# It relies on the information on /etc/oratab

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export ORACLE_OWNR=oracle
export PATH=$PATH:$ORACLE_HOME/bin

if [ ! -f $ORACLE_HOME/bin/dbstart -o ! -d $ORACLE_HOME ]
then
echo "Oracle startup: cannot start"
exit 1
fi

case "$1" in
start)
# Oracle listener and instance startup
echo -n "Starting Oracle: "
su $ORACLE_OWNR -c "$ORACLE_HOME/bin/lsnrctl start"
su $ORACLE_OWNR -c "$ORACLE_HOME/bin/dbstart"
touch /var/lock/oracle
echo "OK"
;;
stop)
# Oracle listener and instance shutdown
echo -n "Shutdown Oracle: "
su $ORACLE_OWNR -c "$ORACLE_HOME/bin/lsnrctl stop"
su $ORACLE_OWNR -c $ORACLE_HOME/bin/dbshut
rm -f /var/lock/oracle
echo "OK"
;;
reload|restart)
$0 stop
$0 start
;;
*)
echo "Usage: `basename $0` start|stop|restart|reload"
exit 1
esac

exit 0
EOF


sudo chmod a+x /etc/init.d/oracledb

cd /etc/init.d

sudo update-rc.d oracledb defaults 99


Edit .bash_profile and add the following
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export ORACLE_OWNR=oracle
export PATH=$PATH:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

install oracle software only

./runInstaller -ignoreSysPrereqs


then run dbca and name the database (cert11) in my case.

edit /etc/oratab and set the last field to 'Y' to start oracle automatically at boot.

Making flash player work in Ubuntu 9.10 64 bit

sudo apt-get remove --purge flashplugin-nonfree gnash gnash-common mozilla-plugin-gnash
sudo apt-get remove --purge swfdec-mozilla libflashsupport nspluginwrapper iceape-flashplugin
sudo apt-get remove --purge iceweasel-flashplugin mozilla-flashplugin firefox-flashplugin
sudo apt-get remove --purge xulrunner-flashplugin midbrowser-flashplugin xulrunner-addons-flashplugin
sudo rm -f /usr/lib/mozilla/plugins/npwrapper.libflashplayer.so
sudo rm -f /usr/lib/iceweasel/plugins/npwrapper.libflashplayer.so
sudo rm -f /usr/lib/firefox/plugins/npwrapper.libflashplayer.so
sudo rm -f /var/lib/flashplugin-nonfree/npwrapper.libflashplayer.so
sudo rm -f ~/.mozilla/plugins/npwrapper.libflashplayer.so
sudo rm -f /usr/lib/firefox-addons/plugins/npwrapper.libflashplayer.so
sudo rm -f ~/.mozilla/plugins/*flash*
sudo rm -f /usr/lib/firefox/plugins/*flash*
sudo rm -f /usr/lib/firefox-addons/plugins/*flash*
sudo rm -f /usr/lib/mozilla/plugins/*flash*





sudo wget http://download.macromedia.com/pub/labs/flashplayer10/libflashplayer-10.0.45.2.linux-x86_64.so.tar.gz


sudo tar xzf libflashplayer-10.0.45.2.linux-x86_64.so.tar.gz
sudo mv libflashplayer.so /usr/lib/mozilla/plugins


restart firefox and test flash on youtube.

source : http://ubuntuforums.org/showthread.php?t=1081964

Disk cloning / partition image howto

http://wiki.archlinux.org/index.php/Disk_Cloning

use clonezilla or fsarchiver (for ext4 partitions)

fsarchiver is found on system rescuecd

clonezilla is a live cd.

PL/SQL questions

- When do you omit a semicolon with dynamic sql?
- expdp. how to export metadata only?
-

Oracle PL/SQL test score.

Assessment Scores
Test: Oracle PL/SQL
This report is confidential and its contents are intended to assist in the prediction of an applicant's work behavior. If you would like more information about this interpretive report or other products that PreVisor offers, please contact your account representative.

Score: 3.67
Proficiency Level: Advanced (3.51 - 4.50)
The candidate has mastered the basic concepts of Oracle PL/SQL (Architecture, Exception Handling, Built-in Packages) and intermediate Oracle PL/SQL concepts (SQL Operations, Language Elements, Tuning). The candidate is likely proficient with more advanced Oracle PL/SQL concepts such as:
# Subprograms
# Debugging
# Utilities
At the Advanced level, the candidate will be capable of working on projects involving Oracle PL/SQL and will be capable of mentoring others on most projects in this area.

Percentile Comparisons
The percentile score indicates how well the candidate scored relative to other candidates in the comparison population indicated by the score.


Percentile Comparisons


Percentile
Low Medium High
30 70 100
Global Population
86
Company Population
74


Time Summary:
Time Taken (mm:ss): 38:29
Started on: 3/4/10 3:08 PM
Completed on: 3/4/10 3:47 PM

Rownum trick

SELECT ROWNUM
FROM DUAL
CONNECT BY ROWNUM<=1000000;

or if you need random numbers, just simply use

SELECT DBMS_RANDOM.VALUE
FROM DUAL
CONNECT BY ROWNUM<=1000000;


source : http://www.oracledba.co.uk/tips/cute_rownum.htm

To Practice

- Pipelined functions
- collections : with limit,without limit , exceptions
- bulk collect , binding
- ref cursor
- indexes
- explain plan with partitions
- parallel queries
- materialized views
- advanced query
- replication
- dbms_jobs
- dbms_lock.sleep(.01)
- partitioning , partition exchange
- sdlc
- normalization
- tuning
- dbms_stats
- stats_pack

Dimensional Modeling Case Study : Retail Sales

Step 1: Select the business Process : we select POS transactions
Step 2: Declare the grain

Capture the grain in the facts at the most detail level. Such most detailed data is called ATOMIC DATA.
Atomic data provides maximum analytic flexibility because it can be constrained and rolled up in every possible way. Detailed data in a dimensional model is poised and ready for the ad hoc attack by business users.

Step 3: Choose the dimensions
Step 4: Identify the facts.

Question: Should a calculated fact be stored in the database?
A) Yes as application logic at different places using the facts can introduce errors.
Better to have one source of calculation , therefore no chance of user error.
Also why should the reporting tool do calculations if can be avoided.

Yes Views can be an alternate solution but in that case users should not be given access to underlying tables in any circumstance.


Dimensions :
----------
Date
Product
POS Transaction Fact
Store
Promotion


Dimensions should have descriptive column values like
Holiday/NonHoliday instead of Y/N
Weekend/Weekday instead of Y/N flags
selling season column can have values like "Valentine's day/Christmas/Thanksgiving" etc

In a report dimension attributes become report headers.
eg total sales by Month by Weekday

Descriptive values help in slicing/dicing data rather than put another decode logic in
reports say weekend or non weekend based on Y/N flag.

Q) Do we need an explicit date dimension. Cant we do with actual date in the fact table instead
of a surrogate date key from date dimension
A) YES
reasons
1) relation databases can easily handle joins, optimizer will do it efficiently based on PK index defined.
2) Most databases will not index date calculations, so queries constraining on date calculations will be slower as will not take advantage of index.
3) Business users are not versed with SQL date semantics like how to determine weekend/weekday
given a date.
4) calendar logic belongs in the dimension table and not in the application code
5) SQL based date key (actual date) is typically 8 bytes so wasting 4 bytes (integer) for every date key if date is used in the facts.
in each row of the fact table.

Better to have a separate time dimension and not combine with date dimension.

four (4) step dimensional design process

- Select the business process to model
eg raw materials purchasing, orders, shipments, invoicing, inventory, general ledger etc
- Declare the grain of the business process ie specifying exactly what the individual fact
table row represents
- Choose the dimensions that apply to each fact table
- Identify the numeric facts that will populate each fact table.

PL/SQL performance enhancing features

Runtime optimization of fetch loops (10g onwards)
only works for FOR loops involving cursors
eg FOR cust_rec in (select * from customer)
LOOP
END LOOP

and FOR cust_rec in cust_cur
LOOP
END LOOP

Oracle in the above cases post ver 10g will fetch upto 100 rows in each fetch rather than 1
record each time.

This won't work with
open cursor
loop
exit when cursor%NOTFOUND
fetch cursorname into
end loop
close cursorname

source : Page 798 (Oracle PL/SQL by Steven Feuerstein)

Data Caching techniques
- Package Based Caching
- caching variables/constants
- caching table
- caching most commonly accessed rows of a table (just-in-time caching of table data)

Deterministic function caching
Function result caching

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.