oracle study test2b

initcap()


concatenating char strings
  - concat two strings results in another string
  - preserves trailing spaces
  - uses concat as alternativ to vertical bar operator
  - trets zero-length char strings as nulls


convert


before trigger
after trigger


leve of precedence
1. all comparison ops
2. NOT 
3. AND
4. OR



grant privilege
privilege CONNECT nees to be assigned for user to access database
minimum privilege necessary required to access a database
by default, SYSTEM default userr tablespace


declare
begin
/* block1 */
declare
begin
end
/* block2 */
declare 
begin
end


rollback without a savepoint clause
- ends all transactions
- undoes all the changes in the current transactions
- erases all the savepoints in that transactions
- releases the transactions lock

create sequence order_seq
  start with 1
maxvalue 9999 
cycle;  # also nocycle


indexes
  context - for text retrieval, msword, html, xml, plain text
  ctxcat is used to improve mixed query performance, small text fragments, like dates, names
  ctxrule is used to build document classification app.  



grant select, update
on john.product_master
to jack

dml - select, insert, update, delete
ddl - create, alter, drop, rename, truncate
dcl - grant, revoke


subquery used in the FROM clause to eliminiate need to create a new view


spool, spool off
output is *.lst


MERGE can be used to both update and insert
rows that have matchinng keys are updated, others are inserted


lock table emp in row exclusive mode
  - it will wait if the resources are not available
  - row exclusive locks are same as row share
  - these locks are acquired when updating, inserting, deleting



check constraints, limitations
  - condition must be a boolean
  - condition cannot include sysdate, uid, user, or userenv sql
  - condition cannot contain subqueries or sequences


alter table supplier_master
  drop primary key;



CREATE/ALTER/DROP/TRUNCATE, DDL statements with implicit commits
DML do not have implicit
they are eligible for rollback until a commit is issued


normally, an operator recieves a NULL, the result is null
except CONCAt

script is defined as a group of SQL, Sql*plus, Pl/SQL.  
How can they be run?
- enter sql*plus, invoke commands and run interactively
- run a script in "batch mode", specify on command line
- speicify it on the command line with @ sign
  $ sqlplus user/pass@mydb @my_script.sql
two scripts are run too: glogin.sql & login.sql

get - loads external file contents into the buffer
append - appends a line to the buffer
edit - invokes system-default editor to the edit buffer


highest precedence
1. unary +/-, PRIOR
2  / arith ops
3. binary + - arith ops, || char ops
4. all comparison ops


order by desc
order by ascen, default, is alphabetically sorting




user_views - contains definition view
user_tab_views - contains table names for user views



how to run a sql command
- place semi-colon (;) as the end of last clause
- place a forward slash at the sql prompt
- issue a run command at the sql prompt


extneral table
new functionality
any flat file,, appears as real table
and user cannot index an external table


data dictionary
dba_: information useful for DBAs, view all database objects
user_: information on objects owned by a particular user id
all_: information on every object the user has access to


alter table sales_order_details
add constraint order_fkey
foreign key (s_order_no) references sales_order
modify (qty_order(7) not null);

select count(*)
SELECT CTX_QUERY.COUNT_HITS  # rough count


alter sequence
arguments used
- schema - name of the schema to contain the sequence
- increment by
- maxvalue
- minvalue
- nominvalue 

round(months_between(d1, d2))
ROUND(DAYS_BETWEEN)
MONTHS_BETWEEN

CURDATE 
CURRENT_DATE

Mod(10,0) = 0
returns remainder of m divided by n.  If n is 0, returns m


AGV cannot be used in a union
UNIONS
a) no. of columns in all queries should be the same
b) union cannot be used in subquery
c) aggregate functions cannot be used in a union
d) datatype of the columns in each query musts be the same


INSERT INTO employee(ID_NO,name,salary)
  values(employee_id_seq.NEXTVAL, &name, &salary)


SEQUENCE uses the following as default values:
- NOORDER
- INCREMENT BY 1
- CACHE 20


SELECT to_char(hire_date, 'fmDD MONTH YYYY')
  DATE HIRED FROM employee;


the purpose of GRANTS is to grants the ADMIN, DBA, DDL, or RESOURCE roles to users, or grants privileges on a database objects to users.  To grant roles, the user must be logged inot the database as SYSEM, or as a user with DBA/DDL AND ADMIN privileges, or with RESOURCE privileges to GRANT privileges on own objects to other users.  the dba role is recommended as a replacement for DDL role wheneve possible.

ALTER CREATE SEQUENCE order_seq
INCREMENT BY 2
CACHE 40;

oracle study test2a

find current value of sequence
- currval
- nextval



declare total_sal number(9);
begin
  update emp
  set sal=1500
  where name='clark'
  savepoint clark_sal; 
  select sum(sal) into total_sal from emp;
  if total_sal > 20000 rollback;
  end if;
  commit;
end;



lock table customer in exclusive mode no wait
exclusive
share - allows concurrent queries, but disallows updates to table


object privilege for REVOKE?
- alter
- delete, 
- index
- insert
- select
- update

natural join, defined in terms of cartisian product


select name
from authors
where name like '%O\_H%' ESCAPE '\';


select count(birthday)
function sum() & avg() canot be used with date type

drop table table1 cascade constraints
drop statement drops table, data, indexes, contraints, triggers,and privileges


declare 
   num_in_stock number(5)
begin
  select quantity into num_in_stock FROM product
  where product = 'Floppy'
  if num_in_stock > 0 then
  update product set quantity=quantity-1
  where product = 'floppy'
  insert into record
  vlaues('one floppy sale', sysdate)
  end if;
  commit;
end;



select last_nmae, commission_pct
  ( case commission_pct
      when 0.1 then 'low'
      when 0.15 then 'avg'
      when 0.2 then 'high'
    else 'n/a'
    end) commission
   from employees
# return values cannot be null



alter table student modify constraint (name varchar2(25) not null)


sql doesn't allow the use of distinct with count(*)
its possible to use distinct with max, min, but wont make a difference
keyword ALL can be used in place of distinct


select ordid, NLV(TO_CHAR(shipdate), 'Not shipped' ...
# notice ', not "


STORE SET  # generates a *.sql file


- tablespace is parented by database, so tablespace cannot be a part of another db
- datafile is parented by tablespace, so datafile cannot be part of another db
- a datafile may not be removed from a tablespace


right outer join
+
oracle will return NULL for any rows that have no matching rows


select nullif(10,10) from dual;
returns a null if both are equal


scalar subqueries can only return a single column and single row
scalar subqueries cannot be used for:
- default values for columns
- returning clauses
- hash expressions for cluster
- functional index expressions
- check constraints on columns
- when conditions of triggers
- group by and having clauses
- start with and connect by clauses

xen config on FC5

baseurl=file:///home/pgabler/dvd_fc5
/etc/yum.repos.d





/usr/sbin/xm list
dd if=/dev/zero of=fedora1.img bs=5M count=1 seek=1024
/sbin/mke2fs -F -j fedora1.img
mount -o loop fedora1.img /mnt
for i in console null zero ; do /sbin/MAKEDEV -d /mnt/dev -x $i ; done

mkdir /mnt/etc
vi /mnt/etc/fstab
==============
/dev/sda5               /                       ext3    defaults 1 1
none                    /dev/pts                devpts  gid=5,mode=620 0 0
none                    /dev/shm                tmpfs   defaults 0 0
none                    /proc                   proc    defaults 0 0
none                    /sys                    sysfs   defaults 0 0
==============

mkdir /mnt/proc 
mount -t proc none /mnt/proc

yum --installroot=/mnt -y groupinstall Base
yum --installroot=/mnt -y groupinstall "X Window System"
yum --installroot=/mnt -y groupinstall "GNOME Desktop Environment"
cp /etc/yum*d/fedora-core.repo fedora-core.repo
cp /etc/yum*d/fedora-updates.repo fedora-updates.repo
cp /etc/yum*d/fedora-extras.repo fedora-extras.repo
yum --installroot=/mnt grouplist

cp /etc/selinux/config /mnt/etc/selinux/config
cd /
umount /mnt/proc
umount /mnt

vi /etc/xen/rawhide1
=========
kernel ="/boot/vmlinuz-2.6.15-1.2054_FC5xenU"
memory = 384
name = "rawhide1"
nics = 1
disk = ['file:/root/fedora.img,sda5,w']
root = "/dev/sda5"
extra = "ro selinux=0 3"
=========


xm mem-max 0 512
xm mem-set 0 512


# xm shutdown rawhide1
xm create -c rawhide1







dd if=/dev/zero of=/swapfile bs=1M count=1024
/sbin/mkswap /swapfile
swapon /swapfile
fstab:
/swapfile  swap  swap  defaults  0 0

book: Network Performance Open Source Toolkit

network performance open source toolkit


c1 defining network perf
c2 watching network traffic
c3 network device util
c4 netperf
c5 dbs
c6 iperf
c7 pathrate
c8 nettest
c9 netlogger
c10 tcptrace
c11 ntop
c12 comparing network perf tools
c13 measuring app perf
c14 dummynet
c15 nist net
c16 network traffic generator
c17 ns
c18 comparing network app perf tools


c1 defining network perf
c2 watching network traffic # libpcap/winpcap, tcpdump, windump, analyzer, etherreal
c3 network device util # net-snmp
c4 netperf # send data streams across net, and monitor
c5 dbs # perform net tests b/w two remote hosts on network
c6 iperf # how TCP parameters affect net app perf
c7 pathrate # network stat calculations involing delays present in transferring packets
c8 nettest # secure shell for performing network tests b/w hosts
c9 netlogger # set of APIs, logging net work events, such as writing data to network
c10 tcptrace # analyze data captured by tcpdump, display info about each TCP session
c11 ntop # network utilization by each device on network
c12 comparing network perf tools
c13 measuring app perf # network emulators and simulators
c14 dummynet # freebsd, emulate network delay, bandwidth limitations, packet loss
c15 nist net # simulate network behavior using Linux
c16 network traffic generator # generate specific data traffic patterns
c17 ns # network simulator
c18 SSFNetw # model net behaviro using C++ or Java
c19 comparing network app perf tools





c1 defining network performance
availability, ping

2 biggest causes of lost packets
- collisions
- packets dropped by network device

sometimes net work device passes packets of one size, but not another
# ping -s 1000 192.168.0.1

response time, ping

traceroute
some networks sometimes use redudnant paths
packets don't always take best path

network utilization on 10Meg Ether
%util = ((datasent + datarec) * 8) / (intspeed * samptime) * 100

network throughput
bandwidth capacity

methods for collecting performance data
- querying, SNMP
- watching, tcpdump
- generating test traffic

watching existing traffic, watch for these:
1. packet retransmissions
2. frozen tcp window sizes
3. broadcast storms
4. network advertisements
5. chatty applications
6. quality of service applications






c2. watching network traffic

libpcap, winpcap
www.tcpdump.org/release/libpcap-0.7.1.tar.gz
winpcap.polito.it
www.windump.polito.it/install.bin/alpha.windump.exe

tcpdump
$tcpdump -i eth0 -s 200 -x

windump
c:\>windump -s 200 -x -w testcap

filtering packets with tcpdump/windump
tcpdump ip host myhost
tcpdump iip host 192.168.0.1 and port not 23

Analyzer
windows program similar to windump, but with cool gui
shows different layers, mac, ip, tcp, telnet application data

ethereal
one nice feature of ethereal is it decodes alot more packet types for you?
www.ethereal.com/distribution



c3 network device utilization

net-snmp package
sourceforge.net/project/showfiles.php?group_id=12694

snmpwalk/snmpdelta
MIB2 shit, ifNumber, ifTable
ifEntry objects: ifIndex, ifDescr
# display nimber opf interfaces on a device
$ snmpget -c public 192.168.0.1 IF-MIB::ifNumber.0
$ snmpdelta -c public -Cp 5 -CT 192.168.0.1 IF-MIB::ifInOctets.23 IF-MIB::ifOutOctets.23
-CT # format output into tables
-Cs timestamp each entry
once you know input & output over time, calculate bandwidth
%util = ((inO + out0) * 8 ) / (ifSpeed * time) * 100

be careful of bandwidth calcs, full duplex allow 2ce amount of traffic, so 100Mb = 200Mb # not sure about that

error rates:
error rate = (ifInErrors * 100) / (ifInUcastPkts + ifInNUcastPkts)

$snmpdelta -c public -CP 60 -Cs 192.168.0.1 IF-MIB::ifInErrors.23 IF-MIB::ifInUcastPkts.23 IF-MIB::ifInNUcastPkts.23

Using Vendor MIBS
Cisco CPU MIB
MIB uses ASN.1 syntax to define each of the objects
first step to SNMP script is to obtain MIB
ftp://ftp.cisco.com/pub/mibs/v1/OLD-CISCO-CPU-MIB.my
there is a newer MIB, but they are dont work on everything
MIB information for router CPU util is stored in OLD-CISCO-CPU-MIB.my # not sure how they got this
http://www.cisco.com/public/sw-center/netmgmt/cmtk/mibs.shtml
busyPer, avgbusy1, avgbusy5 # choose one of three mib objects
backtrace to get full object identifyer
lcpu, local, found in CSICO-SMI-V1SMI.my
putting all together for avgBusy5, 1.3.6.1.4.1.9.2.1.58.0

#!/bin/sh
datenow=`date +%x,%T"
util=`snmpget 192.168.0.1 -c public 1.3.6.1.4.1.9.2.1.58.0 | awk '{print $4}'
echo $datenow, $util >> utilization.txt

cron every 5min




c4 netperf
client/server, netserver & netperf
ftp.cup.hp.com/dist/networking/benchmarks/netperf

TCP_Stream
$ netperf -H 192.168.0.1 -l 60
Throughput in bits/sec = 7.75 (of 10Meg)

UDP_Stream
$ netperf -t UDP_Stream -H 192.168.0.1 # error message too long?
$ netperf -t UDP_STREAM -H 192.168.0.1 -- -m 102 = 9.52 (of 10Meg)

Measuring Request/Repsonse TImes
TCP_RR
$ netperf -t TCP_RR 192.168.1.1 -l 60
Transaction Rate per sec = 1944

TCP_CRR
sets up a new TCP connection for each request, similar to HTTP
$ netperf -t TCP_CRR -H 192.168.0.1 -l 60
Transactions per sec = 17.25

UDP_RR
$ netperf -t UDP_RR -H 192.168.1.1 -l 60
Trans Rate per sec = 2151

snapshot_script

bittorent sites

This summary is not available. Please click here to view the post.

Ten Most Used BitTorrent Sites Compared

This summary is not available. Please click here to view the post.

google security

1) cookies: setup firefox to deny google cookies, or at least remove cookies when closing the browser

2) ip address: change ip address periodically, probably best done by updating mac on router, and doing another dhcp to fetch a new Ip address