Data pump Shell Script on Oracle Solaris

#CREATE DIRECTORY datapump AS '/opt/oracle/backup/DataPump/Backup';


# !/bin/bash

TODAY=`date`

ORACLE_SID=orcl ; export ORACLE_SID

ORACLE_BASE=/opt/oracle ; export ORACLE_BASE

ORACLE_HOME=/opt/oracle/product/10.2 ; export ORACLE_HOME

PATH=${ORACLE_HOME}/bin:$PATH ; export PATH

EXPFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.dmp; export EXPFILE

ERRFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.err; export ERRFILE

LOGFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.log ; export LOGFILE

SUCC=/opt/oracle/backup/DataPump/Backup/SUCC.txt ; export SUCC



# echo $EXPFILE



find /opt/oracle/backup/DataPump/Backup -name "*.err" -mtime +2 -exec rm {} \;



ps -ef | grep -i smon|grep -v grep|wc -l> oracle.exp



if [ `cat oracle.exp|grep -v grep|grep -i "1"|wc -l` -eq 1 ]; then



[ -f $ERRFILE ]; rm $ERRFILE



expdp "'/ as sysdba'" directory=datapump DUMPFILE=$EXPFILE SCHEMAS=SCOTT,XYZ logfile=$LOGFILE EXCLUDE=STATISTICS parallel=4



fi



find /opt/oracle/backup/DataPump/Backup -name "$ORACLE_SID.export.*" -mtime +3 -exec rm {} \;

oracle trace via database logon trigger

Database trigger


-- GRANT administer DATABASE TRIGGER TO "SCOTT";


CREATE OR REPLACE TRIGGER ON_LOGON_CCCPROD AFTER LOGON ON DATABASE
WHEN ( USER = 'SCOTT' )

declare

v_trace_file_name varchar2(40);

-- LOWER(SYS_CONTEXT('userenv', 'session_user'))||to_char(sysdate,'dd.mm.yyyyhh:mi')
--

BEGIN
v_trace_file_name:= replace(replace(LOWER(SYS_CONTEXT('userenv', 'session_user')),'-', '_'), '''' , '_')||to_char(sysdate,'dd.mm.yyyyhh:mi') ;

EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER='''|| v_trace_file_name||'''';
-- execute immediate ;
execute immediate 'alter session set timed_statistics=true';
execute immediate 'alter session set max_dump_file_size=unlimited';
execute immediate 'alter session set STATISTICS_LEVEL = ALL' ;
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

Oracle patch information

opatch lsinventory -details


col COMMENTS format a25
col ACTION_TIME format a25
col NAMESPACE format a10
col VERSION format a12
col ACTION format a10
set linesize 200
select * from registry$history ;

oracle top 20 sql for the particular schema

SET LINESIZE 500  PAGESIZE 1000  FEEDBACK OFF  VERIFY OFF  SERVEROUTPUT ON
DECLARE

CURSOR c_sql IS
SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
-- a.address
a.sql_id
FROM gv$sqlarea a
where PARSING_SCHEMA_NAME='OS' ---<<<<<<<<<<<<<<
ORDER BY 2 DESC;

BEGIN

Dbms_Output.Enable(1000000);

Dbms_Output.Put_Line(Rpad('SQL Text',50,' ') ||
Lpad('Reads/Execution',16,' ') ||
Lpad('Buffer Gets',12,' ') ||
Lpad('Disk Reads',12,' ') ||
Lpad('Executions',12,' ') ||
Lpad('Sorts',12,' ') ||
-- Lpad('Address',10,' '));
Lpad('sql_id',10,' '));
Dbms_Output.Put_Line(Rpad('-',50,'-') || ' ' ||
Lpad('-',15,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',9,'-'));

<< top_sql >>
FOR cur_rec IN c_sql LOOP
Dbms_Output.Put_Line(Rpad(cur_rec.sql_text,50,' ') ||
Lpad(cur_rec.reads_per_execution,16,' ') ||
Lpad(cur_rec.buffer_gets,12,' ') ||
Lpad(cur_rec.disk_reads,12,' ') ||
Lpad(cur_rec.executions,12,' ') ||
lpad(cur_rec.sorts,12,' ') ||' '||
-- Lpad(cur_rec.address,10,' '));
Lpad(cur_rec.sql_id,12,' '));

-- IF c_sql%ROWCOUNT = 1 THEN
IF c_sql%ROWCOUNT = 20 THEN
EXIT top_sql;
END IF;
END LOOP;

END;
/




SQL Text                                           Reads/Execution Buffer Gets  Disk Reads  Executions       Sorts    sql_id
-------------------------------------------------- --------------- ----------- ----------- ----------- ----------- ---------
/* SQL Analyze(0) */ select /*+  full(t)    parall       398138061  1241785047  1194414183           3           0 c45yd6m5kjfc
/* SQL Analyze(0) */ select /*+  full(t)    parall       130386544   536003895   521546176           4           0 cn6j67uy5wtu




-- With address 

SET LINESIZE 500  PAGESIZE 1000  FEEDBACK OFF  VERIFY OFF  SERVEROUTPUT ON
DECLARE

CURSOR c_sql IS
SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address,
a.sql_id
FROM gv$sqlarea a
where PARSING_SCHEMA_NAME='OS' ---<<<<<<<<<<<<<<
ORDER BY 2 DESC;

BEGIN

Dbms_Output.Enable(1000000);

Dbms_Output.Put_Line(Rpad('SQL Text',50,' ') ||
Lpad('Reads/Execution',16,' ') ||
Lpad('Buffer Gets',12,' ') ||
Lpad('Disk Reads',12,' ') ||
Lpad('Executions',12,' ') ||
Lpad('Sorts',12,' ') ||
Lpad('Address',10,' ')||
Lpad('sql_id',10,' '));
Dbms_Output.Put_Line(Rpad('-',50,'-') || ' ' ||
Lpad('-',15,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',9,'-'));

<< top_sql >>
FOR cur_rec IN c_sql LOOP
Dbms_Output.Put_Line(Rpad(cur_rec.sql_text,50,' ') ||
Lpad(cur_rec.reads_per_execution,16,' ') ||
Lpad(cur_rec.buffer_gets,12,' ') ||
Lpad(cur_rec.disk_reads,12,' ') ||
Lpad(cur_rec.executions,12,' ') ||
lpad(cur_rec.sorts,12,' ') ||' '||
Lpad(cur_rec.address,10,' ')||' '||
Lpad(cur_rec.sql_id,12,' '));

-- IF c_sql%ROWCOUNT = 1 THEN
IF c_sql%ROWCOUNT = 20 THEN
EXIT top_sql;
END IF;
END LOOP;

END;
/

oracle database trigger for audit

create table audit_events(
event_date date,
event_user varchar2(30),
event_osuser varchar2(30),
event_machine varchar2(30),
event_terminal varchar2(30),
event_operation varchar2(30),
event_object_type varchar2(60),
event_object_name varchar2(60))
/


create or replace trigger object_change after
create or drop or alter on scott.schema
begin
declare
cursor c1 is
select sys_context('USERENV','OS_USER') osuser,
sys_context('USERENV','TERMINAL') terminal,
sys_context('USERENV','HOST') machine,
sys_context('USERENV','SESSION_USER') sess_user
from dual;
begin
for c2 in c1 loop
insert into system.audit_events
(event_date,event_user,event_osuser,event_machine,event_terminal,
event_operation,event_object_type,event_object_name)
values(sysdate,c2.sess_user,c2.osuser,c2.machine,c2.terminal,
ora_sysevent,ora_dict_obj_type,ora_dict_obj_name);
end loop;
end;
end;


01:46:28 SQL> select * from system.audit_events;

Oracle function " in " , " any ", " some " , " all "

select 1
from dual
where 1 in (12,23,1,34);


1
----------
1


select 1
from dual
where 1 =any (12,23,1,34)

1
----------
1

select 1
from dual
where 1 > any (12,23,1,34)

no rows selected


"some" and "any" are same

select 1
from dual
where 1 > some (12,23,1,34)

no rows selected



select 1
from dual
where 1 < all (12,23,1,34)

no rows selected


select 1
from dual
where 1 < all (12,23,2,34)

1
----------
1

Relational database system Codd's 12 rule

Dr. E.F. Codd, an IBM researcher, first developed the relational data model in 1970. In 1985, Dr. Codd published a list of 12 rules that concisely define an ideal relational database, which have provided a guideline for the design of all relational database systems ever since. Advertisement
On this topic


1. Information Representation
2. Guranteed Access
3. Systematic Treatment of Null Values
4. Database Description Rule
5. Comprehensive Data Sub-Language
6. View Updating
7. High-Level Update,Insert,Delete
8. Physical data independence
9. Logical Data Independence
10.The Distribution Rule
11. Non-Subversion
12. Integrity Rule




Codd's 12 rules,

Rule 1: The Information Rule
All data should be presented to the user in table form. Last week's newsletter already discussed the basics of this rule.

Rule 2: Guaranteed Access Rule
All data should be accessible without ambiguity. This can be accomplished through a combination of the table name, primary key, and column name.

Rule 3: Systematic Treatment of Null Values
A field should be allowed to remain empty. This involves the support of a null value, which is distinct from an empty string or a number with a value of zero. Of course, this can't apply to primary keys. In addition, most database implementations support the concept of a nun- null field constraint that prevents null values in a specific table column.

Rule 4: Dynamic On-Line Catalog Based on the Relational Model A relational database must provide access to its structure through the same tools that are used to access the data. This is usually accomplished by storing the structure definition within special system tables.

Rule 5: Comprehensive Data Sublanguage Rule
The database must support at least one clearly defined language that includes functionality for data definition, data manipulation, data integrity, and database transaction control. All commercial relational databases use forms of the standard SQL (Structured Query Language) as their supported comprehensive language.

Rule 6: View Updating Rule
Data can be presented to the user in different logical combinations, called views. Each view should support the same full range of data manipulation that direct-access to a table has available. In practice, providing update and delete access to logical views is difficult and is not fully supported by any current database.

Rule 7: High-level Insert, Update, and Delete
Data can be retrieved from a relational database in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.

Rule 8: Physical Data Independence
The user is isolated from the physical method of storing and retrieving information from the database. Changes can be made to the underlying architecture ( hardware, disk storage methods ) without affecting how the user accesses it.

Rule 9: Logical Data Independence
How a user views data should not change when the logical structure (tables structure) of the database changes. This rule is particularly difficult to satisfy. Most databases rely on strong ties between the user view of the data and the actual structure of the underlying tables.

Rule 10: Integrity Independence
The database language (like SQL) should support constraints on user input that maintain database integrity. This rule is not fully implemented by most major vendors. At a minimum, all databases do preserve two constraints through SQL.

No component of a primary key can have a null value. (see rule 3)
If a foreign key is defined in one table, any value in it must exist as a primary key in another table.
Rule 11: Distribution Independence
A user should be totally unaware of whether or not the database is distributed (whether parts of the database exist in multiple locations). A variety of reasons make this rule difficult to implement; I will spend time addressing these reasons when we discuss distributed databases.

Rule 12: Nonsubversion Rule
There should be no way to modify the database structure other than through the multiple row database language (like SQL). Most databases today support administrative tools that allow some direct manipulation of the datastructure.