IP Spoofing: An Introduction
--------------------------------------------------------------------------------
Criminals have long employed the tactic of masking their true identity, from disguises to aliases to caller-id blocking. It should come as no surprise then, that criminals who conduct their nefarious activities on networks and computers should employ such techniques. IP spoofing is one of the most common forms of on-line camouflage. In IP spoofing, an attacker gains unauthorized access to a computer or a network by making it appear that a malicious message has come from a trusted machine by “spoofing” the IP address of that machine. In this article, we will examine the concepts of IP spoofing: why it is possible, how it works, what it is used for and how to defend against it.
History
The concept of IP spoofing, was initially discussed in academic circles in the 1980's. While known about for sometime, it was primarily theoretical until Robert Morris, whose son wrote the first Internet Worm, discovered a security weakness in the TCP protocol known as sequence prediction. Stephen Bellovin discussed the problem in-depth in Security Problems in the TCP/IP Protocol Suite, a paper that addressed design problems with the TCP/IP protocol suite. Another infamous attack, Kevin Mitnick's Christmas Day crack of Tsutomu Shimomura's machine, employed the IP spoofing and TCP sequence prediction techniques. While the popularity of such cracks has decreased due to the demise of the services they exploited, spoofing can still be used and needs to be addressed by all security administrators.
Technical Discussion
To completely understand how these attacks can take place, one must examine the structure of the TCP/IP protocol suite. A basic understanding of these headers and network exchanges is crucial to the process.
Internet Protocol – IP
Internet protocol (IP) is a network protocol operating at layer 3 (network) of the OSI model. It is a connectionless model, meaning there is no information regarding transaction state, which is used to route packets on a network. Additionally, there is no method in place to ensure that a packet is properly delivered to the destination.
Examining the IP header, we can see that the first 12 bytes (or the top 3 rows of the header) contain various information about the packet. The next 8 bytes (the next 2 rows), however, contains the source and destination IP addresses. Using one of several tools, an attacker can easily modify these addresses – specifically the “source address” field. It's important to note that each datagram is sent independent of all others due to the stateless nature of IP. Keep this fact in mind as we examine TCP in the next section.
Transmission Control Protocol – TCP
IP can be thought of as a routing wrapper for layer 4 (transport), which contains the Transmission Control Protocol (TCP). Unlike IP, TCP uses a connection-oriented design. This means that the participants in a TCP session must first build a connection - via the 3-way handshake (SYN-SYN/ACK-ACK) - then update one another on progress - via sequences and acknowledgements. This “conversation”, ensures data reliability, since the sender receives an OK from the recipient after each packet exchange.
As you can see above, a TCP header is very different from an IP header. We are concerned with the first 12 bytes of the TCP packet, which contain port and sequencing information. Much like an IP datagram, TCP packets can be manipulated using software. The source and destination ports normally depend on the network application in use (for example, HTTP via port 80). What's important for our understanding of spoofing are the sequence and acknowledgement numbers. The data contained in these fields ensures packet delivery by determining whether or not a packet needs to be resent. The sequence number is the number of the first byte in the current packet, which is relevant to the data stream. The acknowledgement number, in turn, contains the value of the next expected sequence number in the stream. This relationship confirms, on both ends, that the proper packets were received. It’s quite different than IP, since transaction state is closely monitored.
Consequences of the TCP/IP Design
Now that we have an overview of the TCP/IP formats, let's examine the consequences. Obviously, it's very easy to mask a source address by manipulating an IP header. This technique is used for obvious reasons and is employed in several of the attacks discussed below. Another consequence, specific to TCP, is sequence number prediction, which can lead to session hijacking or host impersonating. This method builds on IP spoofing, since a session, albeit a false one, is built. We will examine the ramifications of this in the attacks discussed below.
Spoofing Attacks
There are a few variations on the types of attacks that successfully employ IP spoofing. Although some are relatively dated, others are very pertinent to current security concerns.
Non-Blind Spoofing
This type of attack takes place when the attacker is on the same subnet as the victim. The sequence and acknowledgement numbers can be sniffed, eliminating the potential difficulty of calculating them accurately. The biggest threat of spoofing in this instance would be session hijacking. This is accomplished by corrupting the datastream of an established connection, then re-establishing it based on correct sequence and acknowledgement numbers with the attack machine. Using this technique, an attacker could effectively bypass any authentication measures taken place to build the connection.
Blind Spoofing
This is a more sophisticated attack, because the sequence and acknowledgement numbers are unreachable. In order to circumvent this, several packets are sent to the target machine in order to sample sequence numbers. While not the case today, machines in the past used basic techniques for generating sequence numbers. It was relatively easy to discover the exact formula by studying packets and TCP sessions. Today, most OSs implement random sequence number generation, making it difficult to predict them accurately. If, however, the sequence number was compromised, data could be sent to the target. Several years ago, many machines used host-based authentication services (i.e. Rlogin). A properly crafted attack could add the requisite data to a system (i.e. a new user account), blindly, enabling full access for the attacker who was impersonating a trusted host.
Man In the Middle Attack
Both types of spoofing are forms of a common security violation known as a man in the middle (MITM) attack. In these attacks, a malicious party intercepts a legitimate communication between two friendly parties. The malicious host then controls the flow of communication and can eliminate or alter the information sent by one of the original participants without the knowledge of either the original sender or the recipient. In this way, an attacker can fool a victim into disclosing confidential information by “spoofing” the identity of the original sender, who is presumably trusted by the recipient.
Denial of Service Attack
IP spoofing is almost always used in what is currently one of the most difficult attacks to defend against – denial of service attacks, or DoS. Since crackers are concerned only with consuming bandwidth and resources, they need not worry about properly completing handshakes and transactions. Rather, they wish to flood the victim with as many packets as possible in a short amount of time. In order to prolong the effectiveness of the attack, they spoof source IP addresses to make tracing and stopping the DoS as difficult as possible. When multiple compromised hosts are participating in the attack, all sending spoofed traffic, it is very challenging to quickly block traffic.
Misconceptions of IP Spoofing
While some of the attacks described above are a bit outdated, such as session hijacking for host-based authentication services, IP spoofing is still prevalent in network scanning and probes, as well as denial of service floods. However, the technique does not allow for anonymous Internet access, which is a common misconception for those unfamiliar with the practice. Any sort of spoofing beyond simple floods is relatively advanced and used in very specific instances such as evasion and connection hijacking.
Defending Against Spoofing
There are a few precautions that can be taken to limit IP spoofing risks on your network, such as:
Filtering at the Router - Implementing ingress and egress filtering on your border routers is a great place to start your spoofing defense. You will need to implement an ACL (access control list) that blocks private IP addresses on your downstream interface. Additionally, this interface should not accept addresses with your internal range as the source, as this is a common spoofing technique used to circumvent firewalls. On the upstream interface, you should restrict source addresses outside of your valid range, which will prevent someone on your network from sending spoofed traffic to the Internet.
Encryption and Authentication - Implementing encryption and authentication will also reduce spoofing threats. Both of these features are included in Ipv6, which will eliminate current spoofing threats. Additionally, you should eliminate all host-based authentication measures, which are sometimes common for machines on the same subnet. Ensure that the proper authentication measures are in place and carried out over a secure (encrypted) channel.
Conclusion
IP Spoofing is a problem without an easy solution, since it’s inherent to the design of the TCP/IP suite. Understanding how and why spoofing attacks are used, combined with a few simple prevention methods, can help protect your network from these malicious cloaking and cracking techniques.
Friday, October 26, 2007
Wednesday, October 3, 2007
Oracle FAQ : PL/SQL
Oracle PL/SQL FAQ
Topics
What is PL/SQL and what is it used for?
Should one use PL/SQL or Java to code procedures and triggers?
How can one see if somebody modified any code?
How can one search PL/SQL code for a string/key value?
How can one keep a history of PL/SQL code changes?
How can I protect my PL/SQL source code?
Can one print to the screen from PL/SQL?
Can one read/write files from PL/SQL?
Can one call DDL statements from PL/SQL?
Can one use dynamic SQL statements from PL/SQL?
What is the difference between %TYPE and %ROWTYPE?
What is the result of comparing NULL with NULL?
How does one get the value of a sequence into a PL/SQL variable?
Can one execute an operating system command from PL/SQL?
How does one loop through tables in PL/SQL?
How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
What is a mutating and constraining table?
Can one pass an object/table as an argument to a remote procedure?
Is it better to put code in triggers or procedures? What is the difference?
Is there a PL/SQL Engine in SQL*Plus?
Is there a limit on the size of a PL/SQL block?
What is PL/SQL and what is it used for?
PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.
-------------------------------------------------------------------------------
Should one use PL/SQL or Java to code procedures and triggers?
Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?".
Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments:
PL/SQL:
Data centric and tightly integrated into the database
Proprietary to Oracle and difficult to port to other database systems
Data manipulation is slightly faster in PL/SQL than in Java
Easier to use than Java (depending on your background)
Java:
Open standard, not proprietary to Oracle
Incurs some data conversion overhead between the Database and Java type systems
Java is more difficult to use (depending on your background)
-------------------------------------------------------------------------------
How can one see if somebody modified any code?
Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:
SELECT OBJECT_NAME,
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
STATUS
FROM USER_OBJECTS
WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';
--------------------------------------------------------------------------------
How can one search PL/SQL code for a string/ key value?
The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code.
SELECT TYPE, NAME, LINE
FROM USER_SOURCE
WHERE UPPER(TEXT) LIKE '%&KEYWORD%';
--------------------------------------------------------------------------------
How can one keep a history of PL/SQL code changes?
One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes. Look at this example:
CREATE TABLE SOURCE_HIST -- Create history table
AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
FROM USER_SOURCE WHERE 1=2;
CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table
AFTER CREATE ON SCOTT.SCHEMA -- Change SCOTT to your schema name
DECLARE
BEGIN
if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY', 'TYPE') then
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, user_source.* FROM USER_SOURCE
WHERE TYPE = DICTIONARY_OBJ_TYPE
AND NAME = DICTIONARY_OBJ_NAME;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
/
show errors
--------------------------------------------------------------------------------
How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.
This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.
The syntax is:
wrap iname=myscript.sql oname=xxxx.plb
--------------------------------------------------------------------------------
Can one print to the screen from PL/SQL?
One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command. For example:
set serveroutput on
begin
dbms_output.put_line('Look Ma, I can print from PL/SQL!!!');
end;
/
DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000
If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you haven't cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.
Note that DBMS_OUTPUT doesn't print blank or NULL lines. To overcome this problem, SET SERVEROUTPUT ON FORMAT WRAP; Look at this example with this option first disabled and then enabled:
SQL> SET SERVEROUTPUT ON
SQL> begin
2 dbms_output.put_line('The next line is blank');
3 dbms_output.put_line('');
4 dbms_output.put_line('The above line should be blank');
5 end;
6 /
The next line is blank
The above line should be blank
SQL> SET SERVEROUTPUT ON FORMAT WRAP
SQL> begin
2 dbms_output.put_line('The next line is blank');
3 dbms_output.put_line('');
4 dbms_output.put_line('The above line should be blank');
5 end;
6 /
The next line is blank
The above line should be blank
--------------------------------------------------------------------------------
Can one read/write files from PL/SQL
Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
Copy this example to get started:
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
END;
/
--------------------------------------------------------------------------------
Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDATE" statement. Users running Oracle versions below 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL).
begin
EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
end;
NOTE: The DDL statement in quotes should not be terminated with a semicolon.
--------------------------------------------------------------------------------
Can one use dynamic SQL statements from PL/SQL?
Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that statements are NOT terminated by semicolons:
EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';
-- Using bind variables...
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
-- Returning a cursor...
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:
CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
/
More complex DBMS_SQL example using bind variables:
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
loop
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
exit;
end if;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name: 'v_dname);
end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
when others then
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: 'sqlcode' 'sqlerrm);
END;
/
--------------------------------------------------------------------------------
What is the difference between %TYPE and %ROWTYPE?
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Example:
DECLARE
v_EmpRecord emp%ROWTYPE;
%TYPE is used to declare a field with the same type as that of a specified table's column. Example:
DECLARE
v_EmpNo emp.empno%TYPE;
Back to top of file
--------------------------------------------------------------------------------
What is the result of comparing NULL with NULL?
NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL. Look at this code example to convince yourself.
declare
a number := NULL;
b number := NULL;
begin
if a=b then
dbms_output.put_line('True, NULL = NULL');
elsif a<>b then
dbms_output.put_line('False, NULL <> NULL');
else
dbms_output.put_line('Undefined NULL is neither = nor <> to NULL');
end if;
end;
--------------------------------------------------------------------------------
How does one get the value of a sequence into a PL/SQL variable?
As you might know, one cannot use sequences directly from PL/SQL. Oracle (for some silly reason) prohibits this:
i := sq_sequence.NEXTVAL;
However, one can use embedded SQL statements to obtain sequence values:
select sq_sequence.NEXTVAL into :i from dual;
Thanks to Ronald van Woensel
--------------------------------------------------------------------------------
Can one execute an operating system command from PL/SQL?
There is no direct way to execute operating system commands from PL/SQL in Oracle7. However, one can write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide.
In Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.
--------------------------------------------------------------------------------
How does one loop through tables in PL/SQL?
Look at the following nested loop code example.
DECLARE
CURSOR dept_cur IS
SELECT deptno
FROM dept
ORDER BY deptno;
-- Employee cursor all employees for a dept number
CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
SELECT ename
FROM emp
WHERE deptno = v_dept_no;
BEGIN
FOR dept_rec IN dept_cur LOOP
dbms_output.put_line('Employees in Department 'TO_CHAR(dept_rec.deptno));
FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
dbms_output.put_line('...Employee is 'emp_rec.ename);
END LOOP;
END LOOP;
END;
/
--------------------------------------------------------------------------------
How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular believe, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the rollback segments will be cleared for new transactions, causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this:
FOR records IN my_cursor LOOP
...do some stuff...
COMMIT;
END LOOP;
COMMIT;
... to ...
FOR records IN my_cursor LOOP
...do some stuff...
i := i+1;
IF mod(i, 10000) = 0 THEN -- Commit every 10000 records
COMMIT;
END IF;
END LOOP;
COMMIT;
If you still get ORA-1555 errors, contact your DBA to increase the rollback segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.
--------------------------------------------------------------------------------
I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions:
Grant direct access on the tables to your user. Do not use roles!
GRANT select ON scott.emp TO my_user;
Define your procedures with invoker rights (Oracle 8i and higher);
Move all the tables to one user/schema.
--------------------------------------------------------------------------------
What is a mutating and constraining table?
"Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:
A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) .
A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.
Etc.
--------------------------------------------------------------------------------
Can one pass an object/table as an argument to a remote procedure?
The only way to reference an object type between databases is via a database link. Note that it is not enough to just use "similar" type definitions. Look at this example:
-- Database A: receives a PL/SQL table from database B
CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS
BEGIN
-- do something with TabX from database B
null;
END;
/
-- Database B: sends a PL/SQL table to database A
CREATE OR REPLACE PROCEDURE pcalling IS
TabX DBMS_SQL.VARCHAR2S@DBLINK2;
BEGIN
pcalled@DBLINK2(TabX);
END;
/
--------------------------------------------------------------------------------
Is it better to put code in triggers or procedures? What is the difference?
In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.
-------------------------------------------------------------------------------
Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have an embedded PL/SQL engine. Thus, all your PL/SQL code is sent directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and sent to the database individually.
--------------------------------------------------------------------------------
Is there a limit on the size of a PL/SQL block?
Yes, the max size is not an explicit byte limit, but related to the parse tree that is created when you compile the code. You can run the following select statement to query the size of an existing package or procedure:
SQL> select * from dba_object_size where name = 'procedure_name';
Topics
What is PL/SQL and what is it used for?
Should one use PL/SQL or Java to code procedures and triggers?
How can one see if somebody modified any code?
How can one search PL/SQL code for a string/key value?
How can one keep a history of PL/SQL code changes?
How can I protect my PL/SQL source code?
Can one print to the screen from PL/SQL?
Can one read/write files from PL/SQL?
Can one call DDL statements from PL/SQL?
Can one use dynamic SQL statements from PL/SQL?
What is the difference between %TYPE and %ROWTYPE?
What is the result of comparing NULL with NULL?
How does one get the value of a sequence into a PL/SQL variable?
Can one execute an operating system command from PL/SQL?
How does one loop through tables in PL/SQL?
How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
What is a mutating and constraining table?
Can one pass an object/table as an argument to a remote procedure?
Is it better to put code in triggers or procedures? What is the difference?
Is there a PL/SQL Engine in SQL*Plus?
Is there a limit on the size of a PL/SQL block?
What is PL/SQL and what is it used for?
PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.
-------------------------------------------------------------------------------
Should one use PL/SQL or Java to code procedures and triggers?
Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?".
Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments:
PL/SQL:
Data centric and tightly integrated into the database
Proprietary to Oracle and difficult to port to other database systems
Data manipulation is slightly faster in PL/SQL than in Java
Easier to use than Java (depending on your background)
Java:
Open standard, not proprietary to Oracle
Incurs some data conversion overhead between the Database and Java type systems
Java is more difficult to use (depending on your background)
-------------------------------------------------------------------------------
How can one see if somebody modified any code?
Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:
SELECT OBJECT_NAME,
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
STATUS
FROM USER_OBJECTS
WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';
--------------------------------------------------------------------------------
How can one search PL/SQL code for a string/ key value?
The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code.
SELECT TYPE, NAME, LINE
FROM USER_SOURCE
WHERE UPPER(TEXT) LIKE '%&KEYWORD%';
--------------------------------------------------------------------------------
How can one keep a history of PL/SQL code changes?
One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes. Look at this example:
CREATE TABLE SOURCE_HIST -- Create history table
AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
FROM USER_SOURCE WHERE 1=2;
CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table
AFTER CREATE ON SCOTT.SCHEMA -- Change SCOTT to your schema name
DECLARE
BEGIN
if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY', 'TYPE') then
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, user_source.* FROM USER_SOURCE
WHERE TYPE = DICTIONARY_OBJ_TYPE
AND NAME = DICTIONARY_OBJ_NAME;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
/
show errors
--------------------------------------------------------------------------------
How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.
This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.
The syntax is:
wrap iname=myscript.sql oname=xxxx.plb
--------------------------------------------------------------------------------
Can one print to the screen from PL/SQL?
One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command. For example:
set serveroutput on
begin
dbms_output.put_line('Look Ma, I can print from PL/SQL!!!');
end;
/
DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000
If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you haven't cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.
Note that DBMS_OUTPUT doesn't print blank or NULL lines. To overcome this problem, SET SERVEROUTPUT ON FORMAT WRAP; Look at this example with this option first disabled and then enabled:
SQL> SET SERVEROUTPUT ON
SQL> begin
2 dbms_output.put_line('The next line is blank');
3 dbms_output.put_line('');
4 dbms_output.put_line('The above line should be blank');
5 end;
6 /
The next line is blank
The above line should be blank
SQL> SET SERVEROUTPUT ON FORMAT WRAP
SQL> begin
2 dbms_output.put_line('The next line is blank');
3 dbms_output.put_line('');
4 dbms_output.put_line('The above line should be blank');
5 end;
6 /
The next line is blank
The above line should be blank
--------------------------------------------------------------------------------
Can one read/write files from PL/SQL
Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
Copy this example to get started:
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
END;
/
--------------------------------------------------------------------------------
Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDATE" statement. Users running Oracle versions below 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL).
begin
EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
end;
NOTE: The DDL statement in quotes should not be terminated with a semicolon.
--------------------------------------------------------------------------------
Can one use dynamic SQL statements from PL/SQL?
Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that statements are NOT terminated by semicolons:
EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';
-- Using bind variables...
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
-- Returning a cursor...
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:
CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
/
More complex DBMS_SQL example using bind variables:
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
loop
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
exit;
end if;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name: 'v_dname);
end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
when others then
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: 'sqlcode' 'sqlerrm);
END;
/
--------------------------------------------------------------------------------
What is the difference between %TYPE and %ROWTYPE?
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Example:
DECLARE
v_EmpRecord emp%ROWTYPE;
%TYPE is used to declare a field with the same type as that of a specified table's column. Example:
DECLARE
v_EmpNo emp.empno%TYPE;
Back to top of file
--------------------------------------------------------------------------------
What is the result of comparing NULL with NULL?
NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL. Look at this code example to convince yourself.
declare
a number := NULL;
b number := NULL;
begin
if a=b then
dbms_output.put_line('True, NULL = NULL');
elsif a<>b then
dbms_output.put_line('False, NULL <> NULL');
else
dbms_output.put_line('Undefined NULL is neither = nor <> to NULL');
end if;
end;
--------------------------------------------------------------------------------
How does one get the value of a sequence into a PL/SQL variable?
As you might know, one cannot use sequences directly from PL/SQL. Oracle (for some silly reason) prohibits this:
i := sq_sequence.NEXTVAL;
However, one can use embedded SQL statements to obtain sequence values:
select sq_sequence.NEXTVAL into :i from dual;
Thanks to Ronald van Woensel
--------------------------------------------------------------------------------
Can one execute an operating system command from PL/SQL?
There is no direct way to execute operating system commands from PL/SQL in Oracle7. However, one can write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide.
In Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.
--------------------------------------------------------------------------------
How does one loop through tables in PL/SQL?
Look at the following nested loop code example.
DECLARE
CURSOR dept_cur IS
SELECT deptno
FROM dept
ORDER BY deptno;
-- Employee cursor all employees for a dept number
CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
SELECT ename
FROM emp
WHERE deptno = v_dept_no;
BEGIN
FOR dept_rec IN dept_cur LOOP
dbms_output.put_line('Employees in Department 'TO_CHAR(dept_rec.deptno));
FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
dbms_output.put_line('...Employee is 'emp_rec.ename);
END LOOP;
END LOOP;
END;
/
--------------------------------------------------------------------------------
How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular believe, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the rollback segments will be cleared for new transactions, causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this:
FOR records IN my_cursor LOOP
...do some stuff...
COMMIT;
END LOOP;
COMMIT;
... to ...
FOR records IN my_cursor LOOP
...do some stuff...
i := i+1;
IF mod(i, 10000) = 0 THEN -- Commit every 10000 records
COMMIT;
END IF;
END LOOP;
COMMIT;
If you still get ORA-1555 errors, contact your DBA to increase the rollback segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.
--------------------------------------------------------------------------------
I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions:
Grant direct access on the tables to your user. Do not use roles!
GRANT select ON scott.emp TO my_user;
Define your procedures with invoker rights (Oracle 8i and higher);
Move all the tables to one user/schema.
--------------------------------------------------------------------------------
What is a mutating and constraining table?
"Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:
A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) .
A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.
Etc.
--------------------------------------------------------------------------------
Can one pass an object/table as an argument to a remote procedure?
The only way to reference an object type between databases is via a database link. Note that it is not enough to just use "similar" type definitions. Look at this example:
-- Database A: receives a PL/SQL table from database B
CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS
BEGIN
-- do something with TabX from database B
null;
END;
/
-- Database B: sends a PL/SQL table to database A
CREATE OR REPLACE PROCEDURE pcalling IS
TabX DBMS_SQL.VARCHAR2S@DBLINK2;
BEGIN
pcalled@DBLINK2(TabX);
END;
/
--------------------------------------------------------------------------------
Is it better to put code in triggers or procedures? What is the difference?
In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.
-------------------------------------------------------------------------------
Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have an embedded PL/SQL engine. Thus, all your PL/SQL code is sent directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and sent to the database individually.
--------------------------------------------------------------------------------
Is there a limit on the size of a PL/SQL block?
Yes, the max size is not an explicit byte limit, but related to the parse tree that is created when you compile the code. You can run the following select statement to query the size of an existing package or procedure:
SQL> select * from dba_object_size where name = 'procedure_name';
Calling Stored Database Procedures from Oracle Forms
Calling Stored Database Procedures from Oracle Forms
CONCEPTS
With V7 of the database came PL/SQL V2, which added the new functionality
of database stored procedures. Unfortunately, both SQL*Forms V3.0 and Oracle
Forms V4.0 still use PL/SQL V1. Because of this, when Oracle Forms comes
across a database stored procedure call in your code, it doesn't know how to
handle it.
In order to get around this limitation, the PL/SQL team wrote a set of
procedures that resolve the reference. When Oracle Forms comes across an
identifier, it checks to see if it is a variable, bind variable, table/view,
synonym, sequence, etc. If it is none of these things, the compiler calls a
stub generator to see if it can resolve the identifier as a database function
or procedure. In that case, a stub is generated for syntactical checking, and
the compiler continues. A stub is a PL/SQL procedure or function that has the
same header as the actual procedure or function so forms can perform
syntactical checks. (If the identifier is a package, the stub generator creates
a stub that references each program unit in the package.) From then on, the
stub is used, so the stub generation procedures are only needed at compile
time.
COMMON CAUSES OF ERRORS
(Causes listed below are explained in detail in POTENTIAL PROBLEMS)
PLS-103 Using SQL*Forms 3.0.16.11 or earlier
PLS-201 Server set up incorrectly
Invalid synonym
PLS-302 Procedure uses %type arugment
Extra rows in the PSTUBTBL table
Invalid synonym
PLS-303 Invalid synonym
Using SQL*Forms 3.0.16.11 or earlier
Attempting to use DBMS_STANDARD.RAISE_APPLICATION_ERROR
PLS-306 Missing parameters/incorrect arguments
PLS-313 Invalid synonym
Procedure uses %type argument
Server set up incorrectly
Invalid datatype in procedure
PLS-363 Attempting to assign constant to in/out parameter
PLS-903 Stored procedure has only out argument types
ORA-4031 The SGA is too full to load the PSTUB procedure
FRM-40735 Type mismatch when calling database procedure
CORE DUMP Passing real value through integer
Attempting to declare variable precision or length
Stored procedure has only OUT argument types
DEBUGGING
The first step is to determine where the process is failing. We can do
this by calling the stub generator directly from SQL*Plus.
First off, you should be able to execute your procedure from SQL*Plus. If
you are unable to do this, check the permissions and syntax of your procedure.
When you know it can execute correctly, enter these lines:
VARIABLE a VARCHAR2(2000);
VARIABLE b VARCHAR2(2000);
DELETE FROM sys.pstubtbl;
EXECUTE sys.pstub('', NULL, :a, :b);
PRINT b;
DELETE FROM sys.pstubtbl;
(For, do not prefix it with a username. If
the procedure is in a package, use the package name only instead.)
If PSTUB will not execute, or if you receive an error message referring to
another package, your server may not be set up correctly (see 'Server set
up incorrectly' in POTENTIAL PROBLEMS).
If you get a message about the shared pool, or about the package being invalid
or discarded, the problem may be that the SGA is to fragmented or full to
load the necessary stub generators (see 'The SGA is too full to load the
DIUTIL package' in POTENTIAL PROBLEMS).
If the result of the PRINT b is '$$$ s_notv6Compat', you may be trying to use
%type arguments (see 'Procedure uses %type argument' in POTENTIAL PROBLEMS).
If the result is '$$$ s_subp not found', the problem is that the stub
generator cannot find the procedure. This is probably because you do not
have a synonym set up. A limitation of stub generation is that it cannot
search across users for procedures or packages. You cannot get around this by
prefixing the package or procedure name with the name of the user
(username.procedure). You must declare a public synonym. To do this you need
to both create the synonym, and do a "grant execute on proc to public". If
the result is '$$$ s_other', this generally means that the procedure can't run
in sqlplus independently. The action to take would be to call RDBMS support.
If the stub was correctly generated, PRINT b will return the stub body for your
procedure (PRINT a will give you the package spec if your procedure is in a
package). If this is the case, the problem may be with your version of
SQL*Forms, or may be a problem with your procedure. You can scan the list of
POTENTIAL PROBLEMS to see if you can find the source of the problem.
The following is an example of a successful call to the stub generator:
SQL> create procedure donothing(var1 in number, var2 in out char) is
2 begin
3 null;
4 end;
5 /
Procedure created.
SQL> execute sys.pstub('donothing', null, :a, :b);
PL/SQL procedure successfully completed.
SQL> print b
B
----------------------------------------------------------------------
procedure donothing (VAR1 NUMBER, VAR2 in out CHAR) is begin stproc.in
it('begin donothing(:VAR1, :VAR2); end;'); stproc.bind_i(VAR1); stproc
_bind_io(VAR2); stproc.execute; stproc.retrieve(2, VAR2); end;
POTENTIAL PROBLEMS
*Server set up incorrectly
There are 13 objects that need to be on the server and VALID for it to
create a stub. You can check the status of these objects by running the
following script from SQLPLUS while connected as SYS:
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A12
SELECT object_name, object_type, status
FROM all_objects
WHERE object_name IN ('PSTUBT', 'PSTUBTBL', 'PIDL', 'DIUTIL',
'DIANA', 'STANDARD', 'DBMS_STANDARD')
ORDER BY 1,2;
The correct result of this query is as follows:
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------
DBMS_STANDARD PACKAGE VALID
DBMS_STANDARD PACKAGE BODY VALID
DBMS_STANDARD SYNONYM VALID
DIANA PACKAGE VALID
DIANA PACKAGE BODY VALID
DIUTIL PACKAGE VALID
DIUTIL PACKAGE BODY VALID
PIDL PACKAGE VALID
PIDL PACKAGE BODY VALID
PSTUBT PROCEDURE VALID
PSTUBTBL TABLE VALID
STANDARD PACKAGE VALID
STANDARD PACKAGE BODY VALID
If any of these objects are missing or invalid, run DIUTIL.SQL as SYS
($ORACLE_HOME/rdbms/admin for Unix, \ORAWIN\DBS\ for windows. Note: On
windows, DIUTIL must be run as SYS from SQL*DBA on the server side). A
version of DIUTIL.SQL is usually shipped with the TOOLS--it may or may not
be newer than the server version (the only way to tell is to look at the
comments in the file)--if it is newer, feel free to use it. If you
are still having problems, you may be able to correctly install the
objects by running these scripts in this order: STANDARD.SQL, PIPIDL.SQL,
PIDIAN.SQL, DIUTIL.SQL, and PISTUB.SQL.
*Invalid datatype in procedure
There are only four datatypes supported for use in stored procedures that
are called from Oracle Forms, or called by another procedure that is called
by forms: VARCHAR2, NUMBER, DATE, and BOOLEAN, as documented in the release
notes. If a package has procedures with non-V6 compatible arguments/return
types, it will be dropped from the stub. A stub will still be returned, but
it will be missing these procedures.
*Stored procedure has only OUT argument types
The workaround to this problem is to use IN/OUT arguments instead.
*Extra rows in the PSTUBTBL table
The PSTUBTBL table is a temporary storage space for stubs, and after a stub
has been created, this table should be cleared out. If there are any rows
in PSTUBTBL, this can cause the stub generation process to fail. How do
rows get committed into PSTUBTBL?
1) A user runs the sys.pstub procedure from SQL*Plus, and afterword
they commit. If this is the case, the solution is simple: log
into sqlplus and type "delete from sys.pstubtbl".
2) Two different version of Oracle Forms are referencing the same
procedure. If this is the case, try to upgrade the older version,
and if you are running both V3.0 & V4.0, make sure that you are
using V4.0.12.
3) The stored procedures are being referenced by and older version of
forms (older than V3.0.16.12.7 on UNIX systems). Solution:
upgrade version of SQL*Forms.
*The SGA is too full to load the DIUTIL package
The DIUTIL package must be loaded into the SGA in order for a stub to be
generated. This package takes 32K, and it is not uncommon for heavily
loaded systems to not have that much contiguous SGA available. You have a
couple of options in this case--you can wait until later and try again, but
this is only a temporary fix. If this is a common problem, you can increase
your SGA size, or you can 'pin' the PSTUB procedure into the SGA so that it
won't be aged out. 7.0.15 and later versions of the RDBMS includes a
package called DBMS_SHARED_POOL that will allow you to do this. If this
package is not installed, you can install it by running DBMSPOOL.SQL. To
make sure there is room in the SGA to load the package, you can do this
right after startup, or you can clear the SGA by logging into SQLPLUS as SYS
and typing:
ALTER SYSTEM FLUSH SHARED_POOL;
Do this three times to make sure the SGA is flushed. After that, run PSTUB
on DIUTIL, or generate your form, as this will put the DIUTIL package into
the SGA, then (from SQLPLUS as SYS) type:
EXECUTE DBMS_SHARED_POOL.KEEP('DIUTIL');
*Invalid synonym
Double-check that your synonym is valid, and granted to public.
*Missing parameters/incorrect arguments
Make sure the parameters you specify in Oracle Forms match those specified in
the database procedure.
*Attempting to assign constant to in/out parameter
In/out parameters expect to send a value back to Oracle Forms. You cannot
assign a value to a constant.
*Referencing synonym with access granted through a role
Access must be explicitly granted.
*Procedure uses %type argument (bug 190597)
%type is supported in newer versions of DIUTIL.SQL (but %type must still
evaluate to a V6 compatible datatype). There is a patch available for this
bug for SQL*Forms V3.0.16.12 and later and Oracle Forms V4.0.11 and later.
*Using V3.0.16.11 or earlier
Referencing stored procedures works only in V3.0.16.12 and later.
*Type mismatch when calling database procedure
Check that the datatypes of your parameters match what is specified in the
database stored procedure.
*Passing real value through integer
This type mismatch may cause a core dump.
*Attempting to declare variable precision or length
The precision and length of variables are determined by forms, and should
not be specified in the procedure.
*Attempting to use DBMS_STANDARD.RAISE_APPLICATION_ERROR
This uses a binary_integer datatype, which is not supported.
LIMITATIONS
-----------
*In Oracle Forms, you may reference database stored procedures, but you cannot
debug them from forms. There are two tables called USER_SOURCE and
USER_ERRORS which store information about PL/SQL use and errors.
*You cannot reference database stored package variables or cursors from forms.
*On 7.0.13 and earlier of the RDBMS, Boolean values do not return the correct
values
*On 3.0.16.12 of forms and earlier, there may be some problems with BOOLEAN
values--better to use NUMBER and 0 and 1.
*You cannot look up a remote subprogram via a synonym until RDBMS 7.1.1.
*Performance:
A development group had this experience--"Generation performance seems to
degrade over time in a multi-user system when many forms are being generated
and they all have several stored procedure references. We analyzed this using
tkprof and it turned out that it was the query of SYS.PSTUBTBL which was
slowing everything down. The trace file statistics for the query were huge
(>100000 block visists). All I could think of was that the nature of the
transaction to get stored procedure stub text (i.e. run the PSTUB generator
which inserts into PSTUBTBL, select from PSTUBTBL, rollback) was demanding
large amounts of read consistent data per session, and there was some
(unidentified) problem in the server. We fixed it by dropping and recreating
the SYS.PSUBTBL table, and the stats dropped right down and generation
performance increased."
*References:
There is more information on calling stored procedures from Oracle Forms
in the Oracle Forms release notes.
CONCEPTS
With V7 of the database came PL/SQL V2, which added the new functionality
of database stored procedures. Unfortunately, both SQL*Forms V3.0 and Oracle
Forms V4.0 still use PL/SQL V1. Because of this, when Oracle Forms comes
across a database stored procedure call in your code, it doesn't know how to
handle it.
In order to get around this limitation, the PL/SQL team wrote a set of
procedures that resolve the reference. When Oracle Forms comes across an
identifier, it checks to see if it is a variable, bind variable, table/view,
synonym, sequence, etc. If it is none of these things, the compiler calls a
stub generator to see if it can resolve the identifier as a database function
or procedure. In that case, a stub is generated for syntactical checking, and
the compiler continues. A stub is a PL/SQL procedure or function that has the
same header as the actual procedure or function so forms can perform
syntactical checks. (If the identifier is a package, the stub generator creates
a stub that references each program unit in the package.) From then on, the
stub is used, so the stub generation procedures are only needed at compile
time.
COMMON CAUSES OF ERRORS
(Causes listed below are explained in detail in POTENTIAL PROBLEMS)
PLS-103 Using SQL*Forms 3.0.16.11 or earlier
PLS-201 Server set up incorrectly
Invalid synonym
PLS-302 Procedure uses %type arugment
Extra rows in the PSTUBTBL table
Invalid synonym
PLS-303 Invalid synonym
Using SQL*Forms 3.0.16.11 or earlier
Attempting to use DBMS_STANDARD.RAISE_APPLICATION_ERROR
PLS-306 Missing parameters/incorrect arguments
PLS-313 Invalid synonym
Procedure uses %type argument
Server set up incorrectly
Invalid datatype in procedure
PLS-363 Attempting to assign constant to in/out parameter
PLS-903 Stored procedure has only out argument types
ORA-4031 The SGA is too full to load the PSTUB procedure
FRM-40735 Type mismatch when calling database procedure
CORE DUMP Passing real value through integer
Attempting to declare variable precision or length
Stored procedure has only OUT argument types
DEBUGGING
The first step is to determine where the process is failing. We can do
this by calling the stub generator directly from SQL*Plus.
First off, you should be able to execute your procedure from SQL*Plus. If
you are unable to do this, check the permissions and syntax of your procedure.
When you know it can execute correctly, enter these lines:
VARIABLE a VARCHAR2(2000);
VARIABLE b VARCHAR2(2000);
DELETE FROM sys.pstubtbl;
EXECUTE sys.pstub('
PRINT b;
DELETE FROM sys.pstubtbl;
(For
the procedure is in a package, use the package name only instead.)
If PSTUB will not execute, or if you receive an error message referring to
another package, your server may not be set up correctly (see 'Server set
up incorrectly' in POTENTIAL PROBLEMS).
If you get a message about the shared pool, or about the package being invalid
or discarded, the problem may be that the SGA is to fragmented or full to
load the necessary stub generators (see 'The SGA is too full to load the
DIUTIL package' in POTENTIAL PROBLEMS).
If the result of the PRINT b is '$$$ s_notv6Compat', you may be trying to use
%type arguments (see 'Procedure uses %type argument' in POTENTIAL PROBLEMS).
If the result is '$$$ s_subp not found', the problem is that the stub
generator cannot find the procedure. This is probably because you do not
have a synonym set up. A limitation of stub generation is that it cannot
search across users for procedures or packages. You cannot get around this by
prefixing the package or procedure name with the name of the user
(username.procedure). You must declare a public synonym. To do this you need
to both create the synonym, and do a "grant execute on proc to public". If
the result is '$$$ s_other', this generally means that the procedure can't run
in sqlplus independently. The action to take would be to call RDBMS support.
If the stub was correctly generated, PRINT b will return the stub body for your
procedure (PRINT a will give you the package spec if your procedure is in a
package). If this is the case, the problem may be with your version of
SQL*Forms, or may be a problem with your procedure. You can scan the list of
POTENTIAL PROBLEMS to see if you can find the source of the problem.
The following is an example of a successful call to the stub generator:
SQL> create procedure donothing(var1 in number, var2 in out char) is
2 begin
3 null;
4 end;
5 /
Procedure created.
SQL> execute sys.pstub('donothing', null, :a, :b);
PL/SQL procedure successfully completed.
SQL> print b
B
----------------------------------------------------------------------
procedure donothing (VAR1 NUMBER, VAR2 in out CHAR) is begin stproc.in
it('begin donothing(:VAR1, :VAR2); end;'); stproc.bind_i(VAR1); stproc
_bind_io(VAR2); stproc.execute; stproc.retrieve(2, VAR2); end;
POTENTIAL PROBLEMS
*Server set up incorrectly
There are 13 objects that need to be on the server and VALID for it to
create a stub. You can check the status of these objects by running the
following script from SQLPLUS while connected as SYS:
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A12
SELECT object_name, object_type, status
FROM all_objects
WHERE object_name IN ('PSTUBT', 'PSTUBTBL', 'PIDL', 'DIUTIL',
'DIANA', 'STANDARD', 'DBMS_STANDARD')
ORDER BY 1,2;
The correct result of this query is as follows:
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------
DBMS_STANDARD PACKAGE VALID
DBMS_STANDARD PACKAGE BODY VALID
DBMS_STANDARD SYNONYM VALID
DIANA PACKAGE VALID
DIANA PACKAGE BODY VALID
DIUTIL PACKAGE VALID
DIUTIL PACKAGE BODY VALID
PIDL PACKAGE VALID
PIDL PACKAGE BODY VALID
PSTUBT PROCEDURE VALID
PSTUBTBL TABLE VALID
STANDARD PACKAGE VALID
STANDARD PACKAGE BODY VALID
If any of these objects are missing or invalid, run DIUTIL.SQL as SYS
($ORACLE_HOME/rdbms/admin for Unix, \ORAWIN\DBS\ for windows. Note: On
windows, DIUTIL must be run as SYS from SQL*DBA on the server side). A
version of DIUTIL.SQL is usually shipped with the TOOLS--it may or may not
be newer than the server version (the only way to tell is to look at the
comments in the file)--if it is newer, feel free to use it. If you
are still having problems, you may be able to correctly install the
objects by running these scripts in this order: STANDARD.SQL, PIPIDL.SQL,
PIDIAN.SQL, DIUTIL.SQL, and PISTUB.SQL.
*Invalid datatype in procedure
There are only four datatypes supported for use in stored procedures that
are called from Oracle Forms, or called by another procedure that is called
by forms: VARCHAR2, NUMBER, DATE, and BOOLEAN, as documented in the release
notes. If a package has procedures with non-V6 compatible arguments/return
types, it will be dropped from the stub. A stub will still be returned, but
it will be missing these procedures.
*Stored procedure has only OUT argument types
The workaround to this problem is to use IN/OUT arguments instead.
*Extra rows in the PSTUBTBL table
The PSTUBTBL table is a temporary storage space for stubs, and after a stub
has been created, this table should be cleared out. If there are any rows
in PSTUBTBL, this can cause the stub generation process to fail. How do
rows get committed into PSTUBTBL?
1) A user runs the sys.pstub procedure from SQL*Plus, and afterword
they commit. If this is the case, the solution is simple: log
into sqlplus and type "delete from sys.pstubtbl".
2) Two different version of Oracle Forms are referencing the same
procedure. If this is the case, try to upgrade the older version,
and if you are running both V3.0 & V4.0, make sure that you are
using V4.0.12.
3) The stored procedures are being referenced by and older version of
forms (older than V3.0.16.12.7 on UNIX systems). Solution:
upgrade version of SQL*Forms.
*The SGA is too full to load the DIUTIL package
The DIUTIL package must be loaded into the SGA in order for a stub to be
generated. This package takes 32K, and it is not uncommon for heavily
loaded systems to not have that much contiguous SGA available. You have a
couple of options in this case--you can wait until later and try again, but
this is only a temporary fix. If this is a common problem, you can increase
your SGA size, or you can 'pin' the PSTUB procedure into the SGA so that it
won't be aged out. 7.0.15 and later versions of the RDBMS includes a
package called DBMS_SHARED_POOL that will allow you to do this. If this
package is not installed, you can install it by running DBMSPOOL.SQL. To
make sure there is room in the SGA to load the package, you can do this
right after startup, or you can clear the SGA by logging into SQLPLUS as SYS
and typing:
ALTER SYSTEM FLUSH SHARED_POOL;
Do this three times to make sure the SGA is flushed. After that, run PSTUB
on DIUTIL, or generate your form, as this will put the DIUTIL package into
the SGA, then (from SQLPLUS as SYS) type:
EXECUTE DBMS_SHARED_POOL.KEEP('DIUTIL');
*Invalid synonym
Double-check that your synonym is valid, and granted to public.
*Missing parameters/incorrect arguments
Make sure the parameters you specify in Oracle Forms match those specified in
the database procedure.
*Attempting to assign constant to in/out parameter
In/out parameters expect to send a value back to Oracle Forms. You cannot
assign a value to a constant.
*Referencing synonym with access granted through a role
Access must be explicitly granted.
*Procedure uses %type argument (bug 190597)
%type is supported in newer versions of DIUTIL.SQL (but %type must still
evaluate to a V6 compatible datatype). There is a patch available for this
bug for SQL*Forms V3.0.16.12 and later and Oracle Forms V4.0.11 and later.
*Using V3.0.16.11 or earlier
Referencing stored procedures works only in V3.0.16.12 and later.
*Type mismatch when calling database procedure
Check that the datatypes of your parameters match what is specified in the
database stored procedure.
*Passing real value through integer
This type mismatch may cause a core dump.
*Attempting to declare variable precision or length
The precision and length of variables are determined by forms, and should
not be specified in the procedure.
*Attempting to use DBMS_STANDARD.RAISE_APPLICATION_ERROR
This uses a binary_integer datatype, which is not supported.
LIMITATIONS
-----------
*In Oracle Forms, you may reference database stored procedures, but you cannot
debug them from forms. There are two tables called USER_SOURCE and
USER_ERRORS which store information about PL/SQL use and errors.
*You cannot reference database stored package variables or cursors from forms.
*On 7.0.13 and earlier of the RDBMS, Boolean values do not return the correct
values
*On 3.0.16.12 of forms and earlier, there may be some problems with BOOLEAN
values--better to use NUMBER and 0 and 1.
*You cannot look up a remote subprogram via a synonym until RDBMS 7.1.1.
*Performance:
A development group had this experience--"Generation performance seems to
degrade over time in a multi-user system when many forms are being generated
and they all have several stored procedure references. We analyzed this using
tkprof and it turned out that it was the query of SYS.PSTUBTBL which was
slowing everything down. The trace file statistics for the query were huge
(>100000 block visists). All I could think of was that the nature of the
transaction to get stored procedure stub text (i.e. run the PSTUB generator
which inserts into PSTUBTBL, select from PSTUBTBL, rollback) was demanding
large amounts of read consistent data per session, and there was some
(unidentified) problem in the server. We fixed it by dropping and recreating
the SYS.PSUBTBL table, and the stats dropped right down and generation
performance increased."
*References:
There is more information on calling stored procedures from Oracle Forms
in the Oracle Forms release notes.
Saturday, September 29, 2007
Setting Date in Linux
Introduction
This document explains how to set your computer's clock from Linux, how to set your timezone, and other stuff related to Linux and how it does its time-keeping.
Your computer has two timepieces; a battery-backed one that is always running (the ``hardware'', ``BIOS'', or ``CMOS'' clock), and another that is maintained by the operating system currently running on your computer (the ``system'' clock). The hardware clock is generally only used to set the system clock when your operating system boots, and then from that point until you reboot or turn off your system, the system clock is the one used to keep track of time.
On Linux systems, you have a choice of keeping the hardware clock in UTC/GMT time or local time. The preferred option is to keep it in UTC because then daylight savings can be automatically accounted for. The only disadvantage with keeping the hardware clock in UTC is that if you dual boot with an operating system (such as DOS) that expects the hardware clock to be set to local time, the time will always be wrong in that operating system.
Setting your timezone
The timezone under Linux is set by a symbolic link from /etc/localtime[1] to a file in the /usr/share/zoneinfo[2] directory that corresponds with what timezone you are in. For example, since I'm in South Australia, /etc/localtime is a symlink /usr/share/zoneinfo/Australia/South. To set this link, type:
ln -sf ../usr/share/zoneinfo/your/zone /etc/localtime
Replace your/zone with something like Australia/NSW or Australia/Perth. Have a look in the directories under /usr/share/zoneinfo to see what timezones are available.
[1] This assumes that /usr/share/zoneinfo is linked to /etc/localtime as it is under Red Hat Linux.
[2] On older systems, you'll find that /usr/lib/zoneinfo is used instead of /usr/share/zoneinfo. See also the later section ``The time in some applications is wrong''.
Setting UTC or local time
When Linux boots, one of the initialisation scripts will run the /sbin/hwclock program to copy the current hardware clock time to the system clock. hwclock will assume the hardware clock is set to local time unless it is run with the --utc switch. Rather than editing the startup script, under Red Hat Linux you should edit the /etc/sysconfig/clock file and change the ``UTC'' line to either ``UTC=true'' or ``UTC=false'' as appropriate.
Setting the system clock
To set the system clock under Linux, use the date command. As an example, to set the current time and date to July 31, 11:16pm, type ``date 07312316'' (note that the time is given in 24 hour notation). If you wanted to change the year as well, you could type ``date 073123161998''. To set the seconds as well, type ``date 07312316.30'' or ``date 073123161998.30''. To see what Linux thinks the current local time is, run date with no arguments.
Setting the hardware clock
To set the hardware clock, my favourite way is to set the system clock first, and then set the hardware clock to the current system clock by typing ``/sbin/hwclock --systohc'' (or ``/sbin/hwclock --systohc --utc'' if you are keeping the hardware clock in UTC). To see what the hardware clock is currently set to, run hwclock with no arguments. If the hardware clock is in UTC and you want to see the local equivalent, type ``/sbin/hwclock --utc''
The time in some applications is wrong
If some applications (such as date) display the correct time, but others don't, and you are running Red Hat Linux 5.0 or 5.1, you most likely have run into a bug caused by a move of the timezone information from /usr/lib/zoneinfo to /usr/share/zoneinfo. The fix is to create a symbolic link from /usr/lib/zoneinfo to /usr/share/zoneinfo: ``ln -s ../share/zoneinfo /usr/lib/zoneinfo''.
Summary
/etc/sysconfig/clock sets whether the hardware clock is stored as UTC or local time.
Symlink /etc/localtime to /usr/share/zoneinfo/... to set your timezone.
Run ``date MMDDhhmm'' to set the current system date/time.
Type ``/sbin/hwclock --systohc [--utc]'' to set the hardware clock.
Other interesting notes
The Linux kernel always stores and calculates time as the number of seconds since midnight of the 1st of January 1970 UTC regardless of whether your hardware clock is stored as UTC or not. Conversions to your local time are done at run-time. One neat thing about this is that if someone is using your computer from a different timezone, they can set the TZ environment variable and all dates and times will appear correct for their timezone.
If the number of seconds since the 1st of January 1970 UTC is stored as an signed 32-bit integer (as it is on your Linux/Intel system), your clock will stop working sometime on the year 2038. Linux has no inherent Y2K problem, but it does have a year 2038 problem. Hopefully we'll all be running Linux on 64-bit systems by then. 64-bit integers will keep our clocks running quite well until aproximately the year 292271-million.
Other programs worth looking at
rdate - get the current time from a remote machine; can be used to set the system time.
xntpd - like rdate, but it's extremely accurate and you need a permanent 'net connection. xntpd runs continuously and accounts for things like network delay and clock drift, but there's also a program (ntpdate) included that just sets the current time like rdate does.
This document explains how to set your computer's clock from Linux, how to set your timezone, and other stuff related to Linux and how it does its time-keeping.
Your computer has two timepieces; a battery-backed one that is always running (the ``hardware'', ``BIOS'', or ``CMOS'' clock), and another that is maintained by the operating system currently running on your computer (the ``system'' clock). The hardware clock is generally only used to set the system clock when your operating system boots, and then from that point until you reboot or turn off your system, the system clock is the one used to keep track of time.
On Linux systems, you have a choice of keeping the hardware clock in UTC/GMT time or local time. The preferred option is to keep it in UTC because then daylight savings can be automatically accounted for. The only disadvantage with keeping the hardware clock in UTC is that if you dual boot with an operating system (such as DOS) that expects the hardware clock to be set to local time, the time will always be wrong in that operating system.
Setting your timezone
The timezone under Linux is set by a symbolic link from /etc/localtime[1] to a file in the /usr/share/zoneinfo[2] directory that corresponds with what timezone you are in. For example, since I'm in South Australia, /etc/localtime is a symlink /usr/share/zoneinfo/Australia/South. To set this link, type:
ln -sf ../usr/share/zoneinfo/your/zone /etc/localtime
Replace your/zone with something like Australia/NSW or Australia/Perth. Have a look in the directories under /usr/share/zoneinfo to see what timezones are available.
[1] This assumes that /usr/share/zoneinfo is linked to /etc/localtime as it is under Red Hat Linux.
[2] On older systems, you'll find that /usr/lib/zoneinfo is used instead of /usr/share/zoneinfo. See also the later section ``The time in some applications is wrong''.
Setting UTC or local time
When Linux boots, one of the initialisation scripts will run the /sbin/hwclock program to copy the current hardware clock time to the system clock. hwclock will assume the hardware clock is set to local time unless it is run with the --utc switch. Rather than editing the startup script, under Red Hat Linux you should edit the /etc/sysconfig/clock file and change the ``UTC'' line to either ``UTC=true'' or ``UTC=false'' as appropriate.
Setting the system clock
To set the system clock under Linux, use the date command. As an example, to set the current time and date to July 31, 11:16pm, type ``date 07312316'' (note that the time is given in 24 hour notation). If you wanted to change the year as well, you could type ``date 073123161998''. To set the seconds as well, type ``date 07312316.30'' or ``date 073123161998.30''. To see what Linux thinks the current local time is, run date with no arguments.
Setting the hardware clock
To set the hardware clock, my favourite way is to set the system clock first, and then set the hardware clock to the current system clock by typing ``/sbin/hwclock --systohc'' (or ``/sbin/hwclock --systohc --utc'' if you are keeping the hardware clock in UTC). To see what the hardware clock is currently set to, run hwclock with no arguments. If the hardware clock is in UTC and you want to see the local equivalent, type ``/sbin/hwclock --utc''
The time in some applications is wrong
If some applications (such as date) display the correct time, but others don't, and you are running Red Hat Linux 5.0 or 5.1, you most likely have run into a bug caused by a move of the timezone information from /usr/lib/zoneinfo to /usr/share/zoneinfo. The fix is to create a symbolic link from /usr/lib/zoneinfo to /usr/share/zoneinfo: ``ln -s ../share/zoneinfo /usr/lib/zoneinfo''.
Summary
/etc/sysconfig/clock sets whether the hardware clock is stored as UTC or local time.
Symlink /etc/localtime to /usr/share/zoneinfo/... to set your timezone.
Run ``date MMDDhhmm'' to set the current system date/time.
Type ``/sbin/hwclock --systohc [--utc]'' to set the hardware clock.
Other interesting notes
The Linux kernel always stores and calculates time as the number of seconds since midnight of the 1st of January 1970 UTC regardless of whether your hardware clock is stored as UTC or not. Conversions to your local time are done at run-time. One neat thing about this is that if someone is using your computer from a different timezone, they can set the TZ environment variable and all dates and times will appear correct for their timezone.
If the number of seconds since the 1st of January 1970 UTC is stored as an signed 32-bit integer (as it is on your Linux/Intel system), your clock will stop working sometime on the year 2038. Linux has no inherent Y2K problem, but it does have a year 2038 problem. Hopefully we'll all be running Linux on 64-bit systems by then. 64-bit integers will keep our clocks running quite well until aproximately the year 292271-million.
Other programs worth looking at
rdate - get the current time from a remote machine; can be used to set the system time.
xntpd - like rdate, but it's extremely accurate and you need a permanent 'net connection. xntpd runs continuously and accounts for things like network delay and clock drift, but there's also a program (ntpdate) included that just sets the current time like rdate does.
Tips And Tricks in Linux Administration
This is a compilation of tips and tricks I got from reading the mailing list from CentOS and PLUG which I will update from time to time.
1. How to get users list in a linux machine.
As local user
# cat /etc/passwd
- Seperated by username, password, UID, GID, home directory, shell
# cat /etc/group
- Use this to see group information.
getent passwd
- This handles everything nsswitch.conf does (NIS, LDAP, xyzzy, whatever).
getent passwd cut -d: -f1 sort -u
- To get just a list of usernames
2. To check what is the IP address of the DHCP server where assigned IP leased from:
For CentOS 5, the command:
# grep dhcp-server-identifier /var/lib/dhclient/dhclient-eth0.leases
or for CentOS 4, the command:
# grep dhcp-server-identifier /var/lib/dhcp/dhclient-eth0.leases
To list all PCI device:
/sbin/lspci
To determine the change log of installed software:
rpm -qi –changelog
yum install yum-changelog
Then …
man yum-changelog
or
repoquery –changelog foo
3. Robert Moskowitz Tips on Private Network IP Addresses:
If you use addresses from the 192.168 range, avoid the nets:
192.168.0.0, 192.168.1.0, 192.168.2.0, and 192.168.100.0 These are commonly used by various ‘appliances’. Choose something ‘wierd’ like 192.168.49.0 (or some other prime number less than 255). Though I tend to use 192.168.64.0, 192.168.96.0, or 192.168.128.0
Also avoid the 172.16 and 10. ranges. These are commonly used in Company internal private networks, and if you want to ‘VPN’ to them, it an cause unwanted results (like it won’t work).
This is general advice to anyone using private addresses for small usages.
4. To Get Memory Usage and Information:
free - for summary information
cat /proc/meminfo - for detailed information
5. To get hardware info:
/usr/sbin/dmidecode
6. To test for disk I/O performance:
Download ‘disktest’ utility from Linux Test Project(LTP) and do the ff:
Sequential reads:
disktest -B 4k -h 1 -I BD -K 4 -p l -P T -T 300 -r /dev/sdX
Sequential writes:
disktest -B 4k -h 1 -I BD -K 4 -p l -P T -T 300 -w /dev/sdX
Random writes:
disktest -B 4k -h 1 -I BD -K 4 -p r -P T -T 300 -r /dev/sdX
Random writes:
disktest -B 4k -h 1 -I BD -K 4 -p r -P T -T 300 -w /dev/sdX
Description of the options used:
-B 4k = 4k block ios
-h 1 = 1 second heartbeat
-I BD = block device, direct io
-K 4 = 4 threads, or 4 outstanding/overlapping ios, typical pattern
(use -K 1 for the raw performance of single drive, aka dd type output)
-p= io type, l=linear, r=random
-P T = output metrics type “Throughput”
-T 300 = duration of test 300 seconds
-r = read
-w = write
These tests will run across the whole disk/partition and the write
tests WILL BE DESTRUCTIVE so be warned!
7. To determine PCI information and replacing LAN card info:
/sbin/lspci
In /etc/modprobe.conf
Put or replace alias entries for LAN card and restart network.
8. To get driver info:
/sbin/modinfo 3w-9xxx
1. How to get users list in a linux machine.
As local user
# cat /etc/passwd
- Seperated by username, password, UID, GID, home directory, shell
# cat /etc/group
- Use this to see group information.
getent passwd
- This handles everything nsswitch.conf does (NIS, LDAP, xyzzy, whatever).
getent passwd cut -d: -f1 sort -u
- To get just a list of usernames
2. To check what is the IP address of the DHCP server where assigned IP leased from:
For CentOS 5, the command:
# grep dhcp-server-identifier /var/lib/dhclient/dhclient-eth0.leases
or for CentOS 4, the command:
# grep dhcp-server-identifier /var/lib/dhcp/dhclient-eth0.leases
To list all PCI device:
/sbin/lspci
To determine the change log of installed software:
rpm -qi –changelog
yum install yum-changelog
Then …
man yum-changelog
or
repoquery –changelog foo
3. Robert Moskowitz Tips on Private Network IP Addresses:
If you use addresses from the 192.168 range, avoid the nets:
192.168.0.0, 192.168.1.0, 192.168.2.0, and 192.168.100.0 These are commonly used by various ‘appliances’. Choose something ‘wierd’ like 192.168.49.0 (or some other prime number less than 255). Though I tend to use 192.168.64.0, 192.168.96.0, or 192.168.128.0
Also avoid the 172.16 and 10. ranges. These are commonly used in Company internal private networks, and if you want to ‘VPN’ to them, it an cause unwanted results (like it won’t work).
This is general advice to anyone using private addresses for small usages.
4. To Get Memory Usage and Information:
free - for summary information
cat /proc/meminfo - for detailed information
5. To get hardware info:
/usr/sbin/dmidecode
6. To test for disk I/O performance:
Download ‘disktest’ utility from Linux Test Project(LTP) and do the ff:
Sequential reads:
disktest -B 4k -h 1 -I BD -K 4 -p l -P T -T 300 -r /dev/sdX
Sequential writes:
disktest -B 4k -h 1 -I BD -K 4 -p l -P T -T 300 -w /dev/sdX
Random writes:
disktest -B 4k -h 1 -I BD -K 4 -p r -P T -T 300 -r /dev/sdX
Random writes:
disktest -B 4k -h 1 -I BD -K 4 -p r -P T -T 300 -w /dev/sdX
Description of the options used:
-B 4k = 4k block ios
-h 1 = 1 second heartbeat
-I BD = block device, direct io
-K 4 = 4 threads, or 4 outstanding/overlapping ios, typical pattern
(use -K 1 for the raw performance of single drive, aka dd type output)
-p
-P T = output metrics type “Throughput”
-T 300 = duration of test 300 seconds
-r = read
-w = write
These tests will run across the whole disk/partition and the write
tests WILL BE DESTRUCTIVE so be warned!
7. To determine PCI information and replacing LAN card info:
/sbin/lspci
In /etc/modprobe.conf
Put or replace alias entries for LAN card and restart network.
8. To get driver info:
/sbin/modinfo 3w-9xxx
Summary of VI Editor commands
This list is a summary of VI commands, categorized by function. There may be other commands available, so check the on-line manual on VI. For easy reference, you can save this file as text and delete any commands you don't think you would use and print out the resulting shorter file.
Cutting and Pasting/Deleting text
"
Specify a buffer to be used any of the commands using buffers. Follow the " with a letter or a number, which corresponds to a buffer.
D
Delete to the end of the line from the current cursor position.
P
Paste the specified buffer before the current cursor position or line. If no buffer is specified (with the " command.) then 'P' uses the general buffer.
X
Delete the character before the cursor.
Y
Yank the current line into the specified buffer. If no buffer is specified, then the general buffer is used.
d
Delete until where. "dd" deletes the current line. A count deletes that many lines. Whatever is deleted is placed into the buffer specified with the " command. If no buffer is specified, then the general buffer is used.
p
Paste the specified buffer after the current cursor position or line. If no buffer is specified (with the " command.) then 'p' uses the general buffer.
x
Delete character under the cursor. A count tells how many characters to delete. The characters will be deleted after the cursor.
y
Yank until , putting the result into a buffer. "yy" yanks the current line. a count yanks that many lines. The buffer can be specified with the " command. If no buffer is specified, then the general buffer is used.
Inserting New Text
A
Append at the end of the current line.
I
Insert from the beginning of a line.
O
(letter oh) Enter insert mode in a new line above the current cursor position.
a
Enter insert mode, the characters typed in will be inserted after the current cursor position. A count inserts all the text that had been inserted that many times.
i
Enter insert mode, the characters typed in will be inserted before the current cursor position. A count inserts all the text that had been inserted that many times.
o
Enter insert mode in a new line below the current cursor position.
Moving the Cursor Within the File
^B
Scroll backwards one page. A count scrolls that many pages.
^D
Scroll forwards half a window. A count scrolls that many lines.
^F
Scroll forwards one page. A count scrolls that many pages.
^H
Move the cursor one space to the left. A count moves that many spaces.
^J
Move the cursor down one line in the same column. A count moves that many lines down.
^M
Move to the first character on the next line.
^N
Move the cursor down one line in the same column. A count moves that many lines down.
^P
Move the cursor up one line in the same column. A count moves that many lines up.
^U
Scroll backwards half a window. A count scrolls that many lines.
$
Move the cursor to the end of the current line. A count moves to the end of the following lines.
%
Move the cursor to the matching parenthesis or brace.
^
Move the cursor to the first non-whitespace character.
(
Move the cursor to the beginning of a sentence.
)
Move the cursor to the beginning of the next sentence.
{
Move the cursor to the preceding paragraph.
}
Move the cursor to the next paragraph.
Move the cursor to the column specified by the count.
+
Move the cursor to the first non-whitespace character in the next line.
-
Move the cursor to the first non-whitespace character in the previous line.
_
Move the cursor to the first non-whitespace character in the current line.
0
(Zero) Move the cursor to the first column of the current line.
B
Move the cursor back one word, skipping over punctuation.
E
Move forward to the end of a word, skipping over punctuation.
G
Go to the line number specified as the count. If no count is given, then go to the end of the file.
H
Move the cursor to the first non-whitespace character on the top of the screen.
L
Move the cursor to the first non-whitespace character on the bottom of the screen.
M
Move the cursor to the first non-whitespace character on the middle of the screen.
W
Move forward to the beginning of a word, skipping over punctuation.
b
Move the cursor back one word. If the cursor is in the middle of a word, move the cursor to the first character of that word.
e
Move the cursor forward one word. If the cursor is in the middle of a word, move the cursor to the last character of that word.
h
Move the cursor to the left one character position.
j
Move the cursor down one line.
k
Move the cursor up one line.
l
Move the cursor to the right one character position.
w
Move the cursor forward one word. If the cursor is in the middle of a word, move the cursor to the first character of the next word.
Moving the Cursor Around the Screen
^E
Scroll forwards one line. A count scrolls that many lines.
^Y
Scroll backwards one line. A count scrolls that many lines.
z
Redraw the screen with the following options. "z" puts the current line on the top of the screen; "z." puts the current line on the center of the screen; and "z-" puts the current line on the bottom of the screen. If you specify a count before the 'z' command, it changes the current line to the line specified. For example, "16z." puts line 16 on the center of the screen.
Replacing Text
C
Change to the end of the line from the current cursor position.
R
Replace characters on the screen with a set of characters entered, ending with the Escape key.
S
Change an entire line.
c
Change until . "cc" changes the current line. A count changes that many lines.
r
Replace one character under the cursor. Specify a count to replace a number of characters.
s
Substitute one character under the cursor, and go into insert mode. Specify a count to substitute a number of characters. A dollar sign ($) will be put at the last character to be substituted.
Searching for Text or Characters
,
Repeat the last f, F, t or T command in the reverse direction.
/
Search the file downwards for the string specified after the /.
;
Repeat the last f, F, t or T command.
?
Search the file upwards for the string specified after the ?.
F
Search the current line backwards for the character specified after the 'F' command. If found, move the cursor to the position.
N
Repeat the last search given by '/' or '?', except in the reverse direction.
T
Search the current line backwards for the character specified after the 'T' command, and move to the column after the if it's found.
f
Search the current line for the character specified after the 'f' command. If found, move the cursor to the position.
n
Repeat last search given by '/' or '?'.
t
Search the current line for the character specified after the 't' command, and move to the column before the character if it's found.
Manipulating Character/Line Formatting
~
Switch the case of the character under the cursor.
<>
Shift the lines up to where to the right by one shiftwidth.
">>"
shifts the current line to the right, and can be specified with a count.
J
Join the current line with the next one. A count joins that many lines.
Saving and Quitting
^\
Quit out of "VI" mode and go into "EX" mode. The EX editor is the line editor VI is build upon. The EX command to get back into VI is ":vi".
Q
Quit out of "VI" mode and go into "EX" mode. The ex editor is a line-by-line editor. The EX command to get back into VI is ":vi".
ZZ
Exit the editor, saving if any changes were made.
Miscellany
^G
Show the current filename and the status.
^L
Clear and redraw the screen.
^R
Redraw the screen removing false lines.
^[
Escape key. Cancels partially formed command.
^^
Go back to the last file edited.
!
Execute a shell. If a is specified, the program which is executed using ! uses the specified line(s) as standard input, and will replace those lines with the standard output of the program executed. "!!" executes a program using the current line as input. For example, "!4jsort" will take five lines from the current cursor position and execute sort. After typing the command, there will be a single exclamation point where you can type the command in.
&
Repeat the previous ":s" command.
.
Repeat the last command that modified the file.
:
Begin typing an EX editor command. The command is executed once the user types return. (See section below.)
@
Type the command stored in the specified buffer.
U
Restore the current line to the state it was in before the cursor entered the line.
m
Mark the current position with the character specified after the 'm' command.
u
Undo the last change to the file. Typing 'u' again will re-do the change.
EX Commands
The VI editor is built upon another editor, called EX. The EX editor only edits by line. From the VI editor you use the : command to start entering an EX command. This list given here is not complete, but the commands given are the more commonly used. If more than one line is to be modified by certain commands (such as ":s" and ":w" ) the range must be specified before the command. For example, to substitute lines 3 through 15, the command is ":3,15s/from/this/g".
:ab string strings
Abbreviation. If a word is typed in VI corresponding to string1, the editor automatically inserts the corresponding words. For example, the abbreviation ":ab usa United States of America" would insert the words, "United States of America" whenever the word "usa" is typed in.
:map keys new_seq
Mapping. This lets you map a key or a sequence of keys to another key or a sequence of keys.
:q
Quit VI. If there have been changes made, the editor will issue a warning message.
:q!
Quit VI without saving changes.
:s/pattern/to_pattern/options
Substitute. This substitutes the specified pattern with the string in the to_pattern. Without options, it only substitutes the first occurence of the pattern. If a 'g' is specified, then all occurences are substituted. For example, the command ":1,$s/Dwayne/Dwight/g" substitutes all occurences of "Dwayne" to "Dwight".
:set [all]
Sets some customizing options to VI and EX. The ":set all" command gives all the possible options. (See the section on customizing VI for some options.)
:una string
Removes the abbreviation previously defined by ":ab".
:unm keys
Removes the remove mapping defined by ":map".
:vi filename
Starts editing a new file. If changes have not been saved, the editor will give you a warning.
:w
Write out the current file.
:w filename
Write the buffer to the filename specified.
:w >> filename
Append the contents of the buffer to the filename.
:wq
Write the buffer and quit.
Cutting and Pasting/Deleting text
"
Specify a buffer to be used any of the commands using buffers. Follow the " with a letter or a number, which corresponds to a buffer.
D
Delete to the end of the line from the current cursor position.
P
Paste the specified buffer before the current cursor position or line. If no buffer is specified (with the " command.) then 'P' uses the general buffer.
X
Delete the character before the cursor.
Y
Yank the current line into the specified buffer. If no buffer is specified, then the general buffer is used.
d
Delete until where. "dd" deletes the current line. A count deletes that many lines. Whatever is deleted is placed into the buffer specified with the " command. If no buffer is specified, then the general buffer is used.
p
Paste the specified buffer after the current cursor position or line. If no buffer is specified (with the " command.) then 'p' uses the general buffer.
x
Delete character under the cursor. A count tells how many characters to delete. The characters will be deleted after the cursor.
y
Yank until , putting the result into a buffer. "yy" yanks the current line. a count yanks that many lines. The buffer can be specified with the " command. If no buffer is specified, then the general buffer is used.
Inserting New Text
A
Append at the end of the current line.
I
Insert from the beginning of a line.
O
(letter oh) Enter insert mode in a new line above the current cursor position.
a
Enter insert mode, the characters typed in will be inserted after the current cursor position. A count inserts all the text that had been inserted that many times.
i
Enter insert mode, the characters typed in will be inserted before the current cursor position. A count inserts all the text that had been inserted that many times.
o
Enter insert mode in a new line below the current cursor position.
Moving the Cursor Within the File
^B
Scroll backwards one page. A count scrolls that many pages.
^D
Scroll forwards half a window. A count scrolls that many lines.
^F
Scroll forwards one page. A count scrolls that many pages.
^H
Move the cursor one space to the left. A count moves that many spaces.
^J
Move the cursor down one line in the same column. A count moves that many lines down.
^M
Move to the first character on the next line.
^N
Move the cursor down one line in the same column. A count moves that many lines down.
^P
Move the cursor up one line in the same column. A count moves that many lines up.
^U
Scroll backwards half a window. A count scrolls that many lines.
$
Move the cursor to the end of the current line. A count moves to the end of the following lines.
%
Move the cursor to the matching parenthesis or brace.
^
Move the cursor to the first non-whitespace character.
(
Move the cursor to the beginning of a sentence.
)
Move the cursor to the beginning of the next sentence.
{
Move the cursor to the preceding paragraph.
}
Move the cursor to the next paragraph.
Move the cursor to the column specified by the count.
+
Move the cursor to the first non-whitespace character in the next line.
-
Move the cursor to the first non-whitespace character in the previous line.
_
Move the cursor to the first non-whitespace character in the current line.
0
(Zero) Move the cursor to the first column of the current line.
B
Move the cursor back one word, skipping over punctuation.
E
Move forward to the end of a word, skipping over punctuation.
G
Go to the line number specified as the count. If no count is given, then go to the end of the file.
H
Move the cursor to the first non-whitespace character on the top of the screen.
L
Move the cursor to the first non-whitespace character on the bottom of the screen.
M
Move the cursor to the first non-whitespace character on the middle of the screen.
W
Move forward to the beginning of a word, skipping over punctuation.
b
Move the cursor back one word. If the cursor is in the middle of a word, move the cursor to the first character of that word.
e
Move the cursor forward one word. If the cursor is in the middle of a word, move the cursor to the last character of that word.
h
Move the cursor to the left one character position.
j
Move the cursor down one line.
k
Move the cursor up one line.
l
Move the cursor to the right one character position.
w
Move the cursor forward one word. If the cursor is in the middle of a word, move the cursor to the first character of the next word.
Moving the Cursor Around the Screen
^E
Scroll forwards one line. A count scrolls that many lines.
^Y
Scroll backwards one line. A count scrolls that many lines.
z
Redraw the screen with the following options. "z
Replacing Text
C
Change to the end of the line from the current cursor position.
R
Replace characters on the screen with a set of characters entered, ending with the Escape key.
S
Change an entire line.
c
Change until . "cc" changes the current line. A count changes that many lines.
r
Replace one character under the cursor. Specify a count to replace a number of characters.
s
Substitute one character under the cursor, and go into insert mode. Specify a count to substitute a number of characters. A dollar sign ($) will be put at the last character to be substituted.
Searching for Text or Characters
,
Repeat the last f, F, t or T command in the reverse direction.
/
Search the file downwards for the string specified after the /.
;
Repeat the last f, F, t or T command.
?
Search the file upwards for the string specified after the ?.
F
Search the current line backwards for the character specified after the 'F' command. If found, move the cursor to the position.
N
Repeat the last search given by '/' or '?', except in the reverse direction.
T
Search the current line backwards for the character specified after the 'T' command, and move to the column after the if it's found.
f
Search the current line for the character specified after the 'f' command. If found, move the cursor to the position.
n
Repeat last search given by '/' or '?'.
t
Search the current line for the character specified after the 't' command, and move to the column before the character if it's found.
Manipulating Character/Line Formatting
~
Switch the case of the character under the cursor.
<>
Shift the lines up to where to the right by one shiftwidth.
">>"
shifts the current line to the right, and can be specified with a count.
J
Join the current line with the next one. A count joins that many lines.
Saving and Quitting
^\
Quit out of "VI" mode and go into "EX" mode. The EX editor is the line editor VI is build upon. The EX command to get back into VI is ":vi".
Q
Quit out of "VI" mode and go into "EX" mode. The ex editor is a line-by-line editor. The EX command to get back into VI is ":vi".
ZZ
Exit the editor, saving if any changes were made.
Miscellany
^G
Show the current filename and the status.
^L
Clear and redraw the screen.
^R
Redraw the screen removing false lines.
^[
Escape key. Cancels partially formed command.
^^
Go back to the last file edited.
!
Execute a shell. If a is specified, the program which is executed using ! uses the specified line(s) as standard input, and will replace those lines with the standard output of the program executed. "!!" executes a program using the current line as input. For example, "!4jsort" will take five lines from the current cursor position and execute sort. After typing the command, there will be a single exclamation point where you can type the command in.
&
Repeat the previous ":s" command.
.
Repeat the last command that modified the file.
:
Begin typing an EX editor command. The command is executed once the user types return. (See section below.)
@
Type the command stored in the specified buffer.
U
Restore the current line to the state it was in before the cursor entered the line.
m
Mark the current position with the character specified after the 'm' command.
u
Undo the last change to the file. Typing 'u' again will re-do the change.
EX Commands
The VI editor is built upon another editor, called EX. The EX editor only edits by line. From the VI editor you use the : command to start entering an EX command. This list given here is not complete, but the commands given are the more commonly used. If more than one line is to be modified by certain commands (such as ":s" and ":w" ) the range must be specified before the command. For example, to substitute lines 3 through 15, the command is ":3,15s/from/this/g".
:ab string strings
Abbreviation. If a word is typed in VI corresponding to string1, the editor automatically inserts the corresponding words. For example, the abbreviation ":ab usa United States of America" would insert the words, "United States of America" whenever the word "usa" is typed in.
:map keys new_seq
Mapping. This lets you map a key or a sequence of keys to another key or a sequence of keys.
:q
Quit VI. If there have been changes made, the editor will issue a warning message.
:q!
Quit VI without saving changes.
:s/pattern/to_pattern/options
Substitute. This substitutes the specified pattern with the string in the to_pattern. Without options, it only substitutes the first occurence of the pattern. If a 'g' is specified, then all occurences are substituted. For example, the command ":1,$s/Dwayne/Dwight/g" substitutes all occurences of "Dwayne" to "Dwight".
:set [all]
Sets some customizing options to VI and EX. The ":set all" command gives all the possible options. (See the section on customizing VI for some options.)
:una string
Removes the abbreviation previously defined by ":ab".
:unm keys
Removes the remove mapping defined by ":map".
:vi filename
Starts editing a new file. If changes have not been saved, the editor will give you a warning.
:w
Write out the current file.
:w filename
Write the buffer to the filename specified.
:w >> filename
Append the contents of the buffer to the filename.
:wq
Write the buffer and quit.
Friday, September 28, 2007
Database Terminalogy
Table
A collection of columns and rows representing a single entity (e.g., customers, orders, employees, etc.).
Column
A single attribute of an entity stored in a table. A column has a name and a datatype. A table may have, and typically does have, more than one column as part of its definition.
Row
A single instance of an entity in a table, including all columns. For example, a student row will store all information about a single student, such as that student’s ID, name, and address.
Cell
The term cell is used to refer to the intersection of a single column in a single row. For example, the CompanyName column for CustomerID 10002 in our example would be a cell holding that data—Bradley Systems.
Constraint
A database object that is used to enforce simple business rules and database integrity. Examples of constraints are PRIMARY KEY, FOREIGN KEY, NOT NULL, and CHECK.
View
A view is a logical projection of data from one or more tables as represented by a SQL statement stored in the database. Views are used to simplify complex and repetitive SQL statements by assigning those statements a name in the database.
Index
An index is a database object that helps speed up retrieval of data by storing logical pointers to specific key values. By scanning the index, which is organized in either ascending or descending order according to the key value, you are able to retrieve a row quicker than by scanning all rows in a table.
Indexorganized table
A table whose physical storage is organized like an index. Unlike a regular table, where rows are inserted in no particular order and querying all rows will retrieve the data in random order, index-organized tables store data organized according to the primary key defined on the table. The difference between a table (referred to as storing data on a heap) and an indexorganized table is like the difference between storing all of your receipts in a shoebox (i.e., in no specific order) and storing it chronologically according to the date the expense was incurred. Taking the receipts out of the shoebox will result in no specific logic in their retrieval, while doing the same when the receipts are organized chronologically will allow you to predict that the June 2 receipt will appear before the August 1 receipt.
Partition
Tables in Oracle 10g can be cut into pieces for more efficient physical storage. A partition (or subpartition) holds a subset of the table’s data, typically on a separate physical disk, so that data retrieval is quicker either by allowing reads from more than one physical disk simultaneously (multipartition parallel reads) or by not reading a partition’s data at all if it is not required to satisfy the query (partition elimination).
Cluster
A storage mechanism object that allows rows from more than one table to be physically stored together for quicker retrieval. For example, if you store the Order information (customer, payment info, delivery details, etc.) in one table and the line items (item, cost, sale price, quantity, etc.) in a different table, you will need to perform at least two reads to retrieve information about an order: one for the order info and the second for line item info. Creating both tables on the cluster organized by the order ID will allow Oracle to place the order and line item data for the same order ID on the same physical block, thereby reducing retrieval of that order’s information to a single read. The downside of clusters is that they force you to preallocate a certain portion or all of the disk space they require when rows are added or the cluster is created.
Sequence
A sequence allows you to creat and increment a counter that can be used to generate numerical values to be used as primary key values for a table.
Synonym
As in the English language, a synonym is another name for an existing object. Synonyms are used in Oracle as shorthand for objects with long names, or to make it easier to remember a specific object.
Stored procedure
A stored procedure is a collection of SQL and PL/SQL statements that perform a specific task, such as to insert a row into a table or to update data.
Trigger
A trigger is a special kind of stored procedure that cannot be invoked manually but rather is automatically invoked whenever an action is performed on a table. Triggers can be associated with a table and a corresponding action such as INSERT, UPDATE, or DELETE as well as system events such as user logon and logoff, or database STARTUP and SHUTDOWN.
Function
A function is a stored program that must return a value. Unlike stored procedures, which can have parameters passed to them and do not need to return any value as output, a function must return a value.
A collection of columns and rows representing a single entity (e.g., customers, orders, employees, etc.).
Column
A single attribute of an entity stored in a table. A column has a name and a datatype. A table may have, and typically does have, more than one column as part of its definition.
Row
A single instance of an entity in a table, including all columns. For example, a student row will store all information about a single student, such as that student’s ID, name, and address.
Cell
The term cell is used to refer to the intersection of a single column in a single row. For example, the CompanyName column for CustomerID 10002 in our example would be a cell holding that data—Bradley Systems.
Constraint
A database object that is used to enforce simple business rules and database integrity. Examples of constraints are PRIMARY KEY, FOREIGN KEY, NOT NULL, and CHECK.
View
A view is a logical projection of data from one or more tables as represented by a SQL statement stored in the database. Views are used to simplify complex and repetitive SQL statements by assigning those statements a name in the database.
Index
An index is a database object that helps speed up retrieval of data by storing logical pointers to specific key values. By scanning the index, which is organized in either ascending or descending order according to the key value, you are able to retrieve a row quicker than by scanning all rows in a table.
Indexorganized table
A table whose physical storage is organized like an index. Unlike a regular table, where rows are inserted in no particular order and querying all rows will retrieve the data in random order, index-organized tables store data organized according to the primary key defined on the table. The difference between a table (referred to as storing data on a heap) and an indexorganized table is like the difference between storing all of your receipts in a shoebox (i.e., in no specific order) and storing it chronologically according to the date the expense was incurred. Taking the receipts out of the shoebox will result in no specific logic in their retrieval, while doing the same when the receipts are organized chronologically will allow you to predict that the June 2 receipt will appear before the August 1 receipt.
Partition
Tables in Oracle 10g can be cut into pieces for more efficient physical storage. A partition (or subpartition) holds a subset of the table’s data, typically on a separate physical disk, so that data retrieval is quicker either by allowing reads from more than one physical disk simultaneously (multipartition parallel reads) or by not reading a partition’s data at all if it is not required to satisfy the query (partition elimination).
Cluster
A storage mechanism object that allows rows from more than one table to be physically stored together for quicker retrieval. For example, if you store the Order information (customer, payment info, delivery details, etc.) in one table and the line items (item, cost, sale price, quantity, etc.) in a different table, you will need to perform at least two reads to retrieve information about an order: one for the order info and the second for line item info. Creating both tables on the cluster organized by the order ID will allow Oracle to place the order and line item data for the same order ID on the same physical block, thereby reducing retrieval of that order’s information to a single read. The downside of clusters is that they force you to preallocate a certain portion or all of the disk space they require when rows are added or the cluster is created.
Sequence
A sequence allows you to creat and increment a counter that can be used to generate numerical values to be used as primary key values for a table.
Synonym
As in the English language, a synonym is another name for an existing object. Synonyms are used in Oracle as shorthand for objects with long names, or to make it easier to remember a specific object.
Stored procedure
A stored procedure is a collection of SQL and PL/SQL statements that perform a specific task, such as to insert a row into a table or to update data.
Trigger
A trigger is a special kind of stored procedure that cannot be invoked manually but rather is automatically invoked whenever an action is performed on a table. Triggers can be associated with a table and a corresponding action such as INSERT, UPDATE, or DELETE as well as system events such as user logon and logoff, or database STARTUP and SHUTDOWN.
Function
A function is a stored program that must return a value. Unlike stored procedures, which can have parameters passed to them and do not need to return any value as output, a function must return a value.
Package
A package is a collection of stored procedures and functions grouped under a common name. This allows you to logically group all program elements for a particular part of the database under a single name for maintenance and performance reasons.
User-defined datatype
A user-defined datatype is a database object that can be used in any table or another object definition. Using user-defined datatypes allows you to ensure consistency between tables and also lets you apply methods (i.e., actions that can be performed by the object) as part of the definition.
BLOB
A BLOB is a binary large object used to store video, images, and large amounts of text. BLOBs are defined as a column in a table and can be one of several datatypes: BLOB, CLOB, NCLOB, or BFILE.
Thursday, September 27, 2007
Crontabrocks
Crontabrocks is a quick and simple guide to crontab usage. The cron daemon provides the ability for a system administrator or any other user (if permitted to do so) to automate the routine running of scripts, tasks or any other server function on a regular basis. The cron daemon is somewhat similar to what the "scheduled tasks" tool on a Windows machine would be. Most Unix/Linux based systems come with the cron daemon installed and configured to start on server boot by default. There is usually not much work, if any, that you have to do to start using the cron daemon. The server itself usually comes out of the box with several systemic cron jobs already set up, so adding your own jobs to the schedule is not big deal. Cron jobs are scheduled by a user using a tool known as "crontab". The crontab is nothing more than a text file written in a syntax that the cron daemon understands.
So first, let's talk about how to handle the crontab itself...
Accessing the Crontab
The following commands would apply to the user which you are logged in as. For example, if you are logged in as "root", these commands would pertain to root's crontab file.
crontab -e - opens the user's crontab file for viewing/editing
crontab -l - simply lists the crontab file's contents for the user. Think of it as a "cat" function for the crontab.
crontab -r - removes the crontab file contents for the user
But what if you want to edit another user's crontab?
The system administrator is usually logged in as "root", but making changes to another user's crontab file or simply looking at another user's crontab file is often necessary. For situations like this, you can append the "-u" flag followed by the desired username.
For example, if logged in as root but you want to edit the crontab for the user "admin", you would do the following:
crontab -e -u admin
The same logic applies to the other crontab commands as well, such as:
crontab -l -u admin - lists the crontab entry for the "admin" user.
crontab -r -u admin - removes the crontab entry for the "admin" user.
Writing to the Crontab
Now that you know how to access the crontab, let's take a look at the syntax of the crontab entry itself.
A typical crontab entry might look like this:
30 0,12 * * * /usr/local/scripts/whatever.script
OK, so what does that mean? Well, there are 2 parts to the entry you see above. In fact, any crontab entry has 2 parts:
Part 1 - The schedule
The schedule, which governs when the task will run, consists of a string of numbers, possible commas and asterisks (*).
So, in the above example, the schedule is:
30 0,12 * * *
What you are seeing is actually split up into 5 sections. The following chart illustrates what each section of the schedule is for:
1. Minute - Minutes after the hour (0-59).
2. Hour - 24-hour format (0-23).
3. Day - Day of the month (1-31).
4. Month - Month of the year (1-12).
5. Weekday - Day of the week. (0-6, where 0 indicates Sunday).
* = An asterisk in a schedule field indicates "every". It means that the task will occur on "every" instance of the given field. So a "*" on the Month field indicates the the task will run "every" month of the year. A * in the Minutes field would indicate that the task would run "every" minute.
, = A comma is used to input multiple values for a field. For example, if you wanted a task to run at hours 12, 15 and 18, you would enter that as "12,15,18".
Let's take a look at how this format fits into the syntax of a crontab entry:
_________________________ 1. Minute - Minutes after the hour (0-59)
______________________ 2. Hour - 24-hour format (0-23).
___________________ 3. Day - Day of the month (1-31)
________________ 4. Month - Month of the year (1-12)
______________5. Weekday - Day of the week. (0-6, where 0 indicates Sunday)
30 0,12 * * * /some/script/or/command
So, when we combine all the schedule elements, we know when and how often this task will run. Going on the above example, this task would run:
At 30 minutes past the hours of 0 (midnight) and 12 (noon), EVERY day of the month, EVERY month of the year and EVERY day of the week.
In other words, the above task would run every single day at 12:30AM and 12:30PM.
Let's play around with the schedule a little bit and try something different. What if we had something like this:
15,45 0,12,6 20 1,2,3 0 /some/script/or/command
Wow! Now this is a pretty complex crontab entry. Let's decipher it...
15,45 - This means that the task will run at 15 and 45 minutes past the hour. But what hours and what days? Well, that's coming up.
0.12.6 - The task will run during the hours of 0 (midnight), 12 (noon) and 6AM.
20 - The task will run on the 20th day of the month. But during what months?
1,2,3 - The task will run only during the months of January, February and March.
0 - The task will only run on a Sunday.
So, when we put all of this information together, here is what it boils down to:
This task is going to run at 0:15, 0:45, 6:15, 6:45, 12:15 and 12:45 on the 20th of January, February and March IF that day falls on a Sunday. Pretty cool, huh?
Part 2: The task itself
This is pretty self-explanatory. In the above example, the "task" to run would be: /some/script/or/command.
Now obviously, that's not a real script or commnad, it's just an example that you would fill in the blanks for. Generally, there are 2 ways to run a task from the crontab:
Method A: Run the desired command directly from the crontab
Here's an example of running a command directly from the crontab:
*/15 * * * * /sbin/ifconfig mail myname@mydomain.com
This is pretty cut and dry. This entry is going to run the "ifconfig" command and then pipe the results out to an e-mail that gets sent to myname@mydomain.com. This task will run EVERY 15 minutes, EVERY day, EVERY month, EVERY day of the week. In other words, this task runs every 15 minutes.
As you can see, the "ifconfig" command is called directly form the crontab entry. This is fine is you are running a simple command. But what if you need to do more than that?
Method B: Run a script from the crontab that runs the commands.
Here's an example of running a script from the crontab. The script that you run, in turn, executes whatever instruction you might want:
15 0* * * /usr/local/scripts/run_webstats.script
As you can see, every night at 0:15, this task runs a script called "/usr/local/scripts/run_webstats.script". That script, as you may guess, generates the webstats for the website on the server. The crontab runs a script and then the script contains the actual instructions on what the task is supposed to do. Get it?
By the way, I'm just making these crontab scripts up out of thin air, so don't expect the above example to really work. You would need to replace my bogus script calls with REAL scripts calls or commands.
So first, let's talk about how to handle the crontab itself...
Accessing the Crontab
The following commands would apply to the user which you are logged in as. For example, if you are logged in as "root", these commands would pertain to root's crontab file.
crontab -e - opens the user's crontab file for viewing/editing
crontab -l - simply lists the crontab file's contents for the user. Think of it as a "cat" function for the crontab.
crontab -r - removes the crontab file contents for the user
But what if you want to edit another user's crontab?
The system administrator is usually logged in as "root", but making changes to another user's crontab file or simply looking at another user's crontab file is often necessary. For situations like this, you can append the "-u" flag followed by the desired username.
For example, if logged in as root but you want to edit the crontab for the user "admin", you would do the following:
crontab -e -u admin
The same logic applies to the other crontab commands as well, such as:
crontab -l -u admin - lists the crontab entry for the "admin" user.
crontab -r -u admin - removes the crontab entry for the "admin" user.
Writing to the Crontab
Now that you know how to access the crontab, let's take a look at the syntax of the crontab entry itself.
A typical crontab entry might look like this:
30 0,12 * * * /usr/local/scripts/whatever.script
OK, so what does that mean? Well, there are 2 parts to the entry you see above. In fact, any crontab entry has 2 parts:
Part 1 - The schedule
The schedule, which governs when the task will run, consists of a string of numbers, possible commas and asterisks (*).
So, in the above example, the schedule is:
30 0,12 * * *
What you are seeing is actually split up into 5 sections. The following chart illustrates what each section of the schedule is for:
1. Minute - Minutes after the hour (0-59).
2. Hour - 24-hour format (0-23).
3. Day - Day of the month (1-31).
4. Month - Month of the year (1-12).
5. Weekday - Day of the week. (0-6, where 0 indicates Sunday).
* = An asterisk in a schedule field indicates "every". It means that the task will occur on "every" instance of the given field. So a "*" on the Month field indicates the the task will run "every" month of the year. A * in the Minutes field would indicate that the task would run "every" minute.
, = A comma is used to input multiple values for a field. For example, if you wanted a task to run at hours 12, 15 and 18, you would enter that as "12,15,18".
Let's take a look at how this format fits into the syntax of a crontab entry:
_________________________ 1. Minute - Minutes after the hour (0-59)
______________________ 2. Hour - 24-hour format (0-23).
___________________ 3. Day - Day of the month (1-31)
________________ 4. Month - Month of the year (1-12)
______________5. Weekday - Day of the week. (0-6, where 0 indicates Sunday)
30 0,12 * * * /some/script/or/command
So, when we combine all the schedule elements, we know when and how often this task will run. Going on the above example, this task would run:
At 30 minutes past the hours of 0 (midnight) and 12 (noon), EVERY day of the month, EVERY month of the year and EVERY day of the week.
In other words, the above task would run every single day at 12:30AM and 12:30PM.
Let's play around with the schedule a little bit and try something different. What if we had something like this:
15,45 0,12,6 20 1,2,3 0 /some/script/or/command
Wow! Now this is a pretty complex crontab entry. Let's decipher it...
15,45 - This means that the task will run at 15 and 45 minutes past the hour. But what hours and what days? Well, that's coming up.
0.12.6 - The task will run during the hours of 0 (midnight), 12 (noon) and 6AM.
20 - The task will run on the 20th day of the month. But during what months?
1,2,3 - The task will run only during the months of January, February and March.
0 - The task will only run on a Sunday.
So, when we put all of this information together, here is what it boils down to:
This task is going to run at 0:15, 0:45, 6:15, 6:45, 12:15 and 12:45 on the 20th of January, February and March IF that day falls on a Sunday. Pretty cool, huh?
Part 2: The task itself
This is pretty self-explanatory. In the above example, the "task" to run would be: /some/script/or/command.
Now obviously, that's not a real script or commnad, it's just an example that you would fill in the blanks for. Generally, there are 2 ways to run a task from the crontab:
Method A: Run the desired command directly from the crontab
Here's an example of running a command directly from the crontab:
*/15 * * * * /sbin/ifconfig mail myname@mydomain.com
This is pretty cut and dry. This entry is going to run the "ifconfig" command and then pipe the results out to an e-mail that gets sent to myname@mydomain.com. This task will run EVERY 15 minutes, EVERY day, EVERY month, EVERY day of the week. In other words, this task runs every 15 minutes.
As you can see, the "ifconfig" command is called directly form the crontab entry. This is fine is you are running a simple command. But what if you need to do more than that?
Method B: Run a script from the crontab that runs the commands.
Here's an example of running a script from the crontab. The script that you run, in turn, executes whatever instruction you might want:
15 0* * * /usr/local/scripts/run_webstats.script
As you can see, every night at 0:15, this task runs a script called "/usr/local/scripts/run_webstats.script". That script, as you may guess, generates the webstats for the website on the server. The crontab runs a script and then the script contains the actual instructions on what the task is supposed to do. Get it?
By the way, I'm just making these crontab scripts up out of thin air, so don't expect the above example to really work. You would need to replace my bogus script calls with REAL scripts calls or commands.
Subscribe to:
Posts (Atom)