Saturday, September 12, 2009

Aduit Trail of Application

AUDIT TRAILS
---------------

Audit trails maintain a record of system activity both by system and application processes and by user activity of systems and applications. In conjunction with appropriate tools and procedures, audit trails can assist in detecting security violations, performance problems, and flaws in applications. This bulletin focuses on audit trails as a technical control and discusses the benefits and objectives of audit trails, the types of
audit trails, and some common implementation issues.

An audit trail is a series of records of computer events, about an operating system, an application, or user activities. A computer system may have several audit trails, each devoted to a particular type of activity. Auditing is a review and analysis of management, operational, and technical controls. The auditor can obtain valuable information about activity on a computer system from the audit trail. Audit trails improve the auditability of the computer system.

Audit trails may be used as either a support for regular system operations or a kind of insurance policy or as both of these. As insurance, audit trails are maintained but are not used unless needed, such as after asystem outage. As a support for operations, audit trails are used to help
system administrators ensure that the system or resources have not been
harmed by hackers, insiders, or technical problems.

BENEFITS AND OBJECTIVES
------------------------
Audit trails can provide a means to help accomplish several
security-related objectives, including individual accountability,
reconstruction of events (actions that happen on a computer system),
intrusion detection, and problem analysis.

Individual Accountability
-------------------------
Audit trails are a technical mechanism that help managers maintain
individual accountability. By advising users that they are personally
accountable for their actions, which are tracked by an audit trail that
logs user activities, managers can help promote proper user behavior.
Users are less likely to attempt to circumvent security policy if they know
that their actions will be recorded in an audit log.

For example, audit trails can be used in concert with access controls to
identify and provide information about users suspected of improper
modification of data (e.g., introducing errors into a database). An audit
trail may record "before" and "after" versions of records. (Depending upon
the size of the file and the capabilities of the audit logging tools, this
may be very resource-intensive.) Comparisons can then be made between the
actual changes made to records and what was expected. This can help
management determine if errors were made by the user, by the system or
application software, or by some other source.

Audit trails work in concert with logical access controls, which restrict
use of system resources. Granting users access to particular resources
usually means that they need that access to accomplish their job.
Authorized access, of course, can be misused, which is where audit trail
analysis is useful. While users cannot be prevented from using resources
to which they have legitimate access authorization, audit trail analysis is
used to examine their actions. For example, consider a personnel office in
which users have access to those personnel records for which they are
responsible. Audit trails can reveal that an individual is printing far
more records than the average user, which could indicate the selling of
personal data. Another example may be an engineer who is using a computer
for the design of a new product. Audit trail analysis could reveal that an
outgoing modem was used extensively by the engineer the week before
quitting. This could be used to investigate whether proprietary data files
were sent to an unauthorized party.

Reconstruction of Events
------------------------
Audit trails can also be used to reconstruct events after a problem has
occurred. Damage can be more easily assessed by reviewing audit trails of
system activity to pinpoint how, when, and why normal operations ceased.
Audit trail analysis can often distinguish between operator-induced errors
(during which the system may have performed exactly as instructed) or
system-created errors (e.g., arising from a poorly tested piece of
replacement code). If, for example, a system fails or the integrity of a
file (either program or data) is questioned, an analysis of the
audit trail can reconstruct the series of steps taken by the system, the
users, and the application. Knowledge of the conditions that existed at
the time of, for example, a system crash, can be useful in avoiding future
outages. Additionally, if a technical problem occurs (e.g., the corruption
of a data file) audit trails can aid in the recovery process (e.g., by
using the record of changes made to reconstruct the file).

Intrusion Detection
-------------------
Intrusion detection refers to the process of identifying attempts to
penetrate a system and gain unauthorized access. If audit trails have been
designed and implemented to record appropriate information, they can assist
in intrusion detection. Although normally thought of as a real-time
effort, intrusions can be detected in real time, by examining audit records
as they are created (or through the use of other kinds of warning
flags/notices), or after the fact (e.g., by examining audit records in a
batch process).

Real-time intrusion detection is primarily aimed at outsiders attempting to
gain unauthorized access to the system. It may also be used to detect
changes in the system's performance indicative of, for example, a virus or
worm attack (forms of malicious code). There may be difficulties in
implementing real-time auditing, including unacceptable system performance.

After-the-fact identification may indicate that unauthorized access was
attempted (or was successful). Attention can then be given to damage
assessment or reviewing controls that were attacked.

Problem Analysis
----------------
Audit trails may also be used as on-line tools to help identify problems
other than intrusions as they occur. This is often referred to as
real-time auditing or monitoring. If a system or application is deemed to
be critical to an organization's business or mission, real-time auditing
may be implemented to monitor the status of these processes (although, as
noted above, there can be difficulties with real-time analysis). An
analysis of the audit trails may be able to verify that the system operated
normally (i.e., that an error may have resulted from operator error, as
opposed to a
system-originated error). Such use of audit trails may be complemented by
system performance logs. For example, a significant increase in the use of
system resources (e.g., disk file space or outgoing modem use) could
indicate a security problem.

AUDIT TRAILS AND LOGS
-----------------------
A system can maintain several different audit trails concurrently. There
are typically two kinds of audit records, (1) an event-oriented log and (2)
a record of every keystroke, often called keystroke monitoring.
Event-based logs usually contain records describing system events,
application events, or user events.

An audit trail should include sufficient information to establish what
events occurred and who (or what) caused them. In general, an event record
should specify when the event occurred, the user ID associated with the
event, the program or command used to initiate the event, and the result.
Date and time can help determine if the user was a masquerader or the
actual person specified.

Keystroke Monitoring
----------------------
Keystroke monitoring is the process used to view or record both the
keystrokes entered by a computer user and the computer's response during an
interactive session. Keystroke monitoring is usually considered a special
case of audit trails. Examples of keystroke monitoring would include
viewing characters as they are typed by users, reading users' electronic
mail, and viewing other recorded information typed by users. (See the CSL
Bulletin of March 1993, for guidance on the legality of keystroke monitoring.)

Some forms of routine system maintenance may record user keystrokes. This
could constitute keystroke monitoring if the keystrokes are preserved along
with the user identification so that an administrator could determine the
keystrokes entered by specific users. Keystroke monitoring is conducted in
an effort to protect systems and data from intruders who access the systems
without authority or in excess of their assigned authority. Monitoring
keystrokes typed by intruders can help administrators assess and repair
damage caused by intruders.

Audit Events
-------------
System audit records are generally used to monitor and fine-tune system
performance. Application audit trails may be used to discern flaws in
applications, or violations of security policy committed within an
application. User audits records are generally used to hold individuals
accountable for their actions. An analysis of user audit records may
expose a variety of security violations, which might range from simple
browsing to attempts to plant Trojan horses or gain unauthorized privileges.

The system itself enforces certain aspects of policy (particularly
system-specific policy) such as access to files and access to the system
itself. Monitoring the alteration of systems configuration files that
implement the policy is important. If special accesses (e.g., security
administrator access) have to be used to alter configuration files, the
system should generate audit records whenever these accesses are used.

Sometimes a finer level of detail than system audit trails is required.
Application audit trails can provide this greater level of recorded detail.
If an application is critical, it can be desirable to record not only who
invoked the application, but certain details specific to each use. For
example, consider an e-mail application. It may be desirable to record who
sent mail, as well as to whom they sent mail and the length of messages.
Another example would be that of a database application. It may be useful
to record who accessed what database as well as the individual rows or
columns of a table that were read (or changed or deleted), instead of just
recording the execution of the database program.

A user audit trail monitors and logs user activity in a system or
application by recording events initiated by the user (e.g., access of a
file, record or field, use of a modem).

Flexibility is a critical feature of audit trails. Ideally (from a
security point of view), a system administrator would have the ability to
monitor all system and user activity, but could choose to log only certain
functions at the system level, and within certain applications. The
decision of how much to log and how much to review should be a function of
application/data sensitivity and should be decided by each functional
manager/application owner with guidance from the system administrator and
the computer security manager/officer, weighing the costs and benefits of
the logging. Audit logging can have privacy implications; users should be
aware of applicable privacy laws, regulations, and policies that may apply
in such situations.

System-Level Audit Trails
-------------------------
If a system-level audit capability exists, the audit trail should capture,
at a minimum, any attempt to log on (successful or unsuccessful), the
log-on ID, date and time of each log-on attempt, date and time of each
log-off, the devices used, and the function(s) performed once logged on
(e.g., the applications that the user tried, successfully or
unsuccessfully, to invoke). System-level logging also typically includes
information that is not specifically security-related, such as system
operations, cost-accounting charges, and network performance.

Application-Level Audit Trails
------------------------------
System-level audit trails may not be able to track and log events within
applications, or may not be able to provide the level of detail needed by
application or data owners, the system administrator, or the computer
security manager. In general, application-level audit trails monitor and
log user activities, including data files opened and closed, specific
actions, such as reading, editing, and deleting records or fields, and
printing reports. Some applications may be sensitive enough from a data
availability,
confidentiality, and/or integrity perspective that a "before" and "after"
picture of each modified record (or the data element(s) changed within a
record) should be captured by the audit trail.

User Audit Trails
-----------------------
User audit trails can usually log:


- all commands directly initiated by the user;
- all identification and authentication attempts; and
- files and resources accessed.


It is most useful if options and parameters are also recorded from
commands. It is much more useful to know that a user tried to delete a log
file (e.g., to hide unauthorized actions) than to know the user merely
issued the delete command, possibly for a personal data file.

IMPLEMENTATION ISSUES
--------------------
Audit trail data requires protection, since the data should be available
for use when needed and is not useful if it is not accurate. Also, the
best planned and implemented audit trail is of limited value without timely
review of the logged data. Audit trails may be reviewed periodically, as
needed (often triggered by occurrence of a security event), automatically
in real-time, or in some combination of these. System managers and
administrators, with guidance from computer security personnel, should
determine how long audit trail data will be maintained -- either on the
system or in archive files.

Following are examples of implementation issues that may have to be
addressed when using audit trails.

Protecting Audit Trail Data
---------------------------
Access to on-line audit logs should be strictly controlled. Computer
security managers and system administrators or managers should have access
for review purposes; however, security and/or administration personnel who
maintain logical access functions may have no need for access to audit logs.

It is particularly important to ensure the integrity of audit trail data
against modification. One way to do this is to use digital signatures.
Another way is to use write-once devices. The audit trail files need to be
protected since, for example, intruders may try to "cover their tracks" by
modifying audit trail records. Audit trail records should be protected by
strong access controls to help prevent unauthorized access. The integrity
of audit trail information may be particularly important when legal issues
arise, such as when audit trails are used as legal evidence. (This may,
for example, require daily printing and signing of the logs.) Questions of
such legal issues should be directed to the cognizant legal counsel.

The confidentiality of audit trail information may also be protected, for
example, if the audit trail is recording information about users that may
be disclosure-sensitive such as transaction data containing personal
information (e.g., "before" and "after" records of modification to income
tax data). Strong access controls and encryption can be particularly
effective in preserving confidentiality.

Review of Audit Trails
--------------------------
Audit trails can be used to review what occurred after an event, for
periodic reviews, and for real-time analysis. Reviewers should know what
to look for to be effective in spotting unusual activity. They need to
understand what normal activity looks like. Audit trail review can be
easier if the audit trail function can be queried by user ID, terminal ID,
application name, date and time, or some other set of parameters to run
reports of selected information.

Audit Trail Review After an Event. Following a known system or application
software problem, a known violation of existing requirements by a user, or
some unexplained system or user problem, the appropriate system-level or
application-level administrator should review the audit trails. Review by
the application/data owner would normally involve a separate report, based
upon audit trail data, to determine if their resources are being misused.

Periodic Review of Audit Trail Data. Application owners, data owners,
system administrators, data processing function managers, and computer
security managers should determine how much review of audit trail records
is necessary, based on the importance of identifying unauthorized
activities. This determination should have a direct correlation to the
frequency of periodic reviews of audit trail data.

Real-Time Audit Analysis. Traditionally, audit trails are analyzed in a
batch mode at regular intervals (e.g., daily). Audit records are archived
during that interval for later analysis. Audit analysis tools can also be
used in a real-time, or near real-time fashion. Such intrusion detection
tools are based on audit reduction, attack signature, and variance
techniques. Manual review of audit records in real-time is almost never
feasible on large multiuser systems due to the volume of records generated.
However, it might be possible to view all records associated with a
particular user or application, and view them in real time. (This is
similar to keystroke monitoring, though, and may be legally restricted.)

Tools for Audit Trail Analysis
------------------------------
Many types of tools have been developed to help to reduce the amount of
information contained in audit records, as well as to distill useful
information from the raw data. Especially on larger systems, audit trail
software can create very large files, which can be extremely difficult to
analyze manually. The use of automated tools is likely to be the
difference between unused audit trail data and a robust program. Some of
the types of tools include:

Audit reduction tools are preprocessors designed to reduce the volume of
audit records to facilitate manual review. Before a security review, these
tools can remove many audit records known to have little security
significance. (This alone may cut in half the number of records in the
audit trail.) These tools generally remove records generated by specified
classes of events, such as records generated by nightly backups might be
removed.

Trends/variance-detection tools look for anomalies in user or system
behavior. It is possible to construct more sophisticated processors that
monitor usage trends and detect major variations. For example, if a user
typically logs in at 9 a.m., but appears at 4:30 a.m. one morning, this may
indicate a security problem that may need to be investigated.

Attack signature-detection tools look for an attack signature, which is a
specific sequence of events indicative of an unauthorized access attempt.
A simple example would be repeated failed log-in attempts.

COST CONSIDERATIONS
--------------------
Audit trails involve many costs. First, some system overhead is incurred
recording the audit trail. Additional system overhead will be incurred
storing and processing the records. The more detailed the records, the
more overhead is required. Another cost involves human and machine time
required to do the analysis. This can be minimized by using tools to
perform most of the analysis. Many simple analyzers can be constructed
quickly (and cheaply) from system utilities, but they are limited to audit
reduction and identifying particularly sensitive events. More complex
tools that identify trends or sequences of events are slowly becoming
available as off-the-shelf software. (If complex tools are not available
for a system, development may be prohibitively expensive. Some intrusion
detection systems, for example, have taken years to develop.)

The final cost of audit trails is the cost of investigating anomalous
events. If the system is identifying too many events as suspicious,
administrators may spend undue time reconstructing events and questioning
personnel.

FOR MORE INFORMATION
--------------------
This bulletin summarizes a chapter in NIST Special Publication 800-12,
Introduction to Computer Security: The NIST Handbook. The handbook is
available electronically at: http://csrc.nist.gov/nistpubs/800-12 in
WordPerfect 6.1, MS Word, and PostScript formats. You can also order the
handbook from the Government Printing Office at (202) 512-1800, stock
number SN003-003-03374-0, price $18.00.

Example of EXECUTE IMMEDIATE usage

1. To run a DDL statement in PL/SQL.
----------------------------------------

begin
execute immediate 'set role all';
end;


2. To pass values to a dynamic statement (USING clause).
--------------------------------------------------------

declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;


3. To retrieve values from a dynamic statement (INTO clause).
-------------------------------------------------------------

declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;


4. To call a routine dynamically: The bind variables used for parameters of the routine have to be specified along with the parameter type. IN type is the default, others have to be specified explicitly.
------------------------------------------------------------------

declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status;

if l_status != 'OK' then
dbms_output.put_line('error');
end if;
end;


5. To return value into a PL/SQL record type: The same option can be used for %rowtype variables also.
-------------------------------------------------------------------------

declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' ||
'from emp where empno = 7934'
into empdtl;
end;


6. To pass and retrieve values: The INTO clause should precede the USING clause.
---------------------------------------------------------------------------------

declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
end;


7. Multi-row query option. Use the insert statement to populate a temp table for this option. Use the temporary table to carry out further processing. Alternatively, you may use REF cursors to by-pass this drawback.
--------------------------------------------------------------------------


declare
l_sal pls_integer := 2000;
begin
execute immediate 'insert into temp(empno, ename) ' ||
' select empno, ename from emp ' ||
' where sal > :1'
using l_sal;
commit;
end;



EXECUTE IMMEDIATE is a much easier and more efficient method of processing dynamic statements than could have been possible before. As the intention is to execute dynamic statements, proper handling of exceptions becomes all the more important. Care should be taken to trap all possible exceptions.

for details
http://docs.oracle.com/cd/B14117_01/appdev.101/b10807/13_elems017.htm

create vartual drive in windows

subst q: E:\ORBEXE

For every record in the master table i want the records from the detail table

select nvl2( empno, null, deptno ) deptno,
2 nvl2( empno, null, dname ) dname,
3 empno, ename, sal
4 from (
5 select d.deptno deptno, d.dname, e.empno empno, e.ename, e.sal
6 from dept d, emp e
7 where d.deptno = e.deptno
8 union all
9 select d.deptno, d.dname, null, null, null
10 from dept d
11 order by deptno, empno NULLS FIRST
12 ) x
13 /

DEPTNO DNAME EMPNO ENAME SAL
---------- -------------- ---------- ---------- ----------
10 ACCOUNTING
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
20 RESEARCH
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000
30 SALES
7499 ALLEN 1600
7521 WARD 1250
7654 MARTIN 1250
7698 BLAKE 2850
7844 TURNER 1500
7900 JAMES 950
40 OPERATIONS

18 rows selected.

use of REgexp_replace

The innermost regexp_replace gets rid of all the non-letter characters out of the string:

SQL> with src as ( select 'Internet + Multimedia Services (KT Whole)' col_1 from dual)
2 select regexp_replace(col_1,'([^A-Za-z ])','')
3 from src;

REGEXP_REPLACE(COL_1,'([^A-ZA-Z])','')
--------------------------------------
Internet Multimedia Services KT Whole


The second Regexp_replace takes the output from the first, and looks for strings matching the pattern '3 letters followed by any number of characters that aren't spaces. It replaces each of these strings with the '3 letters' part of the string.

SQL> with src as ( select 'Internet Multimedia Services KT Whole' col_1 from dual)
2 select regexp_replace(col_1,'([A-Za-z]{3})[^ ]*','\1')
3 from src;

REGEXP_REPLACE(COL_
-------------------
Int Mul Ser KT Who


The final regexp_replace takes this output, and replaces 1 or more contiguous spaces with a single underscore character:

SQL> with src as ( select 'Int Mul Ser KT Who' col_1 from dual)
2 select regexp_replace(col_1,'([ ]+)','_')
3 from src;

REGEXP_REPLACE(COL
------------------
Int_Mul_Ser_KT_Who
Report message to a moderator

--------------------------------------------
Another option using regex:


SQL> select a1
2 , rtrim(regexp_replace(a1,'([^[:alpha:]]*)([[:alpha:]]{1,3})([^ ]*)', '\2_'), '_') str
3 from full_name;

A1 STR
--------------------------------------------- -------------------------
Internet & Multimedia Services (KT Whole) Int_Mul_Ser_KT_Who
ABCD + XYZ Corp (PQ Half) ABC_XYZ_Cor_PQ_Hal
A B C D E F G A_B_C_D_E_F_G


with src as ( select 'Internet + Multimedia Services (KT Whole)' col_1 from dual)
select regexp_replace(regexp_replace(regexp_replace(col_1,'([^A-Za-z ])',''),'([A-Za-z]{3})[^ ]*','\1'),'([ ]+)','_')
from src;


with src as ( select 'Internet + Multimedia Services (KT Whole)' col_1 from dual)
select regexp_replace(regexp_replace(regexp_replace(col_1,'([^A-Za-z ])',''),'([A-Za-z]{3})[^ ]*','\1'),'([ ]+)','_')
from src;

nested table for hierarchycal xml

CREATE OR REPLACE TYPE test1_type AS OBJECT
(
SOURCE_ID NUMBER(10),
ENTITY_ID_CODE VARCHAR2(3 Byte)
)

CREATE OR REPLACE TYPE test1_type_tbl
AS TABLE OF test1_type;

CREATE OR REPLACE TYPE test2_type AS OBJECT
(
RECEIVER_ID NUMBER(10),
SOURCE_ID NUMBER(10)
)

CREATE OR REPLACE TYPE test2_type_tbl
AS TABLE OF test2_type;

CREATE OR REPLACE TYPE wrapper_type AS OBJECT
(
test_nested_input1 test1_type_tbl,
test_nested_input2 test2_type_tbl
)

CREATE OR REPLACE TYPE wrapper_type_tbl
AS TABLE OF wrapper_type;

create table REALMED_CORP_NESTED_TABLE
(
id INTEGER Primary Key,
Company_Name VarChar2(20),
wrapper_rec wrapper_type_tbl
)
NESTED TABLE wrapper_rec STORE AS nested_wrapper_rec
(NESTED TABLE test_nested_input1 STORE AS nested_Input1_tab
NESTED TABLE test_nested_input2 STORE AS nested_Input2_tab);

insert into REALMED_CORP_NESTED_TABLE
values
(001,
'c12',
wrapper_type_tbl(
wrapper_type(
test1_type_tbl(test1_type(12,'t')),
test2_type_tbl(test2_type(12,12))
))
);

commit

select DBMS_XMLquery.GetXML('select * from REALMED_CORP_NESTED_TABLE') from dual

cursor in query and generate hierarchical xml

cursor in query and generate hierarchical xml
================================================


CREATE TABLE table_1 (countryid VARCHAR2(10),country VARCHAR2(20));


INSERT INTO table_1
VALUES ('100', 'Bangladesh');

INSERT INTO table_1
VALUES ('200', 'India');

INSERT INTO table_1
VALUES ('300', 'Pakistan');

COMMIT ;


CREATE TABLE table_2 (cityid VARCHAR2(10),city VARCHAR2(20),countryid VARCHAR2(10));


INSERT INTO table_2
VALUES ('10', 'Dhaka', '100');

INSERT INTO table_2
VALUES ('20', 'Sylhet', '100');

INSERT INTO table_2
VALUES ('30', 'Rajshahi', '100');


INSERT INTO table_2
VALUES ('10', 'Kolkata', '200');

INSERT INTO table_2
VALUES ('20', 'Mumbai', '200');

INSERT INTO table_2
VALUES ('30', 'Rajastan', '200');


COMMIT ;

----Query

SELECT countryid, country,
CURSOR (SELECT cityid, city
FROM table_2 t1
WHERE t1.countryid = t2.countryid) AS cityname
FROM table_1 t2


-----generate an xml file with hierarchical

select DBMS_XMLquery.GetXML('SELECT COUNTRYID, COUNTRY,
CURSOR (SELECT CITYID, CITY
FROM TABLE_2 T1
WHERE T1.COUNTRYID = T2.COUNTRYID) AS CITYNAME
FROM TABLE_1 T2',2) dd from dual;

how to find locked row

CREATE OR REPLACE FUNCTION find_locked_rows (v_rowid ROWID, table_name VARCHAR2)
RETURN ROWID IS
x NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'Begin Select 1 into :x from '
|| table_name
|| '
where rowid =:v_rowid for update nowait; Exception
When Others Then :x:=null; End;'
USING OUT x, v_rowid;

ROLLBACK;

IF x = 1 THEN
RETURN v_rowid;
ELSIF x IS NULL THEN
RETURN NULL;
END IF;
END;

how to show user privilize by query

SELECT LPAD (' ', 2 * LEVEL) || granted_role "USER PRIVS"
FROM (SELECT NULL grantee, username granted_role
FROM dba_users
WHERE username LIKE UPPER ('%&uname%')
UNION
SELECT grantee, granted_role
FROM dba_role_privs
UNION
SELECT grantee, PRIVILEGE
FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = PRIOR granted_role;



SELECT PATH
FROM (SELECT grantee,
SYS_CONNECT_BY_PATH (PRIVILEGE, ':') || ':'
|| grantee PATH
FROM (SELECT grantee, PRIVILEGE, 0 ROLE
FROM dba_sys_privs
UNION ALL
SELECT grantee, granted_role, 1 ROLE
FROM dba_role_privs)
CONNECT BY PRIVILEGE = PRIOR grantee
START WITH ROLE = 0)
WHERE grantee IN (
SELECT username
FROM dba_users
WHERE lock_date IS NULL
AND PASSWORD != 'EXTERNAL'
AND username != 'SYS')
OR grantee = 'PUBLIC'

point in time recovery using SCN with RMAN

I am trying to do point in time recovery using SCN on windows(11.1.6) for lost of current redolog after shutdown abort(removed logfiles).
Here are the steps....
SQL> select status,group#,first_change# from v$log;

STATUS GROUP# FIRST_CHANGE#
---------------- ---------- -------------
INACTIVE 1 1952117
CURRENT 2 1983844
UNUSED 3 0

SQL> conn scott/tiger
Connected.
SQL> create table aa (id number);

Table created.

SQL> insert into aa values(&1);
Enter value for 1: 1
old 1: insert into aa values(&1)
new 1: insert into aa values(1)

1 row created.

SQL> /
Enter value for 1: 2
old 1: insert into aa values(&1)
new 1: insert into aa values(2)

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select status,group#,first_change# from v$log;

STATUS GROUP# FIRST_CHANGE#
---------------- ---------- -------------
INACTIVE 1 1997211
CURRENT 2 1997214
INACTIVE 3 1997209

SQL> select max(NEXT_CHANGE#)-1 as "SCN" from V$ARCHIVED_LOG;

SCN
----------
1997213

SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\app\sali\oradata\test>rm *.log

C:\Users\sali>rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Sat Jun 27 11:41:29 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area 426852352 bytes

Fixed Size 1333648 bytes
Variable Size 285214320 bytes
Database Buffers 134217728 bytes
Redo Buffers 6086656 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 27-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK

recovery area destination: C:\app\sali\flash_recovery_area
database name (or database unique name) used for search: TEST
channel ORA_DISK_1: AUTOBACKUP C:\APP\sali\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2
009_06_26\O1_MF_S_690594541_54C5PSC1_.BKP found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP C:\APP\sali\FLASH_REC
OVERY_AREA\TEST\AUTOBACKUP\2009_06_26\O1_MF_S_690594541_54C5PSC1_.BKP
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=C:\APP\sali\ORADATA\TEST\CONTROL01.CTL
output file name=C:\APP\sali\ORADATA\TEST\CONTROL02.CTL
output file name=C:\APP\sali\ORADATA\TEST\CONTROL03.CTL
Finished restore at 27-JUN-09

RMAN> list incarnation;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 06/27/2009 11:43:36
ORA-01507: database not mounted

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> list incarnation;


List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 TEST 1988467378 PARENT 1 15-OCT-07
2 2 TEST 1988467378 PARENT 886308 23-JUN-09
3 3 TEST 1988467378 PARENT 1950836 26-JUN-09
4 4 TEST 1988467378 PARENT 1951672 26-JUN-09
6 6 TEST 1988467378 ORPHAN 1952117 26-JUN-09
7 7 TEST 1988467378 CURRENT 1952117 26-JUN-09
5 5 TEST 1988467378 ORPHAN 1952344 26-JUN-09

RMAN> reset database to incarnation 4;

database reset to incarnation 4

RMAN> restore database until scn 1997213;

Starting restore at 27-JUN-09
Starting implicit crosscheck backup at 27-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
Crosschecked 9 objects
Finished implicit crosscheck backup at 27-JUN-09

Starting implicit crosscheck copy at 27-JUN-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-JUN-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_27\O1_MF_1_1_
54DB81TJ_.ARC
File Name: C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_27\O1_MF_1_2_
54DH6QCR_.ARC
File Name: C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_27\O1_MF_1_3_
54DH6T9T_.ARC
File Name: C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_27\O1_MF_1_4_
54DH6WYB_.ARC
File Name: C:\APP\sali\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2009_06_26\O1_MF_S_69
0594541_54C5PSC1_.BKP

using channel ORA_DISK_1

creating datafile file number=6 name=C:\APP\sali\ORADATA\TEST\USER02.DBF
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\APP\sali\ORADATA\TEST\SYSTEM0
1.DBF
channel ORA_DISK_1: restoring datafile 00002 to C:\APP\sali\ORADATA\TEST\SYSAUX0
1.DBF
channel ORA_DISK_1: restoring datafile 00003 to C:\APP\sali\ORADATA\TEST\UNDOTBS
01.DBF
channel ORA_DISK_1: restoring datafile 00004 to C:\APP\sali\ORADATA\TEST\USERS01
.DBF
channel ORA_DISK_1: restoring datafile 00005 to C:\APP\sali\ORADATA\TEST\EXAMPLE
01.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\sali\FLASH_RECOVERY_AREA\TE
ST\BACKUPSET\2009_06_24\O1_MF_NNNDF_TAG20090624T103303_544G9GF8_.BKP
channel ORA_DISK_1: piece handle=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\BACKUPSET\
2009_06_24\O1_MF_NNNDF_TAG20090624T103303_544G9GF8_.BKP tag=TAG20090624T103303
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 27-JUN-09

RMAN> recover database until scn 1997213;

Starting recover at 27-JUN-09
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 52 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_24\O1_MF_1_52_544GG9LY_.ARC
archived log for thread 1 with sequence 53 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_24\O1_MF_1_53_544JOW42_.ARC
archived log for thread 1 with sequence 54 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_24\O1_MF_1_54_544KG613_.ARC
archived log for thread 1 with sequence 55 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_24\O1_MF_1_55_545KFV3K_.ARC
archived log for thread 1 with sequence 56 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_25\O1_MF_1_56_546YP173_.ARC
archived log for thread 1 with sequence 57 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_57_549HVW3T_.ARC
archived log for thread 1 with sequence 58 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_58_549SRJFK_.ARC
archived log for thread 1 with sequence 1 is already on disk as file C:\APP\sali
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_1_54BXDKH5_.ARC
archived log for thread 1 with sequence 2 is already on disk as file C:\APP\sali
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_2_54BXDLGO_.ARC
archived log for thread 1 with sequence 3 is already on disk as file C:\APP\sali
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_3_54BXDR2G_.ARC
archived log for thread 1 with sequence 1 is already on disk as file C:\APP\sali
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_1_54BYK6R6_.ARC
archived log for thread 1 with sequence 2 is already on disk as file C:\APP\sali
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_2_54BYK8RB_.ARC
archived log for thread 1 with sequence 3 is already on disk as file C:\APP\sali
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_3_54BYKG92_.ARC
archived log for thread 1 with sequence 4 is already on disk as file C:\APP\sali
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_4_54BYKJ7O_.ARC
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_52_544GG9LY_.ARC thread=1 sequence=52
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_53_544JOW42_.ARC thread=1 sequence=53
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_54_544KG613_.ARC thread=1 sequence=54
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_55_545KFV3K_.ARC thread=1 sequence=55
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
5\O1_MF_1_56_546YP173_.ARC thread=1 sequence=56
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_57_549HVW3T_.ARC thread=1 sequence=57
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_58_549SRJFK_.ARC thread=1 sequence=58
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_1_54BXDKH5_.ARC thread=1 sequence=1
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_2_54BXDLGO_.ARC thread=1 sequence=2
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_3_54BXDR2G_.ARC thread=1 sequence=3
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_1_54BYK6R6_.ARC thread=1 sequence=1
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_2_54BYK8RB_.ARC thread=1 sequence=2
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_3_54BYKG92_.ARC thread=1 sequence=3
archived log file name=C:\APP\sali\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_4_54BYKJ7O_.ARC thread=1 sequence=4
unable to find archived log
archived log thread=1 sequence=5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/27/2009 11:58:56
RMAN-06054: media recovery requesting unknown archived log for thread 1 with seq
uence 5 and starting SCN of 1952116

RMAN> alter database open resetlogs;

database opened

SQL> conn scott/tiger
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE

Friday, September 11, 2009

Oracle Advanced Security

INTRODUCTION
Oracle introduced Advance Security option on 8.x onwards. It is only available with Enterprise Edition. Advance Security option will protect the network, if the hacker is using sniffer any other tools, ASO features will protect enterprise networks and securely extend corporate networks to the Internet. ASO formerly known as ANO.
Writing encryption/decryption on application side for each packet over the network is difficult task Using any sniffer the data can be captured via NIC, using oracle ASO will prevent the each packets routed NIC will be encrypted on server side and decrypted on client side.
Any oraganisation the biggest threat is hacking. As of now, most of the intruder within the organization only, since intruder knew the in and out of Infrastructure, it may be easy task to hack the informations. ASO can prevent some extend of the network hacking.

The ASO protects the the following area i.e
Data Privacy, Data Integriy,Authentication and Authorization.

What is data Privacy ?
Data Privacy to guarantee you that data is not disclosed during transmission of the packets via network.

What is data integrity?
The data should not be modified during the transmission of the packets.

What is Authorization?
It is the privileges to access the objects for a user.

What is Authentication?
It will be validated that users,hosts and clients identities are correctly known. It also ensure the Single Sign on.
ASO supports Connection Manager also.

The ASO supports the following authentication “SSL,RADIUS,Kerberos, Entrust,CyberSafe, SmartCards, TokenCards, Bull ISM, Biometric,etc”.
The ASO configured on sqlnet.ora of the client and as well as the Server.
The default list of algorithms is defined on a client is RC4_40, RC4_56
On server side RC4_40, RC4_56, RC4_128
The Installed Oracle Advanced Security option/Security products are:
DES40 40-bit encryption algorithm
DES 56-bit encryption algorithm
MD5 crypto-checksumming algorithm
How to configure the ASO:

On client machine sqlnet.ora or application server sqlnet.ora
-------------------------------------
sqlnet.encryption_client = required
sqlnet.crypto_seed=”encryptionkey”
sqlnet.encryption_types_client = (RC4_56)
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUESTED
SQLNET.CRYPTO_CHECKSUM_SERVER = ACCEPTED

On Server machine sqlnet.ora
---------------------------
sqlnet.encryption_server = required
sqlnet.crypto_seed=”encryptionkey”
sqlnet.encryption_types_server = (RC4_56)
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUESTED
SQLNET.CRYPTO_CHECKSUM_SERVER = ACCEPTED

One can find out the whether the encryptions are enabled or not setting the client or server sid sqlnet tracing.
Add on sqlnet.ora trace_level_client=16
Connect to sqlplus and execute this command select * from v$option.
ASO supported on 11g, the various encryptions are DES Encryption, RC4 Encryption , Triple-DES Encryption, Advanced Encryption Standard.
Oracle 11g supports now Secure Hash Algorithm (SHA-1)

License
License to be procured for ANO from Oracle.

REGEXP_LIKE

select ename,sal,deptno from scott.emp
where
ename not like '%ED%'
and
ename not like '%CD%'
and
ename not like '%FD%'
and
ename not like '%RD%'
and
ename not like '%SD%'
and
ename not like '%KD%' ;

you can change above query very simply--------

select ename,sal,deptno
from scott.emp
where not REGEXP_LIKE(ename,'RD|ED|CD|FD|SD|KD')


select ename,sal,deptno from emp
where not regexp_like(ename, '(E|C|F|R|S|K)D');


Also you can do,
SELECT ename, sal, deptno
FROM emp_test
WHERE NOT REGEXP_LIKE (ename, '[ECFRSK]D');


A longer format using like is,

select ename, sal, deptno
from emp, table (sys.dbms_debug_vc2coll (
'ED',
'CD',
'FD',
'RD',
'SD',
'KD'))
where ename not like '%' || column_value || '%';
Or,select ename,sal,deptno from emp
where not regexp_like(ename, '(ED|CD|FD|RD|SD|KD)');

GROUP WISE QUERY

select deptno,
decode(grouping(ename),
1, 'Total dept '||deptno||': '||count(*) over(partition by deptno),
deptno) dept,
decode(grouping(ename),
1, to_number(null),
row_number() over(partition by deptno order by ename)) rn,
ename
from emp
group by rollup(deptno,ename)
having grouping(deptno) = 0
order by deptno, rn
/

SEPARATED BY COMMA IN QUERY

create table test_018 (emp_id number, skill varchar2(100));

insert into test_018 values (131, 'oracle,vb,java');
insert into test_018 values (123, 'c,c++,vb,java,db2');
insert into test_018 values (111, 'oracle');

commit

select * from test_018

select emp_id,substr(skill, INSTR(skill, ',', 1, LEVEL ) + 1,
INSTR(skill, ',', 1, LEVEL+1) -
INSTR(skill, ',', 1, LEVEL) -1 ) skill
FROM (
SELECT ','||skill||',' AS skill ,emp_id
FROM test_018
)
CONNECT BY PRIOR emp_id = emp_id
AND INSTR (skill, ',', 1, LEVEL+1) > 0
AND PRIOR dbms_random.string ('p', 10) IS NOT NULL



select emp_id
,substr(skill,instr(skill,',',1,lvl)+1
,instr(skill,',',1,lvl+1) - instr(skill,',',1,lvl)-1) sub_skill
from (select emp_id, ','||skill||',' skill from test_018)
,(select level lvl from dual connect by level <= (select max(length(skill) - length(replace(skill,',','')))-1 from (select ','||skill||',' skill from test_018)))
where lvl <= length(skill) - length(replace(skill,',',''))-1
order by emp_id,lvl

UNDO SIZE

Two Questions:
--------------

1. How would I calculate how much Undo space I need for this alter table ?

2. Is there anything I can do to minimise the amount of Undo needed when doing the alter table ?



1. Not easy, about ("column size"+"rowid size"+overhead)*"number of rows"+"other overhead" + wasted space to block rounding.
But of course there maybe row migration (due to increase in size of the rows) and depending on the number of migrated rows this may be (far) above.
Here's a quick test:

SQL> create table t (col char(12));

Table created.

SQL> insert into t select dummy from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> @mystat 'undo change vector size'

NAME VALUE
-------------------------------------------------- ----------
undo change vector size 2469404

SQL> alter table t modify col char(13);

Table altered.

SQL> @mystat2 'undo change vector size'

NAME VALUE DIFF
-------------------------------------------------- ---------- ----------
undo change vector size 162542612 160073208

SQL> @calc 160073208/1000000

160073208/1000000 = 160.073208
It took 160 bytes per row.

2. Nothing, it is a row per row process. but you could create a new table with "create table as select" drop the old one and rename the new one.
This is far far faster but you have to then recreate all indexes, grants, and so on and you need to have the double of the space.

SQL> create table t (col char(12));

Table created.

SQL> insert into t select dummy from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> @mystat 'undo change vector size'

NAME VALUE
-------------------------------------------------- ----------
undo change vector size 167683724

SQL> create table t2 as select cast(col as char(13)) col from t;

Table created.

SQL> @mystat2 'undo change vector size'

NAME VALUE DIFF
-------------------------------------------------- ---------- ----------
undo change vector size 167713420 29696

SQL> select count(*) from t2;
COUNT(*)
----------
1000000

1 row selected.

SQL> set lines 65
SQL> desc t2
Name Null? Type
-------------------------------- -------- ----------------------
COL CHAR(13)

SQL> drop table t;

Table dropped.

SQL> rename t2 to t;

Table renamed.

SQL> desc t
Name Null? Type
-------------------------------- -------- ----------------------
COL CHAR(13)

DATE WISE BALANCE REPORT FUNCTION

function CF_debitFormula return Number is
s_debit number(20,3);
begin

If :P28 is Null then
Select nvl(SUM(DECODE(DBCRCODE,'D',JVLCAMNT,-Jvlcamnt)),0) credit
--- sum(decode(dbcrcode,'C',nvl(-jvlcamnt,0),nvl(jvlcamnt,0))) Credit
into :P28
From STTRNdtl
WHERE compcode =decode(:p0,'ALL',compcode,:p0)
AND doctdate < :p11
AND SUBSTR(acctcode,1,5)=:p1
Group By SUBSTR(acctcode,1,5);
End if;


Select nvl(SUM(DECODE(DBCRCODE,'D',JVLCAMNT,-jvlcamnt)),0) credit
---sum(decode(dbcrcode,'C',nvl(-jvlcamnt,0),nvl(jvlcamnt,0))) Credit
into s_debit
from actrndtl
where compcode =decode(:p0,'ALL',compcode,:p0)
and doctdate = :stholdte
and SUBSTR(acctcode,1,5)=:p1
group by substr(acctcode,1,5);

:P28 := nvl(:P28,0) + s_debit;

If :P28 > 0 then
return :P28;
Else
return 0;
End if;
Exception
when others then
If :P28 > 0 then
return :P28;
End If;
Return 0;
End;


------PROCEDURE

CREATE OR REPLACE PROCEDURE dpr_datewise_gl_balance (
p_date OUT DATE,
p_acctcode OUT VARCHAR2,
p_bal OUT NUMBER
) IS
s_datefrom DATE := TO_DATE ('01102008', 'ddmmrrrr');
s_tofrom DATE := TO_DATE ('31102008', 'ddmmrrrr');
s_opnbal NUMBER := 0;
s_curbal NUMBER := 0;
s_incrementbal NUMBER := 0;

CURSOR c_atchrtac IS
SELECT acctcode, acctname
FROM stchrtac
WHERE acctcode IN ('10200')
ORDER BY 1;
BEGIN
FOR i IN c_atchrtac LOOP
SELECT NVL (SUM (DECODE (dbcrcode, 'C', jvlcamnt, -jvlcamnt)), 0)
INTO s_opnbal
FROM sttrndtl
WHERE compcode = DECODE (:p0, 'ALL', compcode, :p0)
AND SUBSTR (acctcode, 1, 5) = i.acctcode
AND doctdate < s_datefrom;

WHILE s_datefrom <= s_tofrom LOOP
p_acctcode := i.acctcode;
p_date := s_datefrom;

SELECT (NVL (SUM (DECODE (dbcrcode, 'C', jvlcamnt, -jvlcamnt)), 0))
INTO s_curbal
FROM sttrndtl
WHERE SUBSTR (acctcode, 1, 5) = i.acctcode AND doctdate = p_date;

s_incrementbal := s_incrementbal + s_curbal;
p_bal := s_opnbal + s_incrementbal;
DBMS_OUTPUT.put_line (p_date || ' ' || p_acctcode || ' ' || p_bal);
s_datefrom := s_datefrom + 1;
END LOOP;
END LOOP;
END;
/

----ANOTHER

var p_acctcode varchar2(100)
var p_date varchar2(100)
var p_bal number
set serverout on


CREATE OR REPLACE PROCEDURE dpr_datewise_gl_balance (
p_date OUT DATE,
p_acctcode OUT VARCHAR2,
p_bal OUT NUMBER
) IS
s_datefrom DATE := TO_DATE ('01102008', 'ddmmrrrr');
s_tofrom DATE := TO_DATE ('31102008', 'ddmmrrrr');
s_opnbal NUMBER := 0;
s_curbal NUMBER := 0;
s_incrementbal NUMBER := 0;

CURSOR c_atchrtac IS
SELECT acctcode, acctname
FROM stchrtac
WHERE acctcode IN ('10200')
ORDER BY 1;
BEGIN
FOR i IN c_atchrtac LOOP
SELECT NVL (SUM (DECODE (dbcrcode, 'C', jvlcamnt, -jvlcamnt)), 0)
INTO s_opnbal
FROM sttrndtl
WHERE SUBSTR (acctcode, 1, 5) = i.acctcode AND doctdate < s_datefrom;

WHILE s_datefrom <= s_tofrom LOOP
p_acctcode := i.acctcode;
p_date := s_datefrom;

SELECT (NVL (SUM (DECODE (dbcrcode, 'C', jvlcamnt, -jvlcamnt)), 0))
INTO s_curbal
FROM sttrndtl
WHERE SUBSTR (acctcode, 1, 5) = i.acctcode AND doctdate = p_date;

s_incrementbal := s_incrementbal + s_curbal;
p_bal := s_opnbal + s_incrementbal;
DBMS_OUTPUT.put_line (p_date || ' ' || p_acctcode || ' ' || p_bal);
s_datefrom := s_datefrom + 1;
END LOOP;
END LOOP;
END;
/
exec dpr_datewise_gl_balance(:p_date,:p_acctcode,:p_bal);


--For Check

SELECT SUBSTR (acctcode, 1, 5) acctcode,
NVL (SUM (DECODE (dbcrcode, 'C', jvlcamnt, -jvlcamnt)), 0) balance
FROM sttrndtl
WHERE SUBSTR (acctcode, 1, 5) IN ('10100', '10200')
AND doctdate <= TO_DATE ('30092008', 'ddmmrrrr')
GROUP BY SUBSTR (acctcode, 1, 5)

PREVIOUS VALUE RETURN IN QUERY

select dummy_date,nvl(glcode,lag(glcode) over (order by dummy_date)) glcode,nvl(amount,lag(amount) over ( order by dummy_date))amount
from (
select dummy_date,nvl(glcode,lag(glcode) over (order by dummy_date)) glcode,nvl(amount,lag(amount) over ( order by dummy_date))amount
from (
select dummy_date,nvl(glcode,lag(glcode) over (order by dummy_date)) glcode,nvl(amount,lag(amount) over ( order by dummy_date))amount
from (
select dummy_date,nvl(glcode,lag(glcode) over (order by dummy_date)) glcode, nvl(amount,lag(amount) over ( order by dummy_date))amount
from (
SELECT doctdate, SUBSTR (b.acctcode, 1, 5) || ' : ' || b.acctname glcode,
nvl(sum(decode(dbcrcode,'C',jvlcamnt,-jvlcamnt)),0) AMOUNT
FROM sttrndtl a, stchrtac b
WHERE
a.compcode=decode(:p0,'ALL',a.compcode,:p0)
AND doctdate BETWEEN :p11 AND :p12
AND b.acctcode =:p1
AND grupcode = '001'
AND substr(a.acctcode,1,5)= b.acctcode
GROUP BY doctdate, b.acctname,b.acctcode) a,(select trunc(:p11,'MM')-1+rownum dummy_date
from all_objects
where rownum <= (:p12+1)-:p11) b
where a.doctdate(+)=b.dummy_date
ORDER BY dummy_date
)))

---PROCEDURE

CREATE OR REPLACE PROCEDURE dpr_datewise_gl_balance (
p_date OUT DATE,
p_acctcode OUT VARCHAR2,
p_bal OUT NUMBER
) IS
s_datefrom DATE := TO_DATE ('01102008', 'ddmmrrrr');
s_tofrom DATE := TO_DATE ('31102008', 'ddmmrrrr');
s_opnbal NUMBER := 0;
s_curbal NUMBER := 0;
s_incrementbal NUMBER := 0;

CURSOR c_atchrtac IS
SELECT acctcode, acctname
FROM stchrtac
WHERE acctcode IN ('10200')
ORDER BY 1;
BEGIN
FOR i IN c_atchrtac LOOP
SELECT NVL (SUM (DECODE (dbcrcode, 'C', jvlcamnt, -jvlcamnt)), 0)
INTO s_opnbal
FROM sttrndtl
WHERE SUBSTR (acctcode, 1, 5) = i.acctcode AND doctdate < s_datefrom;

WHILE s_datefrom <= s_tofrom LOOP
p_acctcode := i.acctcode;
p_date := s_datefrom;

SELECT (NVL (SUM (DECODE (dbcrcode, 'C', jvlcamnt, -jvlcamnt)), 0))
INTO s_curbal
FROM sttrndtl
WHERE SUBSTR (acctcode, 1, 5) = i.acctcode AND doctdate = p_date;

s_incrementbal := s_incrementbal + s_curbal;
p_bal := s_opnbal + s_incrementbal;
DBMS_OUTPUT.put_line (p_date || ' ' || p_acctcode || ' ' || p_bal);
s_datefrom := s_datefrom + 1;
END LOOP;
END LOOP;
END;

MATRIX QUERY

CREATE TABLE temp(tablekey varchar(10),fieldname varchar(15),olddata varchar(15));

Records are :


INSERT INTO temp values('C18CVBNK89','ACTIVE','1');
INSERT INTO temp values('C18CVBNK89','ENDDATE','NULL');
INSERT INTO temp values('C18CVBNK89','ID','01Z041782NH');
INSERT INTO temp values('C18CVBNK89','STARTDATE','01-JAN-00');
INSERT INTO temp values('C18CVBNK89','TYPE_RTK','C1X111AP59');
INSERT INTO temp values('C18CVBNK89','ENDDATE','27-OCT-05');
COMMIT;


select * from temp


select tablekey , (case when fieldname=fieldname then lag(olddata) over(order by tablekey) else fieldname end )se ,
lag(olddata) over(order by tablekey) lag1 ,lead(fieldname) over(order by tablekey) le,lag(olddata) over(order by tablekey) lag1
from temp


select tablekey , (case when fieldname=fieldname then fieldname else lag(olddata) over(order by tablekey) end ) r
, lag(olddata) over(order by tablekey) se ,olddata
--- lead(fieldname) over(order by tablekey) le
from temp


SELECT a.tablekey,a.fieldname,a.olddata
FROM temp a
WHERE EXISTS (SELECT b.fieldname FROM temp b
WHERE b.tablekey = a.tablekey)
GROUP BY a.tablekey,a.fieldname,a.olddata
ORDER BY a.tablekey,a.fieldname;





/* Formatted on 2008/10/16 18:06 (Formatter Plus v4.8.8) */
SELECT tablekey, MAX (DECODE (fieldname, 'ACTIVE', olddata)) active,
MAX (DECODE (fieldname, 'ENDDATE', olddata)) enddate,
MAX (DECODE (fieldname, 'ID', olddata)) ID,
MAX (DECODE (fieldname, 'STARTDATE', olddata)) startdate,
MAX (DECODE (fieldname, 'TYPE_RTK', olddata)) type_rtk
FROM temp
GROUP BY tablekey
ORDER BY tablekey


SELECT tablekey, MAX (DECODE (rn, 1, olddata)) AS "ACTIVE",
MAX (DECODE (rn, 2, olddata)) AS "ENDDATE",
MAX (DECODE (rn, 3, olddata)) AS "ID",
MAX (DECODE (rn, 4, olddata)) AS "STARTDATE",
MAX (DECODE (rn, 5, olddata)) AS "TYPE_RTK",
MAX (DECODE (rn, 6, olddata)) AS "ENDATE_1"
FROM (SELECT temp.*, ROW_NUMBER() OVER (PARTITION BY TABLEKEY ORDER BY TABLEKEY) RN
FROM temp)
GROUP BY tablekey


SELECT job,
sum(decode(deptno,10,sal)) DEPT10,
sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,
sum(decode(deptno,40,sal)) DEPT40
FROM scott.emp
GROUP BY job


SELECT job,
sum(decode(deptno,10,sal)) DEPT10,
sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,
sum(decode(deptno,40,sal)) DEPT40
FROM scott.emp
GROUP BY job


select *
from (
select
max(decode(attrName, 'FIRST_NAME', value, null)) first_name,
max(decode(attrName, 'LAST_NAME', value, null)) last_name,
max(decode(attrName, 'DATE_OF_BIRTH', value, null))
date_of_birth
from objects, object_attributes, attributes
where attributes.attrName in ( 'FIRST_NAME',
'LAST_NAME', 'DATE_OF_BIRTH' )
and object_attributes.attrId = attributes.attrId
and object_attributes.oid = objects.oid
and objects.name = 'PERSON'
group by objects.oid
)
where last_name = 'Smith'
or date_of_birth like '%-mar-%'

DATE OF ONE MONTH

SELECT TRUNC (TO_DATE (:p11, 'DDMMYYYY'), 'MM') - 1 + ROWNUM date_month
FROM all_objects
WHERE ROWNUM <=
ADD_MONTHS (TRUNC (TO_DATE (:p11, 'DDMMYYYY'), 'MM'), 1)
- TRUNC (TO_DATE (:p11, 'DDMMYYYY'), 'MM')


SELECT TRUNC (TO_DATE (:p11, 'DDMMYYYY'), 'MM') - 1 + ROWNUM date_month
FROM all_objects
WHERE ROWNUM <=
ADD_MONTHS (TRUNC (TO_DATE (:p11, 'DDMMYYYY'), 'MM'), 1)
- TRUNC (TO_DATE (:p11, 'DDMMYYYY'), 'MM')

PL/SQL code for Email validation

DECLARE
t_valid NUMBER(1);
t_totallen NUMBER(2);
t_counter NUMBER(2):=0;
t_atpos NUMBER(2):= 1;
i NUMBER(2) := 1;
t_pointpos NUMBER(2):= 1;

mail_ch VARCHAR2(1);

result number; --:GR

BEGIN

t_totallen := LENGTH(:text_item_email);
t_counter := t_totallen;
i := 1;
t_valid := 1;

-------------------------------------------------------------------------------------

IF LENGTH(ltrim(rtrim(:text_item_email))) = 0 THEN
t_valid := 0;
ELSE
---------------------------------------------------------------------------------------
--This is to check special characters are present or not in the email ID
t_counter := t_totallen;

WHILE t_counter > 0
LOOP
mail_ch := substr(:text_item_email,i,1);
i := i+1;
t_counter := t_counter -1;

IF mail_ch IN (' ','!','#','$','%','^','&','*','(',')','-','','"',
'+','|','{','}','[',']',':','>','<','?','/','\','=') THEN
t_valid := 0;
EXIT;
END IF;

END LOOP;

---------------------------------------------------------------------------------------
--This is to check more than one '@' character present or not

t_atpos := instr(:text_item_email,'@',1,2) ;

IF t_atpos > 1 then
t_valid := 0;
END IF;

---------------------------------------------------------------------------------------
--This is to check at minimum and at maximum only one '@' character present

t_atpos := instr(:text_item_email,'@',1) ;

IF t_atpos IN (0,1) THEN
t_valid := 0;
END IF;

---------------------------------------------------------------------------------------
--This is to check at least one '.' character present or not

t_pointpos := instr(:text_item_email,'.',1) ;

IF t_pointpos IN (0,1) THEN
t_valid := 0;
END IF;

---------------------------------------------------------------------------------------

--This is to check at least one character is present between @ and '.' :GR

t_atpos := instr(:text_item_email,'@',1) ;
t_pointpos := instr(:text_item_email,'.',1) ;

IF t_pointpos-t_atpos<=1 THEN
t_valid := 0;
END IF;

---------------------------------------------------------------------------------------

--This is to check at least one character is present after '.' :GR

t_pointpos := instr(:text_item_email,'.',1) ;

IF t_totallen-t_pointpos<=0 THEN
t_valid := 0;
END IF;

---------------------------------------------------------------------------------------

END IF;

if(t_valid=0) then
message('Invalid Email');
result:=show_alert('alert_email');
raise form_trigger_failure;
end if;
END;

---***PL/SQL code for Name Validation***---
DECLARE
t_valid NUMBER(1);
t_totallen NUMBER(2);
t_counter NUMBER(2):=0;
i NUMBER(2) := 1;

name_ch VARCHAR2(1);
--name_ch_ascii NUMBER;

result number; --:GR

BEGIN

t_totallen := LENGTH(:text_item_first_name);
t_counter := t_totallen;
i := 1;
t_valid := 1;

-------------------------------------------------------------------------------------

IF LENGTH(ltrim(rtrim(:text_item_first_name))) = 0 THEN
t_valid := 0;
ELSE
---------------------------------------------------------------------------------------
--This is to check if each character in name lies in the valid ascii range
t_counter := t_totallen;

WHILE t_counter > 0
LOOP
name_ch := upper(substr(:text_item_first_name,i,1));
--name_ch_ascii := convert(name_ch,'US7ASCII');
i := i+1;
t_counter := t_counter -1;

--IF name_ch_ascii not between 65 and 90 THEN
IF name_ch not between 'A' and 'Z' THEN
t_valid := 0;
EXIT;
END IF;

END LOOP;
---------------------------------------------------------------------------------------
END IF;

if(t_valid=0) then
message('Invalid First Name');
result:=show_alert('alert_first_name');
raise form_trigger_failure;
end if;
END;

EMAIL FORMAT VALIDATION

CREATE OR REPLACE FUNCTION xx_check_email (l_user_name IN VARCHAR2)
RETURN VARCHAR2
IS
l_dot_pos NUMBER;
l_at_pos NUMBER;
l_str_length NUMBER;
BEGIN
l_dot_pos := INSTR (l_user_name, '.');
l_at_pos := INSTR (l_user_name, '@');
l_str_length := LENGTH (l_user_name);

IF ( (l_dot_pos = 0)
OR (l_at_pos = 0)
OR (l_dot_pos = l_at_pos + 1)
OR (l_at_pos = 1)
OR (l_at_pos = l_str_length)
OR (l_dot_pos = l_str_length)
)
THEN
RETURN 'FAILURE';
END IF;

IF INSTR (SUBSTR (l_user_name, l_at_pos), '.') = 0
THEN
RETURN 'FAILURE';
END IF;

RETURN 'SUCCESS';
END xx_check_email;


select INSTR (:l_user_name, '.'),
INSTR (:l_user_name, '@'),
LENGTH (:l_user_name)
from dual;


begin
if owa_pattern.match(p_email,'^\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}'||
'@\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}$') then
dbms_output.put_line('email válido : '||p_email);
return true;
else
dbms_output.put_line('email inválido : '||p_email);
return false;
end if;
end f_valida_email;

RECORD TYPE

SQL> create type item_rec is object(item varchar2(20),desc_ varchar2(30));
2 /

Type created

SQL> create type item_list is type of item_rec;
2 /

Warning: Type created with compilation errors

SQL> create type item_list is table of item_rec;
2 /

Type created

SQL>

second In a procedure

CREATE OR REPLACE PROCEDURE tst_bom
(t_Result OUT ITEM_LIST)
IS
i NUMBER;
TYPE rec_Item IS RECORD(Item VARCHAR2(20),
desc_ VARCHAR2(30));
TYPE rec_Tab IS TABLE OF REC_ITEM;
Item_Tab REC_TAB;
BEGIN
FOR i IN 1.. 10 LOOP
Item_Tab(i).Item := 'item'
||To_Char(i);

Item_Tab(i).desc_ := 'description'
||To_Char(i);
END LOOP;
END tst_bom;


DECLARE
2 i NUMBER;
3 Item_Tab ITEM_LIST := item_list();
4 t_Result ITEM_LIST;
5 BEGIN
6 FOR i IN 1.. 10 LOOP
7 item_tab.extend;
8 item_tab(i) := item_rec('item'||To_Char(i),'description'||To_Char(i));
9 END LOOP;
10
11 select item_rec(item,desc_) bulk collect into t_result from table(item_tab);
12 for i in 1..item_tab.count loop
13 dbms_output.put_line(t_result(i).item||' '||t_result(i).desc_);
14 end loop;
15 END;
16 /

SCRIFT OF TABLESPACE

set pagesize 100

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', tablespace_name)
FROM DBA_tablespaces;


export - import with ROWS=N also work.

WITH CLAUSE IN QUERY

WITH TEMP_TAB AS
2 (SELECT 'ABC' Col_1 FROM DUAL
3 UNION
4 SELECT 'DEF' FROM DUAL
5 UNION
6 SELECT 'GHI' FROM DUAL
7 )
8 SELECT Col_1 FROM TEMP_TAB
9* ORDER BY Col_1
10 ;

COL
---
ABC
DEF
GHI

3 rows selected.

SQL> ed
Wrote file afiedt.buf

1 WITH TEMP_TAB AS
2 (SELECT 'ABC' Col_1 FROM DUAL
3 UNION
4 SELECT 'DEF' FROM DUAL
5 UNION
6 SELECT 'GHI' FROM DUAL
7 )
8 SELECT
9 DECODE(COL_1, 'ABC', 2, 'DEF', 3, 'GHI', 1, 999)
10 SORT_1, COL_1 FROM TEMP_TAB
11* ORDER BY SORT_1
12 ;

SORT_1 COL
---------- ---
1 GHI
2 ABC
3 DEF

AWR RETENTION PERIOD (MINUTE)

select
extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) "Snapshot Interval",
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;

SYSAUX OCCUPANTS

SELECT *
FROM v$sysaux_occupants
ORDER BY space_usage_kbytes DESC

SELF JOIN

SELECT s1.student_id "id", s1.student_total_fee "fee1",
SUM (s2.student_total_fee) "fee2"
FROM student s2, student s1
WHERE s1.student_id IN ('133', '330', '925')
AND s2.student_id <= s1.student_id
AND s2.student_id IN ('133', '330', '925')
GROUP BY s1.student_id, s1.student_total_fee
ORDER BY 1;

RANGE INPUT RESTRICTION

BEGIN
FOR cur_r IN (SELECT frm_vin, to_vin
FROM vin_upd
WHERE gr_sub_cd = :BLOCK.gr_sub_cd) LOOP
IF :BLOCK.frm_vin BETWEEN cur_r.frm_vin AND cur_r.to_vin
OR :BLOCK.to_vin BETWEEN cur_r.frm_vin AND cur_r.to_vin THEN
MESSAGE ('Invalid range');
RAISE form_application_error;
END IF;
END LOOP;
END;

buffer_cahech_hit_ritio

SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads' ;

HINT

A hint is code embedded into a SQL statement suggesting to Oracle how it should be processed. Some examples: ALL_ROWS, FIRST_ROWS, CHOOSE, RULE, INDEX, FULL, ORDERED, STAR.

 Hint syntax
Hints must be included into a comment followed by a +-sigh right after the first keyword of the statement. Examples:

--+RULE
/*+RULE */
 [edit]Available hints
Some of the more useful hints are:

ORDERED - usually with USE_NL to get Oracle to not hash join
INDEX(t index_name) - where Oracle chooses the wrong index over the correct one
NO_INDEX - prevent an index from being used
INDEX_COMBINE - merging bitmap indexes (use when Oracle does not merge bitmap indexes)
FIRST_ROWS(n) - when you only want the first few rows
PARALLEL - to force parallel query on certain specific queries
GATHER_PLAN_STATISTICS - used as a handy sql trace
DYNAMIC_SAMPLING - used as alternative to statistics for large-scale warehouse queries
OPT_PARAM - used to control optimizer behavior at query level (added in 10.2.0.3)
QB_NAME - specify query block name when tuning complex queries. It helps in finding a particular query for troubleshooting (10 and up)
CARDINALITY - give the optimizer better information
 [edit]Some examples
Example suggesting a FULL TABLE SCAN method:

SELECT /*+ FULL(x) */ FROM tab1 x WHERE col1 = 10;
Suggest that Oracle uses a specific index:

SELECT /*+ INDEX(x emp_idx1) */ ... FROM scott.emp x...
Suggest that Oracle DOES NOT USE a specific index:

SELECT /*+ NO_INDEX(x emp_idx1) */ ... FROM scott.emp x...

ACTIVE_SESSION_HISTORY

--GV$ACTIVE_SESSION_HISTORY
SELECT /*+ no_merge ordered use_nl(s,a) */
a.inst_id, s.sample_id, s.sample_time, a.session_id, a.session_serial#,
a.user_id, a.sql_id, a.sql_child_number, a.sql_plan_hash_value,
a.force_matching_signature, a.sql_opcode,
DECODE (a.plsql_entry_object_id,
0, TO_NUMBER (NULL),
a.plsql_entry_object_id
),
DECODE (a.plsql_entry_object_id,
0, TO_NUMBER (NULL),
a.plsql_entry_subprogram_id
),
DECODE (a.plsql_object_id, 0, TO_NUMBER (NULL), a.plsql_object_id),
DECODE (a.plsql_object_id, 0, TO_NUMBER (NULL), a.plsql_subprogram_id),
a.service_hash,
DECODE (a.session_type, 1, 'FOREGROUND', 2, 'BACKGROUND', 'UNKNOWN'),
DECODE (a.wait_time, 0, 'WAITING', 'ON CPU'),
DECODE (a.qc_session_id, 0, TO_NUMBER (NULL), a.qc_session_id),
DECODE (a.qc_session_id, 0, TO_NUMBER (NULL), a.qc_instance_id),
(CASE
WHEN a.blocking_session BETWEEN 4294967291 AND 4294967295 THEN TO_NUMBER
(NULL
)
ELSE a.blocking_session
END
),
(CASE
WHEN a.blocking_session = 4294967295 THEN 'UNKNOWN'
WHEN a.blocking_session = 4294967294 THEN 'GLOBAL'
WHEN a.blocking_session = 4294967293 THEN 'UNKNOWN'
WHEN a.blocking_session = 4294967292 THEN 'NO HOLDER'
WHEN a.blocking_session = 4294967291 THEN 'NOT IN WAIT'
ELSE 'VALID'
END
),
(CASE
WHEN a.blocking_session BETWEEN 4294967291 AND 4294967295 THEN TO_NUMBER
(NULL
)
ELSE a.blocking_session_serial#
END
),
DECODE (a.wait_time, 0, a.event, NULL),
DECODE (a.wait_time, 0, a.event_id, NULL),
DECODE (a.wait_time, 0, a.event#, NULL), a.seq#, a.p1text, a.p1,
a.p2text, a.p2, a.p3text, a.p3,
DECODE (a.wait_time, 0, a.wait_class, NULL),
DECODE (a.wait_time, 0, a.wait_class_id, NULL), a.wait_time,
a.time_waited, a.xid, a.current_obj#, a.current_file#,
a.current_block#, a.program, a.module, a.action, a.client_id
FROM x$kewash s, x$ash a
WHERE s.sample_addr = a.sample_addr
AND s.sample_id = a.sample_id
AND s.sample_time = a.sample_time

ROUND CEIL FLOOR ETC..

SELECT CEIL(12345.67) FROM dual; --Smallest integer greater than or equal to a decimal value

SELECT floor(12345.67) FROM dual;--- Returns the largest integer less than or equal to a decimal value

SELECT round(12345.67) FROM dual;

SELECT trunc(12345.67) FROM dual;

SELECT GREATEST(9, 67.6, 10) FROM dual;

SELECT LEAST(9, 67.6,10) FROM dual;


SELECT MEDIAN(initial_extent) FROM all_tables ; ---Returns the middle value of a set

SELECT EXP(2) FROM dual;

INTEREST RATE CALCULATION

select (((:amount*:int_rate)/100)/360)*:how_many_days interest from dual

BLOCK USER KILL

PROCEDURE dpr_user_locks IS
CURSOR c_lock IS
SELECT s.SID, s.serial#, s.username, s.machine, s.osuser
FROM v$lock l, v$session s, dba_objects o
WHERE l.SID = s.SID AND o.object_id(+) = l.id1 AND l.BLOCK = 1;

lock_id INTEGER;
lock_status INTEGER;
stmt VARCHAR2 (512);
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

FOR i IN c_lock LOOP
BEGIN
stmt :=
'ALTER SYSTEM KILL SESSION '
|| ''''
|| i.SID
|| ','
|| i.serial#
|| '''';
lock_id := DBMS_SQL.open_cursor;
DBMS_SQL.parse (lock_id, stmt, DBMS_SQL.native);
lock_status := DBMS_SQL.EXECUTE (lock_id);
DBMS_SQL.close_cursor (lock_id);
DBMS_OUTPUT.put_line ( 'User killed for locking '
|| 'SID: '
|| i.SID
|| ', '
|| 'Serial No: '
|| i.serial#
|| ', '
|| 'User Name: '
|| i.username
|| ', '
|| 'Machine Name: '
|| i.machine
|| SUBSTR (i.osuser, INSTR (i.osuser, '\'))
);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.is_open (lock_id) THEN
DBMS_SQL.close_cursor (lock_id);
END IF;

DBMS_OUTPUT.put_line ( 'User can not killed for locking '
|| 'SID: '
|| i.SID
|| ', '
|| 'Serial No: '
|| i.serial#
|| ', '
|| 'User Name: '
|| i.username
|| ', '
|| 'Machine Name: '
|| i.machine
|| SUBSTR (i.osuser, INSTR (i.osuser, '\'))
);
END;
END LOOP;
END;

RANDOM DISPLAY MESSAGE

SELECT quotmesg
-- into vstr
FROM syqutmsg SAMPLE (25)
WHERE ROWNUM = 1;

Oracle database Security

1 Why should databases be secured/ hardened?
2 How does one change an Oracle user's password?
3 How does one create, manage and drop database users?
4 How does one enforce strict password controls?
5 Can one switch to another database user without a password?
6 Why are OPS$ accounts a security risk in a client/server
environment?
7 Managing administrative (privileged) users and password files
8 What is a Virtual Private Database?
9 What is Fine Grained Auditing?
10 What is Oracle Label Security?


 Why should databases be secured/ hardened?
Data is any company's greatest asset and the only "safe" database is one that has nothing in it. A lot of people think that hackers are all outside the firewall which is false. The greatest threat to your database is the person in the cube next to you who has access to it. For this reason databases should be secured to ensure its data is properly protected.

 How does one change an Oracle user's password?
Issue the following SQL command to change a user's password:

ALTER USER IDENTIFIED BY ;
Starting from Oracle 8 you can just type password from SQL*Plus, or if you need to change another user's password, type password user_name. Look at these examples:

SQL> password
Changing password for SCOTT
Old password:
New password:
Retype new password:
SQL> passw scott
Changing password for scott
New password:
Retype new password:
Password changed

Note: Oracle usernames and passwords are not case sensitive in database versions below Oracle 11g.

 How does one create, manage and drop database users?
One can add, drop and manage database users from the Enterprise Manager GUI. The following examples will show how the same can be achieved from the SQL*Plus command prompt:

CREATE USER scott
IDENTIFIED BY tiger -- Assign password
DEFAULT TABLESPACE tools -- Assign space for table and index segments
TEMPORARY TABLESPACE temp; -- Assign sort space
DROP USER scott CASCADE; -- Remove user
After creating a new user, assign the required privileges:

GRANT CONNECT, RESOURCE TO scott;
GRANT DBA TO scott; -- Make user a DB Administrator
Remember to give the user some space quota on its tablespaces:

ALTER USER scott QUOTA UNLIMITED ON tools;
Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. For example, all accounts except SYS and SYSTEM will be locked after creating an Oracle9iDB database using the DB Configuration Assistant (dbca). DBA's must unlock these accounts to make them available to users.

Look at these examples:

ALTER USER scott ACCOUNT LOCK -- lock a user account
ALTER USER scott ACCOUNT UNLOCK; -- unlocks a locked users account
ALTER USER scott PASSWORD EXPIRE; -- Force user to choose a new password

 How does one enforce strict password controls?
By default Oracle's security is not extremely good. For example, Oracle will allow users to choose single character passwords and passwords that match their names and userids. Also, passwords don't ever expire. This means that one can hack an account for years without ever locking the user.

From Oracle 8 one can manage passwords through profiles. Some of the things that one can restrict:

FAILED_LOGIN_ATTEMPTS - failed login attempts before the account is locked
PASSWORD_LIFE_TIME - limits the number of days the same password can be used for authentication
PASSWORD_REUSE_TIME - number of days before a password can be reused
PASSWORD_REUSE_MAX - number of password changes required before the current password can be reused
PASSWORD_LOCK_TIME - number of days an account will be locked after maximum failed login attempts
PASSWORD_GRACE_TIME - number of days after the grace period begins during which a warning is issued and login is allowed
PASSWORD_VERIFY_FUNCTION - password complexity verification script
Look at this simple example:

CREATE PROFILE my_profile LIMIT
PASSWORD_LIFE_TIME 30;
ALTER USER scott PROFILE my_profile;
 Can one switch to another database user without a password?

Users normally use the "CONNECT" statement to connect from one database user to another. However, DBAs can switch from one user to another without a password. Of course it is not advisable to bridge Oracle's security, but look at this example:

SQL> CONNECT / as sysdba
Connected.

SQL> SELECT password FROM dba_users WHERE username='SCOTT';
PASSWORD
--------------- ---------------
F894844C34402B67
SQL> ALTER USER scott IDENTIFIED BY anything;
User altered.

SQL> CONNECT scott/anything
Connected.
OK, we're in. Let's quickly change the password back before anybody notices.

SQL> ALTER USER scott IDENTIFIED BY VALUES 'F894844C34402B67';
User altered.
Note: Also see the su.sql script in the Scripts and Sample Programs section of this site.

 Why are OPS$ accounts a security risk in a client/server environment?
If you allow people to log in with OPS$ accounts from Windows Workstations, you cannot be sure who they really are. With terminals, you can rely on operating system passwords, with Windows, you cannot.

If you set REMOTE_OS_AUTHENT=TRUE in your init.ora file, Oracle assumes that the remote OS has authenticated the user.

If REMOTE_OS_AUTHENT is set to FALSE (recommended), remote users will be unable to connect without a password. IDENTIFIED EXTERNALLY will only be in effect from the local host. Also, if you are using "OPS$" as your prefix, you will be able to log on locally with or without a password, regardless of whether you have identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY.

 Managing administrative (privileged) users and password files
An administrative account is a user that is granted SYSOPER or SYSDBA privileges. Oracle DBAs and operators typically use administrative accounts to manage the database and database instance.

SYSDBA and SYSOPER allow access to a database instance even if it is not running. Control of these privileges is managed outside of the database via password files and special operating system groups (dba on Unix/Linux and ORA_DBA on Windows). External password files are created with the orapwd utility.

Connecting as an administrative user:

If an administrative users belongs to the "dba" group on Unix, or the "ORA_DBA" (ORA_sid_DBA) group on Windows, he/she can connect like this:

connect / as sysdba
No password is required. This is equivalent to the desupported "connect internal" method.

A password is required for "non-secure" administrative access. These passwords are stored in password files. Remote connections via Net8 are classified as non-secure. Look at this example:

connect sys/password as sysdba
Password files:

The Oracle Password File ($ORACLE_HOME/dbs/orapw or orapwSID) stores passwords for users with administrative privileges. One needs to create a password files before remote administrators (like OEM) will be allowed to connect.

Follow this procedure to create a new password file:

Log in as the Oracle software owner
Run command: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd
Shutdown the database (SQLPLUS> SHUTDOWN IMMEDIATE)
Edit the INIT.ORA file and ensure REMOTE_LOGIN_PASSWORDFILE=exclusive is set.
Startup the database (SQLPLUS> STARTUP)[/list]

NOTE: The orapwd utility presents a security risk in that it receives a password from the command line. This password is visible in the process table of many systems. Administrators needs to be aware of this!

Adding users to Password File:

One can select from the SYS.V_$PWFILE_USERS view to see which users are listed in the password file. New users can be added to the password file by granting them SYSDBA or SYSOPER privileges, or by using the orapwd utility.

GRANT SYSDBA TO scott;

 What is a Virtual Private Database?
Oracle 8i introduced the notion of a Virtual Private Database (VPD). A VPD offers Fine-Grained Access Control (FGAC) for secure separation of data. This ensures that users only have access to data that pertains to them. Using this option, one could even store multiple companies' data within the same schema, without them knowing about it.

VPD configuration is done via the DBMS_RLS (Row Level Security) package. Select from SYS.V$VPD_POLICY to see existing VPD configuration.

 What is Fine Grained Auditing?
Fine Grained Auditing (DBMS_FGA) allows auditing records to be generated when certain rows are selected from a table. A list of defined policies can be obtained from DBA_AUDIT_POLICIES. Audit records are stored in DBA_FGA_AUDIT_TRAIL. Look at this example:

-- Add policy on table with autiting condition...
execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');
-- Must ANALYZE, this feature works with CBO (Cost Based Optimizer)
analyze table EMP compute statistics;

select * from EMP where c1 = 11; -- Will trigger auditing
select * from EMP where c1 = 09; -- No auditing

-- Now we can see the statments that triggered the auditing condition...
select sqltext from sys.fga_log$;
delete from sys.fga_log$;
 What is Oracle Label Security?

Oracle Label Security (formerly called Trusted Oracle MLS RDBMS) uses the VPD (Virtual Private Database) feature of Oracle 8i to implement row level security. Access to rows are restricted according to a user's security sensitivity tag or label. Oracle Label Security is configured, controlled and managed from the Policy Manager, an Enterprise Manager-based GUI utility.

FINE GAINED AUDITING

select * from SYS.V_$PWFILE_USERS

execute dbms_fga.add_policy('STLBAS', 'STFETRAN', 'policy1', 'VALDAT > TO_DATE('10022009','DDMMRRRR')');


analyze table STFETRAN compute statistics;


select sqltext from sys.fga_log$;

delete from sys.fga_log$;


-- Add policy on table with autiting condition...
execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');
-- Must ANALYZE, this feature works with CBO (Cost Based Optimizer)
analyze table EMP compute statistics;

select * from EMP where c1 = 11; -- Will trigger auditing
select * from EMP where c1 = 09; -- No auditing

-- Now we can see the statments that triggered the auditing condition...
select sqltext from sys.fga_log$;
delete from sys.fga_log$;

USERs IN PASSWORD FILE

One can select from the SYS.V_$PWFILE_USERS view
to see which users are listed in the password file.

SELECT * FROM SYS.V_$PWFILE_USERS

ANALYZE INFORMATION

CREATE OR REPLACE PROCEDURE halim_gather_stat_schema IS
BEGIN
DBMS_STATS.gather_schema_stats
(ownname => '&STLBAS',
options => 'GATHER',
estimate_percent => DBMS_STATS.auto_sample_size,
method_opt => 'for all columns size auto',
CASCADE => TRUE,
DEGREE => 5
);
END;
/


SELECT a.owner, a.table_name, a.avg_row_len, a.blocks, a.last_analyzed,
a.num_rows, a.tablespace_name, a.row_movement, a.sample_size
FROM all_tables a
WHERE last_analyzed IS NOT NULL AND owner = 'SCOTT'
ORDER BY a.last_analyzed DESC


select *
from dba_indexes
where owner='SCOTT'
AND


select * from all_tables

ODD AND EVEN NUMBER

SELECT *
FROM stempgen
WHERE (ROWID, 1) IN (SELECT ROWID, MOD (ROWNUM, 2)
FROM stempgen)

SELECT *
FROM stempgen
WHERE (ROWID, 0) IN (SELECT ROWID, MOD (ROWNUM, 2)
FROM stempgen)

select mod(rownum,2) from stempgen

SGA SIZE CALCULATION

DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE +

SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFERS + 1MB


SELECT SUM (VALUE) / (1024)
FROM v$parameter
WHERE NAME IN
('db_16k_cache_size',
'db_2k_cache_size',
'db_32k_cache_size',
'db_4k_cache_size',
'db_8k_cache_size',
'db_cache_size',
'db_keep_cache_size',
'db_recycle_cache_size',
'java_pool_size',
'large_pool_size',
'shared_pool_size',
'log_buffer',
'db_block_buffers',
'db_cache_size'
) --+ 1MB


select *---value
from v$parameter
where name ='large_pool_size'