Time Diff fn

create or replace FUNCTION DTDIFF (
DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS
NDATE_1 NUMBER;
NDATE_2 NUMBER;
NSECOND_1 NUMBER(5,0);
NSECOND_2 NUMBER(5,0);
BEGIN
-- Get Julian date number from first date (DATE_1)
NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));
-- Get Julian date number from second date (DATE_2)
NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));
-- Get seconds since midnight from first date (DATE_1)
NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));
-- Get seconds since midnight from second date (DATE_2)
NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));
RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END;

creating Repository using emca

http://arjudba.blogspot.com/2008/04/stack-of-problems-while-creating.html

Limit the Quota on the Tablespace of user2

While import to user2 limit the quota on user2 so that no objects created on users1
**************************************
alter user rbpc quota 0 on evtprjnew;
**************************************

default tablespace when imp. using the INDEXFILE

How to change the default tablespace when importing using the INDEXFILE option
Doc ID: Note:1068183.6 Type: PROBLEM
Last Revision Date: 18-JAN-2004 Status: PUBLISHED


Problem Description:
====================

You are importing a table and receive the following error:

ORA-01658: unable to create INITIAL extent for segment in tablespace %s
Cause: Failed to find sufficient contiguous space to allocate INITIAL
extent for segment being created.
Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
tablespace or retry with a smaller value for INITIAL

You realize that you do not have enough space in the tablespace for the table,
but do not want to add a datafile to that tablespace. So, you need to find out
how to change the default tablespace when importing.


Problem Explanation:
====================

By default an import will try to import tables into the same tablespace they
were exported from. There is not an equivalent FROM_TABLESPACE/TO_TABLESPACE
similar to FROMUSER/TOUSER, so you have to force the import utility into
placing the objects into another tablespace.


Problem References:
===================

Note 61949.1 Export and Import Bulletin
Note 1012307.6 Moving Tables Between Tablespaces Using Export/Import

Solution Description:
=====================

You need to use the INDEXFILE option, edit the file to change the default
tablespace, use script to precreate the table in the new tablespace, and then
do the import.


EXAMPLE TEST TO CHANGE DEFAULT TABLESPACE USING INDEXFILE OPTION:
================================================================

1) Create user REPORTS with default tablespace of TOOLS.

Oracle Server Manager Release 2.3.4.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production

SVRMGR> connect internal
Connected.
SVRMGR> CREATE USER reports IDENTIFIED BY reports;
Statement processed.
SVRMGR> GRANT DBA TO reports;
Statement processed.
SVRMGR> GRANT UNLIMITED TABLESPACE TO reports;
Statement processed.
SVRMGR> ALTER USER reports IDENTIFIED BY reports DEFAULT TABLESPACE tools;
Statement processed.
SVRMGR> ALTER USER reports QUOTA UNLIMITED ON tools;
Statement processed.
SVRMGR> CONNECT reports/reports;
Connected.
SVRMGR> CREATE TABLE test_table AS SELECT * FROM all_objects;
Statement processed.
SVRMGR> SELECT owner, table_name, tablespace_name FROM DBA_TABLES where owner =
'REPORTS';

OWNER TABLE_NAME TABLESPACE_NAME

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

REPORTS TEST_TABLE TOOLS

1 row selected.


2) Do export of TEST_TABLE.

[otchp1]/u02/home/usupport> exp reports/reports tables="(test_table)" file=
sadavid.dmp log=exp1.log

Export: Release 7.3.4.1.0 - Production on Wed Feb 24 19:39:24 1999

Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.


Connected to: Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production
Export done in US7ASCII character set

About to export specified tables via Conventional Path ...
. . exporting table TEST_TABLE 1891 rows exported
Export terminated successfully without warnings.


3) Create user SADAVID with default tablespace of USERS.

SVRMGR> CREATE USER sadavid IDENTIFIED BY sadavid;
Statement processed.
SVRMGR> GRANT DBA TO sadavid;
Statement processed.
SVRMGR> GRANT UNLIMITED TABLESPACE TO sadavid;
Statement processed.
SVRMGR> ALTER USER sadavid IDENTIFIED BY sadavid DEFAULT TABLESPACE users;
Statement processed.
SVRMGR> ALTER USER sadavid QUOTA UNLIMITED ON users;
Statement processed.


4) Do import as SADAVID. Note that the default tablespace does not change.

[otchp1]/u02/home/usupport> imp sadavid/sadavid file=sadavid.dmp tables=
"(test_table)"

Import: Release 7.3.4.1.0 - Production on Wed Feb 24 19:44:08 1999

Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.


Connected to: Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production

Export file created by EXPORT:V07.03.04 via conventional path

Warning: the objects were exported by REPORTS, not by you

. importing REPORTS's objects into SADAVID
. . importing table "TEST_TABLE" 1891 rows imported
Import terminated successfully without warnings.


5) NOTE: The table was created in TOOLS tablespace instead of USERS
tablespace.

SVRMGR> select owner, table_name, tablespace_name from dba_tables
2> where owner = 'SADAVID';

OWNER TABLE_NAMETABLESPACE_NAME

------------------------------ ------------------------------
SADAVID TEST_TABLE TOOLS

1 row selected.
SVRMGR> connect sadavid/sadavid
Connected.


6) Do import using INDEXFILE option.

[otchp1]/u02/home/usupport> imp sadavid/sadavid file=sadavid.dmp tables=
"(test_table)" INDEXFILE=indexfile.log

Import: Release 7.3.4.1.0 - Production on Wed Feb 24 20:04:39 1999

Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.


Connected to: Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production

Export file created by EXPORT:V07.03.04 via conventional path

Warning: the objects were exported by REPORTS, not by you

. . skipping table "TEST_TABLE"

Import terminated successfully without warnings.


7) Edit "indexfile.sql", log in as sadavid, and run script to precreate the
table.


CREATE TABLE "SADAVID"."TEST_TABLE" ("OWNER" VARCHAR2(30) NOT NULL,
"OBJECT_NAME" VARCHAR2(30) NOT NULL, "OBJECT_ID" NUMBER NOT NULL,
"OBJECT_TYPE" VARCHAR2(12), "CREATED" DATE NOT NULL, "LAST_DDL_TIME"
DATE NOT NULL, "TIMESTAMP" VARCHAR2(75), "STATUS" VARCHAR2(7))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 266240
NEXT 126976 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1
FREELIST GROUPS 1) TABLESPACE "USERS" ;

SVRMGR> connect sadavid/sadavid
Connected.
SVRMGR> drop table test_table;
Statement processed.
SVRMGR> @indexfile.sql
Statement processed.


8)Run import with IGNORE=Y to populate the new table:

[otchp1]/u02/home/usupport> imp sadavid/sadavid file=sadavid.dmp tables=
"(test_table)" ignore=y

Import: Release 7.3.4.1.0 - Production on Wed Feb 24 20:12:42 1999

Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.


Connected to: Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production

Export file created by EXPORT:V07.03.04 via conventional path

Warning: the objects were exported by REPORTS, not by you

. importing REPORTS's objects into SADAVID
. . importing table "TEST_TABLE" 1891 rows imported
Import terminated successfully without warnings.


9) NOTE TEST_TABLE HAS A NEW TABLESPACE OF USERS:

SVRMGR> select owner, table_name, tablespace_name from dba_tables where owner =
'SADAVID';

OWNER TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------
SADAVID TEST_TABLE USERS

1 row selected.


Solution Explanation:
=====================

By default, an import will try to import tables into the same tablespace they
were exported from. There is not an equivalent FROM_TABLESPACE/TO_TABLESPACE
similar to FROMUSER/TOUSER. So you have to force the import utility into
placing the objects into another tablespace.

FN . (Extract Numeric Value from a Column)

CREATE OR REPLACE FUNCTION GET_NUMBER
(NM VARCHAR2)
RETURN
VARCHAR2
IS
ST NUMBER(4) := 0;
EN NUMBER(4) := 0;
BEGIN
IF(nm IS NOT NULL) THEN

FOR i IN 1..LENGTH(nm) LOOP
IF ASCII(SUBSTR(nm,i,1)) >48 AND ASCII(SUBSTR(nm,i,1)) <57THEN
st:=i;
EXIT;
END IF;
END LOOP;

FOR i IN st..LENGTH(nm) LOOP
IF ASCII(SUBSTR(nm,i,1)) <48 THEN
EN:=i;
EXIT;
END IF;
END LOOP;
IF(st!=0) THEN
RETURN (SUBSTR(nm,st,EN-st));
ELSE
RETURN ' ';
END IF;
ELSE
RETURN ' ';
END IF;

END;
/

LOG ON - LOG OFF Trigger for User

*********************************************************************************
create table
stats$user_log
(
user_id varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(32),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(8)
)
;

*********************************************************************************

create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END;
/
*********************************************************************************
---------------Designing the logoff trigger--------------------------------------
*********************************************************************************
create or replace trigger
logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
update
stats$user_log
set
last_action = (select action from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
update
stats$user_log
set
last_program = (select program from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
update
stats$user_log
set
last_module = (select module from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
update
stats$user_log
set
logoff_day = sysdate
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
update
stats$user_log
set
logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
update
stats$user_log
set
elapsed_minutes =
round((logoff_day - logon_day)*1440)
where
sys_context('USERENV','SESSIONID') = session_id;
END;
/

*******Here is a script to track the activity of a specific user:*****************

CREATE OR REPLACE TRIGGER "LOGON_AUDIT_TRIGGER" AFTER
LOGON ON DATABASE
DECLARE
sess number(10);
prog varchar2(70);
BEGIN
IF sys_context('USERENV','BG_JOB_ID') is null and user = 'MYUSERNAME' THEN
sess := sys_context('USERENV','SESSIONID');
SELECT program INTO prog FROM v$session WHERE audsid = sess
and rownum<=1;
INSERT INTO stats$user_log VALUES (
user,sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
prog,
sysdate,
sys_context('USERENV','OS_USER'));
END IF;
END;
/

Split Procedure (Token) Fn

CREATE OR REPLACE FUNCTION get_new(
the_list VARCHAR2,
the_index NUMBER,
delim VARCHAR2
)
RETURN VARCHAR2
IS
start_pos NUMBER;
end_pos NUMBER;
BEGIN
IF the_index = 1 THEN
start_pos := 1;
ELSE
start_pos := INSTR(the_list,delim,1,the_index - 1);
IF start_pos = 0 THEN
RETURN NULL;
ELSE
start_pos := start_pos + LENGTH(delim);
END IF;
END IF;

end_pos := INSTR(the_list,delim,start_pos,1);

IF end_pos = 0 THEN
RETURN SUBSTR(the_list,start_pos);
ELSE
RETURN SUBSTR(the_list,start_pos,end_pos - start_pos);
END IF;

END get_new;
/
*************************************************************************************
*************************************************************************************
-- The pl/sql Oracle version for the Tokenizer procedure
Create or Replace
procedure tokenizer ( iStart IN NUMBER,
sPattern in VARCHAR2,
sBuffer in VARCHAR2,
sResult OUT VARCHAR2,
iNextPos OUT NUMBER)
AS
nPos1 number;
nPos2 number;
BEGIN
nPos1 := Instr (sBuffer ,sPattern ,iStart);
IF nPos1 = 0 then
sResult := NULL ;
ELSE
nPos2 := Instr (sBuffer ,sPattern ,nPos1 + 1);
IF nPos2 = 0 then
sResult := Rtrim(Ltrim(Substr(sBuffer ,nPos1+1)));
iNextPos := nPos2;
else
sResult := Substr(sBuffer ,nPos1 + 1 , nPos2 - nPos1 - 1);
iNextPos := nPos2;
END IF;
END IF;
END tokenizer ;
/

-- a Procedure to Test the Tokenizer
Create or Replace procedure sp_test_tokenizer
as
sepr varchar2(1);
sbuf varchar2(200);
sres varchar2(200);
pos number;
istart number;
begin

sbuf := '@0@11@222@3333@44444@555555@6666666@77777777@888888888';
sepr := '@';
istart := 1;
tokenizer (istart ,sepr,sbuf,sres,pos);
if (pos <> 0) then
dbms_output.put_line (sres);
end if;
while (pos <> 0)
loop
istart := pos;
tokenizer (istart ,sepr,sbuf,sres,pos );
dbms_output.put_line (sres);
end loop;
END sp_test_tokenizer;
/

-- Test the Procedure From sqlPlus
set serveroutput on
exec sp_test_tokenizer;