oracle study test2c

exclusive lock - allows queries but prohibits any other activity
share - allows queries but prohibits updates


retrieve all student records in all classes
left outer join

UPDATES without WHERE clauses are defined as unqualified UPDATES

syntax for a View
cannot use groupby, distinct, having, avg, sum, etc.


alter tablespace with TEMPORARY clause
oracle performs a checkpoint for all online datafiles
but does not ensure that all files can be written
any offline files may require media recovery before bringing the tablespace online

which of thesee is an iterative control
- for, while, goto


what type of locks are used with admin allows concurrent access to table,and also wants other users to prohibit other users to lock entire table exclusively?
- ROW SHARE UPDATE: allow concurrent access but prohibt users to lock entire table exclusively
- ROW EXCLUSIVE: same as ROW SHARE, but also prohibits locking in share mode
     which are acquired when updating, inserting, or deleting
- EXCLUSIVE allows uery on the locked resource but prohibits any other activity
- SHARE allows queries but prohibits updates to a table



"Binding" involves the use of placeholders and the USING clause, while concatenation adds the values directly to the SQL string.
the two methods to insert program values into sql string are binding & concat
binding is faster
when bindin, the sql string does not contain the the value; 
it contains only a placeholder name
this allows the user the ability to bind different values to the same sql statement
biding is much easier to write and maintain


types of triggers
- BEFORE statement trigger
- BEFORE row trigger
- AFTER statement trigger



REVOKE DELETE ON emp FROM MATT

CREAT VIEW dept_salary_vw AS
SELECT dept_no, salary, last_name
FROM employee
WHERE salary > 5000
with check option;
# cannot insert a row into view with salary > 5000

SELECT ... FOR UPDATE
- can be used to override locks
- cannot be used with DISTINCT and GROUP BY
- cannot be used with set operators and group functions


scalar subqueries
- return a single value
- can be used with CASE
- can b be used with ORDERBY
- can be used with SELECT
- VALUES clause of an INSERT statement
- WHERE clause
- ORDER BY clause
- As a parameter of a function


locking charasteristics of UPDATE, DElETE, INSERT
- acquires exclusive locks on the rows to be moidified, and row exclusive lock on table containing those rows
- other users can query the rows to be mod, but cannot alter.  Can work on other rows
- locks on rows and tables are released on COMMIT
- a rollback to savepoint, releases all locks acquired upto the named savepoint

GRANT option
allows for privileges passed on via WITH GRANT OPTION of GRANT to be revoked sepeartely from the privileges themselves
- revoke fails if it results in an abandoned object, such as a view, unless the CASCADE keyword has been specified
- privileges granted to this user by other users are not affected

MERGE 
- accepts the character string form of two labels
- the MERGE_LABEL function is a utility for merging two labels together
- it accepts the three character specification of a merge format


Sql*plus
to run a script file, issue the command "START filename, or use the @filename
SAVE
EDIT
GET


ALTER table command with the ADD clause in the Employee table


administrator has granted me the privilege Create any table and create procedure
* i can create tables in any schema but drop tables and create proc in my schema

GRANT select,insert,modify ON Employees TO HR WITH GRANT OPTION;


ROLLBACK with SAVEPOINT;
it rolls back just a portion of the transaction


which functions are used in Oracle 9i
Group functions return a result per set of rows, ie. DISTINCT, SUM
Object reference functions: Manage REF values in user-defined types (ie. DEREF and REFTOHEX)
Analytical funcitions, COmplex analytics
Misc functions: a couple dozen functions that do not fit any of the above categories (ie. NVL, USER, BFILENAME, GREATEST)


Which of these are the locking characteristics for explicit locking?
Explicit lock is put by a trigger
* DBA can lock any table
* Other users can lock tables they own or any tables on which they have been granted table privileges
Users can explicitly lock either tables or rows using select ... for update, or a lock statement


write a subquery
* subquery can be used by the following commands
- it can be used to create tables, and insert records
- it can be used to update records
- the subquery can be used to provide values for the conditions in SELECT, WHERE, HAVING IN, UPDATE, DELETE


procedures
CREATE OR REPLACE PROCEDURE truncobj (
  nm IN VARCHAR2,
  tp IN VARCHAR2 := ;'TABLE',
  sch IN VARCHAR2 := NULL)
IS
BEGIN  
   EXECUTE IMMEDIATE
     'TRUNCATE || tp || '' || nvl(sch, USER) || '.' || nm;
END

# WTF?  this is too tough
the following is the query to give all the clients and saleman in the city
SELECT name FROM sales_master
where city = 'new york' intersect
select name from sales_master
where city = 'new york' in 
(select city from client_master c
where c.client_no IN
select client_no from sales_order s
where salesman_no = sales_master.salesman_no)


COMPUTE - sql*plus  for calculating valoues
CHANGE - change characters in the buffer
CLEAR - clear definitions; or clear the screen or buffer
COLUMN - define column formats or characteristics


# turn on constraint
ALTER TABLE table_name
ENABLE CONSTRAINT constraint_NAME


Which of the following is true for DROP FUNCTION?
* it is used to remove standalone stored function from database


which of the following is true for views?
- primary key of the table should be included
- the view can be created using a single table
- aggregate functions cannot be used in a VIEW
- SELECT statement should not have subquery


which of this is true for iSQL*Plus
this is just a web-interface version of SQL*Plus


LEAST_UBOUND - returns char string label, one label which dominates both
GREatet_lbound - DETERMINE LOWEST LABEL OF THE DATA


mergE statement
- merges the data from a select statement into a table
- user can decide when data is updated/inserted via the "ON()" clause
- user can decide what columns are updated
- user can decide what data is inserted.


trusted in the function based index?
QUERY_REWRITE_ENABLED=TRUE
the followingn are requirements to create a function-based index in the user defined schema on his table:
- prereqs for creating a conventional index
- user should have the QUERY REWRITE system privilege
- to use the function-based indexes in queries, the QUERY_REWRITE_ENABLE=TRUE
- QUERY_REWRITE_INTEGRITY set to TRUSTED
- create the index in another schema the user must have the GLOBAL_QUERY_REWRITE privilege
- the table must have EXECUTE object privielge on the functionss) used in the function based index


steps to create a cursor?
- dclare a cursor that specifies the SQL select statement 
- open
- row at a time
- close

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.