Thursday, February 14, 2013
CPU taking SQL
If you noticed that a session is using too much CPU, you can identify the actions performed by that session using top and Explain Plan.
So first, use TOP to identify the session using high CPU and take a note of the PID.
set linesize 140
set pagesize 100
col username format a15
col machine format a20
ACCEPT Process_ID prompt 'Pid : '
select s.inst_id,p.spid,s.sid,s.serial#,s.username,s.machine
from gv$session s, gv$process p
where s.paddr=p.addr
and p.spid=&proceso;
Once you got the SID associated to that PID, then you can use it with explain plan:
set lines 140
set pages 10000
set long 1000000
ACCEPT Process_SID prompt 'Sid : '
SELECT a.sql_id, a.sql_fulltext
FROM v$sqlarea a, v$session s
WHERE a.address = s.sql_address
AND s.sid = &proceso;
set lines 150
set pages 40000
col operation format a55
col object format a25
ACCEPT sentencia prompt 'Identificador de SQL ejecutado : '
select lpad(' ',2*depth)||operation||' '||options||decode(id, 0, substr(optimizer,1, 6)||'
Cost='||to_char(cost)) operation,
object_name object, cpu_cost, io_cost
from v$sql_plan where sql_id='&sentencia';
synonym - after dba privileges user is not able to access the option. only schema owner can access
create or replace trigger logon_trg
after logon on database
begin
if user in ('A','B') then
execute immediate 'alter session set current_schema=X';
end if;
end;
/
----------------------
create or replace trigger logon_trg
after logon on database
declare
usr varchar2(1000);
begin
select wm_concat(''''|| username || '''') into usr from dba_users;
if user in (usr) then
execute immediate 'alter session set current_schema=LDBO';
end if;
end;
/
SYS@NBS11G> select wm_concat(''''|| username || '''') from dba_users;
select wm_concat(''''|| username || '''') from dba_users
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 30
Who is using database link
Select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
s2.username,
substr(
decode(bitand(ksuseidl,11),
1,'ACTIVE',
0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
2,'SNIPED',
3,'SNIPED',
'KILLED'
),1,1
) "S",
substr(w.event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
And W.Sid=S.Indx
and s2.sid = W.Sid;
partition in a partitioned table truncated by mistake
Import partition data
A partition in a partitioned table was truncated by mistake and data needs to be imported from an export dump.
However instead of importing the data directly into the original table, the plan is to import the partition data into the temporary user and ratify the data before importing it to the original table using partition exchange.
Steps -
1. Create a temporary user.
2. Import partition data only into table in the temporary user
3. Ratify data.
4. Move segments in temporary table into new non-partitioned table.
5. Move this non-partitioned table into the original users tablespace.
6. Exchange partition between temporary and original partition and clean up.
NOTE -
ORIGINAL OWNER is PART_OWNER
TABLESPACE_NAME IS TEST_TBS
TABLE_NAME is TEST
PARTITION IS TEST_PART_DEC
1. Create temporary user with separate tablespace -
Temporary user called PART_RESTORE.
2. Import partition data only into table in the temporary user
imp userid/password file=test_export.dmp log=test_import.log fromuser=PART_OWNER touser=PART_RESTORE tables=TEST:TEST_PART_DEC feedback=10000 buffer = 64000 ignore=y &
3. RATIFY DATA IN PART_RESTORE SCHEMA.
4. Move segments fron temporary table into new non-partitioned table.
CREATE TABLE PART_RESTORE.TEST_TEMP
AS SELECT * FROM PART_RESTORE.TEST
WHERE ROWNUM<1;
5. Move this non-partitioned table into the original users tablespace.
ALTER TABLE PART_RESTORE.TEST_TEMP MOVE TABLESPACE TEST_TBS;
6. Exchange partition between temporary and original partition and clean up.
– set it as a nologging table
ALTER TABLE PART_RESTORE.TEST_TEMP NOLOGGING;
– move the data into the temp table.
INSERT /*+ APPEND */INTO PART_RESTORE.TEST_TEMP
SELECT *
FROM PART_RESTORE.TEST;
COMMIT;
— exchange the partition into the final PART_OWNER TABLE
ALTER TABLE PART_OWNER.TEST
EXCHANGE PARTITION TEST_PART_DEC
WITH TABLE PART_RESTORE.TEST_TEMP
UPDATE GLOBAL INDEXES;
– rebuild any unusable local indexes
ALTER TABLE PART_OWNER.TEST
MODIFY PARTITION TEST_PART_DEC
REBUILD UNUSABLE LOCAL INDEXES;
– gather stats on the new partition…
begin
DBMS_STATS.GATHER_TABLE_STATS (ownname => PART_OWNER, tabname => ‘TEST’, partname => ‘TEST_PART_DEC’, estimate_percent => 5, degree => 1, granularity => ‘ALL’, cascade => FALSE);
end;
/
– drop the ofsa_restored temp table…
DROP TABLE PART_RESTORE.TEST_TEMP ;
temporary tablespace usage
– Listing of temp segments.–
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
– Temp segment usage per session.–
SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
remove two char from string
select substr(oowncode,1,length(oowncode)-2) from ldfibs where oowncode='25TAV014M';
Top 30 I/O Intensive SQL Statements Identification
-- Top 30 I/O Intensive SQL Statements Identification : top30iosql.sql
set linesize 80
set pagesize 58
set heading on
set serveroutput on
spool c:\temp\top30iosql.txt
declare cursor curs1 is
select executions, disk_reads, buffer_gets, first_load_time, sql_text from v$sqlarea order by disk_reads / decode(executions,0,1,executions) desc;
stmnt_ctr number(3);
wrt1 number(3);
begin dbms_output.enable(50000); stmnt_ctr := 0;
for inrec in curs1 loop stmnt_ctr := stmnt_ctr + 1;
if stmnt_ctr >= 31 then
exit;
end if;
dbms_output.put_line('--------------------------------------' || '--------------------------------------');
dbms_output.put_line('SQL Stmnt Number: ' || to_char(stmnt_ctr)); dbms_output.put_line('--------------------------------------' || '--------------------------------------');
dbms_output.put_line('Executions : ' || to_char(inrec.executions));
dbms_output.put_line('Disk Reads : ' || to_char(inrec.disk_reads) || ' Buffer Gets : ' || to_char(inrec.buffer_gets));
dbms_output.put_line('First Load Time: ' || inrec.first_load_time);
dbms_output.put_line('SQL Statement-------->');
end loop;
end;
/
Who called me
http://asktom.oracle.com/tkyte/who_called_me
How Can I find out who called me or what my name is
Many times we are asked "in a procedure/function, can I find out who called me" or "can I dynamically figure out the name of the procedure or package that is currently executing".
You can find it in the call stack returned by dbms_utility.format_call_stack. I wrote a small routine called who_called_me that returns this sort of information (it doesn't tell you who you are, it lets you know who called you). If you wrap who_called_me with a function who_am_i, you'll get what you need. If you create the who_called_me/who_am_i routines, you'll be able to:
SQL> create or replace procedure demo
2 as
3 begin
4 dbms_output.put_line( who_am_i );
5 end;
6 /
Procedure created.
SQL> exec demo;
TKYTE.DEMO
In current releases of the database, this code has been incorporated into the OWA_UTIL package - you probably already have it in your database. If not, you can use this really old version that might need a tweak or two to work in your database release:
create or replace procedure who_called_me( owner out varchar2,
name out varchar2,
lineno out number,
caller_t out varchar2 )
as
call_stack varchar2(4096) default dbms_utility.format_call_stack;
n number;
found_stack BOOLEAN default FALSE;
line varchar2(255);
cnt number := 0;
begin
--
loop
n := instr( call_stack, chr(10) );
exit when ( cnt = 3 or n is NULL or n = 0 );
--
line := substr( call_stack, 1, n-1 );
call_stack := substr( call_stack, n+1 );
--
if ( NOT found_stack ) then
if ( line like '%handle%number%name%' ) then
found_stack := TRUE;
end if;
else
cnt := cnt + 1;
-- cnt = 1 is ME
-- cnt = 2 is MY Caller
-- cnt = 3 is Their Caller
if ( cnt = 3 ) then
lineno := to_number(substr( line, 13, 6 ));
line := substr( line, 21 );
if ( line like 'pr%' ) then
n := length( 'procedure ' );
elsif ( line like 'fun%' ) then
n := length( 'function ' );
elsif ( line like 'package body%' ) then
n := length( 'package body ' );
elsif ( line like 'pack%' ) then
n := length( 'package ' );
elsif ( line like 'anonymous%' ) then
n := length( 'anonymous block ' );
else
n := null;
end if;
if ( n is not null ) then
caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
else
caller_t := 'TRIGGER';
end if;
line := substr( line, nvl(n,1) );
n := instr( line, '.' );
owner := ltrim(rtrim(substr( line, 1, n-1 )));
name := ltrim(rtrim(substr( line, n+1 )));
end if;
end if;
end loop;
end;
/
create or replace function who_am_i return varchar2
is
l_owner varchar2(30);
l_name varchar2(30);
l_lineno number;
l_type varchar2(30);
begin
who_called_me( l_owner, l_name, l_lineno, l_type );
return l_owner || '.' || l_name;
end;
/
Find string into Database
create or replace procedure sp_ldsearchengine
(val varchar2)
is
v_old_table user_tab_columns.table_name%type;
v_where Varchar2(32766);
v_first_col boolean := true;
type rc is ref cursor;
c rc;
v_rowid varchar2(20);
begin
for r in (
select
t.*
from
user_tab_cols t, user_all_tables a
where t.table_name = a.table_name
and t.data_type like '%CHAR%'
order by t.table_name) loop
if v_old_table is null then
v_old_table := r.table_name;
end if;
if v_old_table <> r.table_name then
v_first_col := true;
-- dbms_output.put_line('searching ' || v_old_table);
open c for 'select rowid from "' || v_old_table || '" ' || v_where;
fetch c into v_rowid;
loop
exit when c%notfound;
dbms_output.put_line(' select * from ' || v_old_table || ' where rowid=''' || v_rowid ||''';');
fetch c into v_rowid;
end loop;
v_old_table := r.table_name;
end if;
if v_first_col then
v_where := ' where ' || r.column_name || ' like ''%' || val || '%''';
v_first_col := false;
else
v_where := v_where || ' or ' || r.column_name || ' like ''%' || val || '%''';
end if;
end loop;
end;
/
UTL_FILE_DIR Checking
I found some sample code provided by Oracle for testing utl_file_dir
Metalink Note 45327.1 gives some good procedures to test whether pl/sql code calling utl_file package is able to write to the directories defnied in utl_file_dir
Metalink Note 1016653.4 gives this code to test to verify setup for UTL_FILE Package
-----------Following will create a file and write into it and read from it and create another file and write into it--------------------
SET SERVEROUTPUT ON
DECLARE
fid UTL_FILE.FILE_TYPE;
v VARCHAR2(32767);
PROCEDURE recNgo (str IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('UTL_FILE error ' || str);
UTL_FILE.FCLOSE (fid);
END;
BEGIN
/* Change the directory name to one to which you at least
|| THINK you have read/write access.
*/
fid:= UTL_FILE.FOPEN('d:\ldoutput', 'utl_file_test','W');
UTL_FILE.put_line(fid,' Attempt to write to test the file...Kshitij');
UTL_FILE.FCLOSE(fid);
fid := UTL_FILE.FOPEN ('d:\ldoutput', 'utl_file_test', 'R');
UTL_FILE.GET_LINE (fid, v);
dbms_output.put_line (v);
UTL_FILE.FCLOSE (fid);
fid := UTL_FILE.FOPEN ('d:\ldoutput', 'utl_file_test_10', 'W');
UTL_FILE.PUT_LINE (fid, v);
UTL_FILE.FCLOSE (fid);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN recNgo ('invalid_path');
WHEN UTL_FILE.INVALID_MODE
THEN recNgo ('invalid_mode');
WHEN UTL_FILE.INVALID_FILEHANDLE
THEN recNgo ('invalid_filehandle');
WHEN UTL_FILE.INVALID_OPERATION
THEN recNgo ('invalid_operation');
WHEN UTL_FILE.READ_ERROR
THEN recNgo ('read_error');
WHEN UTL_FILE.WRITE_ERROR
THEN recNgo ('write_error');
WHEN UTL_FILE.INTERNAL_ERROR
THEN recNgo ('internal_error');
END;
/
Purge Queue
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'purge_boqueue',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE po_t dbms_aqadm.aq$_purge_options_t;
BEGIN dbms_aqadm.purge_queue_table(''LDBO.TBLDIGITALBOQUEUE'', NULL, po_t); END;',
start_date => '28-APR-13 04.20.00 AM ASIA/CALCUTTA',
repeat_interval => 'freq=DAILY',
end_date => NULL,
enabled => TRUE,
comments => 'purge queue table');
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'purge_mainboqueue',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE po_t dbms_aqadm.aq$_purge_options_t;
BEGIN dbms_aqadm.purge_queue_table(''LDBO.TBLMAINBOQUEUE'', NULL, po_t); END;',
start_date => '28-APR-13 04.30.00 AM ASIA/CALCUTTA',
repeat_interval => 'freq=DAILY',
end_date => NULL,
enabled => TRUE,
comments => 'purge queue table');
END;
/
Job Analyze Temp tables
CREATE OR REPLACE PROCEDURE Analyzetemp AS
BEGIN
FOR CUR_REC IN (SELECT DISTINCT OWNER,TABLE_NAME
FROM DBA_TABLES where table_name like '%TEMP%') LOOP
BEGIN
EXECUTE IMMEDIATE 'ANALYZE TABLE ' || CUR_REC.OWNER || '.' || CUR_REC.TABLE_NAME ||' COMPUTE STATISTICS' ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
FOR CUR_REC IN (SELECT DISTINCT OWNER,INDEX_NAME
FROM DBA_INDEXES where table_name like '%TEMP%') LOOP
BEGIN
EXECUTE IMMEDIATE 'ANALYZE INDEX ' || CUR_REC.OWNER || '.' || CUR_REC.INDEX_NAME ||' COMPUTE STATISTICS' ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
Analyze Full
CREATE OR REPLACE PROCEDURE AnalyzeFull AS
BEGIN
FOR CUR_REC IN (SELECT DISTINCT OWNER,TABLE_NAME
FROM DBA_TABLES) LOOP
BEGIN
EXECUTE IMMEDIATE 'ANALYZE TABLE ' || CUR_REC.OWNER || '.' || CUR_REC.TABLE_NAME ||' COMPUTE STATISTICS' ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
FOR CUR_REC IN (SELECT DISTINCT OWNER, INDEX_NAME
FROM DBA_INDEXES) LOOP
BEGIN
EXECUTE IMMEDIATE 'ANALYZE INDEX ' || CUR_REC.OWNER || '.' || CUR_REC.INDEX_NAME ||' COMPUTE STATISTICS' ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
User Creation With job
host mkdir c:\yep1314
spool c:\yep1314\yep1314.log
--------------User Creation
CREATE USER LDBO PROFILE "DEFAULT" IDENTIFIED BY ldbo DEFAULT TABLESPACE "USR" TEMPORARY TABLESPACE "TEMPORARY" ACCOUNT UNLOCK ;
BEGIN
DBMS_WM.GrantSystemPriv('ACCESS_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT ADMINISTER ANY SQL TUNING SET TO "LDBO" ;
GRANT ADMINISTER DATABASE TRIGGER TO "LDBO" ;
BEGIN
dbms_resource_manager_privs.grant_system_privilege(privilege_name=>'ADMINISTER_RESOURCE_MANAGER', grantee_name=>'LDBO', admin_option=>FALSE);
END;
/
GRANT ADMINISTER SQL TUNING SET TO "LDBO" ;
GRANT ADVISOR TO "LDBO" ;
GRANT ALTER ANY CLUSTER TO "LDBO" ;
GRANT ALTER ANY DIMENSION TO "LDBO" ;
GRANT ALTER ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT ALTER ANY INDEX TO "LDBO" ;
GRANT ALTER ANY INDEXTYPE TO "LDBO" ;
GRANT ALTER ANY LIBRARY TO "LDBO" ;
GRANT ALTER ANY MATERIALIZED VIEW TO "LDBO" ;
GRANT ALTER ANY OUTLINE TO "LDBO" ;
GRANT ALTER ANY PROCEDURE TO "LDBO" ;
GRANT ALTER ANY ROLE TO "LDBO" ;
GRANT ALTER ANY RULE TO "LDBO" ;
GRANT ALTER ANY RULE SET TO "LDBO" ;
GRANT ALTER ANY SEQUENCE TO "LDBO" ;
GRANT ALTER ANY SQL PROFILE TO "LDBO" ;
GRANT ALTER ANY TABLE TO "LDBO" ;
GRANT ALTER ANY TRIGGER TO "LDBO" ;
GRANT ALTER ANY TYPE TO "LDBO" ;
GRANT ALTER DATABASE TO "LDBO" ;
GRANT ALTER PROFILE TO "LDBO" ;
GRANT ALTER RESOURCE COST TO "LDBO" ;
GRANT ALTER ROLLBACK SEGMENT TO "LDBO" ;
GRANT ALTER SESSION TO "LDBO" ;
GRANT ALTER SYSTEM TO "LDBO" ;
GRANT ALTER TABLESPACE TO "LDBO" ;
GRANT ALTER USER TO "LDBO" ;
GRANT ANALYZE ANY TO "LDBO" ;
GRANT ANALYZE ANY DICTIONARY TO "LDBO" ;
GRANT AUDIT ANY TO "LDBO" ;
GRANT AUDIT SYSTEM TO "LDBO" ;
GRANT BACKUP ANY TABLE TO "LDBO" ;
GRANT BECOME USER TO "LDBO" ;
GRANT CHANGE NOTIFICATION TO "LDBO" ;
GRANT COMMENT ANY TABLE TO "LDBO" ;
GRANT CREATE ANY CLUSTER TO "LDBO" ;
GRANT CREATE ANY CONTEXT TO "LDBO" ;
GRANT CREATE ANY DIMENSION TO "LDBO" ;
GRANT CREATE ANY DIRECTORY TO "LDBO" ;
GRANT CREATE ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT CREATE ANY INDEX TO "LDBO" ;
GRANT CREATE ANY INDEXTYPE TO "LDBO" ;
GRANT CREATE ANY JOB TO "LDBO" ;
GRANT CREATE ANY LIBRARY TO "LDBO" ;
GRANT CREATE ANY MATERIALIZED VIEW TO "LDBO" ;
GRANT CREATE ANY OPERATOR TO "LDBO" ;
GRANT CREATE ANY OUTLINE TO "LDBO" ;
GRANT CREATE ANY PROCEDURE TO "LDBO" ;
GRANT CREATE ANY RULE TO "LDBO" ;
GRANT CREATE ANY RULE SET TO "LDBO" ;
GRANT CREATE ANY SEQUENCE TO "LDBO" ;
GRANT CREATE ANY SQL PROFILE TO "LDBO" ;
GRANT CREATE ANY SYNONYM TO "LDBO" ;
GRANT CREATE ANY TABLE TO "LDBO" ;
GRANT CREATE ANY TRIGGER TO "LDBO" ;
GRANT CREATE ANY TYPE TO "LDBO" ;
GRANT CREATE ANY VIEW TO "LDBO" ;
GRANT CREATE CLUSTER TO "LDBO" ;
GRANT CREATE DATABASE LINK TO "LDBO" ;
GRANT CREATE DIMENSION TO "LDBO" ;
GRANT CREATE EVALUATION CONTEXT TO "LDBO" ;
GRANT CREATE EXTERNAL JOB TO "LDBO" ;
GRANT CREATE INDEXTYPE TO "LDBO" ;
GRANT CREATE JOB TO "LDBO" ;
GRANT CREATE LIBRARY TO "LDBO" ;
GRANT CREATE MATERIALIZED VIEW TO "LDBO" ;
GRANT CREATE OPERATOR TO "LDBO" ;
GRANT CREATE PROCEDURE TO "LDBO" ;
GRANT CREATE PROFILE TO "LDBO" ;
GRANT CREATE PUBLIC DATABASE LINK TO "LDBO" ;
GRANT CREATE PUBLIC SYNONYM TO "LDBO" ;
GRANT CREATE ROLE TO "LDBO" ;
GRANT CREATE ROLLBACK SEGMENT TO "LDBO" ;
GRANT CREATE RULE TO "LDBO" ;
GRANT CREATE RULE SET TO "LDBO" ;
GRANT CREATE SEQUENCE TO "LDBO" ;
GRANT CREATE SESSION TO "LDBO" ;
GRANT CREATE SYNONYM TO "LDBO" ;
GRANT CREATE TABLE TO "LDBO" ;
GRANT CREATE TABLESPACE TO "LDBO" ;
GRANT CREATE TRIGGER TO "LDBO" ;
GRANT CREATE TYPE TO "LDBO" ;
GRANT CREATE USER TO "LDBO" ;
GRANT CREATE VIEW TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('CREATE_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT DEBUG ANY PROCEDURE TO "LDBO" ;
GRANT DEBUG CONNECT SESSION TO "LDBO" ;
GRANT DELETE ANY TABLE TO "LDBO" ;
BEGIN
dbms_aqadm.grant_system_privilege(privilege=>'DEQUEUE_ANY', grantee=>'LDBO', admin_option=>FALSE);
COMMIT;
END;
/
GRANT DROP ANY CLUSTER TO "LDBO" ;
GRANT DROP ANY CONTEXT TO "LDBO" ;
GRANT DROP ANY DIMENSION TO "LDBO" ;
GRANT DROP ANY DIRECTORY TO "LDBO" ;
GRANT DROP ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT DROP ANY INDEX TO "LDBO" ;
GRANT DROP ANY INDEXTYPE TO "LDBO" ;
GRANT DROP ANY LIBRARY TO "LDBO" ;
GRANT DROP ANY MATERIALIZED VIEW TO "LDBO" ;
GRANT DROP ANY OPERATOR TO "LDBO" ;
GRANT DROP ANY OUTLINE TO "LDBO" ;
GRANT DROP ANY PROCEDURE TO "LDBO" ;
GRANT DROP ANY ROLE TO "LDBO" ;
GRANT DROP ANY RULE TO "LDBO" ;
GRANT DROP ANY RULE SET TO "LDBO" ;
GRANT DROP ANY SEQUENCE TO "LDBO" ;
GRANT DROP ANY SQL PROFILE TO "LDBO" ;
GRANT DROP ANY SYNONYM TO "LDBO" ;
GRANT DROP ANY TABLE TO "LDBO" ;
GRANT DROP ANY TRIGGER TO "LDBO" ;
GRANT DROP ANY TYPE TO "LDBO" ;
GRANT DROP ANY VIEW TO "LDBO" ;
GRANT DROP PROFILE TO "LDBO" ;
GRANT DROP PUBLIC DATABASE LINK TO "LDBO" ;
GRANT DROP PUBLIC SYNONYM TO "LDBO" ;
GRANT DROP ROLLBACK SEGMENT TO "LDBO" ;
GRANT DROP TABLESPACE TO "LDBO" ;
GRANT DROP USER TO "LDBO" ;
BEGIN
dbms_aqadm.grant_system_privilege(privilege=>'ENQUEUE_ANY', grantee=>'LDBO', admin_option=>FALSE);
COMMIT;
END;
/
GRANT EXECUTE ANY CLASS TO "LDBO" ;
GRANT EXECUTE ANY EVALUATION CONTEXT TO "LDBO" ;
GRANT EXECUTE ANY INDEXTYPE TO "LDBO" ;
GRANT EXECUTE ANY LIBRARY TO "LDBO" ;
GRANT EXECUTE ANY OPERATOR TO "LDBO" ;
GRANT EXECUTE ANY PROCEDURE TO "LDBO" ;
GRANT EXECUTE ANY PROGRAM TO "LDBO" ;
GRANT EXECUTE ANY RULE TO "LDBO" ;
GRANT EXECUTE ANY RULE SET TO "LDBO" ;
GRANT EXECUTE ANY TYPE TO "LDBO" ;
GRANT EXPORT FULL DATABASE TO "LDBO" ;
GRANT FLASHBACK ANY TABLE TO "LDBO" ;
GRANT FORCE ANY TRANSACTION TO "LDBO" ;
GRANT FORCE TRANSACTION TO "LDBO" ;
GRANT GRANT ANY OBJECT PRIVILEGE TO "LDBO" ;
GRANT GRANT ANY PRIVILEGE TO "LDBO" ;
GRANT GRANT ANY ROLE TO "LDBO" ;
GRANT IMPORT FULL DATABASE TO "LDBO" ;
GRANT INSERT ANY TABLE TO "LDBO" ;
GRANT LOCK ANY TABLE TO "LDBO" ;
GRANT EXECUTE ON DBMS_LOCK TO "LDBO" ;
GRANT MANAGE ANY FILE GROUP TO "LDBO" ;
BEGIN
dbms_aqadm.grant_system_privilege(privilege=>'MANAGE_ANY', grantee=>'LDBO', admin_option=>FALSE);
COMMIT;
END;
/
GRANT MANAGE FILE GROUP TO "LDBO" ;
GRANT MANAGE SCHEDULER TO "LDBO" ;
GRANT MANAGE TABLESPACE TO "LDBO" ;
GRANT MERGE ANY VIEW TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('MERGE_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT ON COMMIT REFRESH TO "LDBO" ;
GRANT QUERY REWRITE TO "LDBO" ;
GRANT READ ANY FILE GROUP TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('REMOVE_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT RESTRICTED SESSION TO "LDBO" ;
GRANT RESUMABLE TO "LDBO" ;
BEGIN
DBMS_WM.GrantSystemPriv('ROLLBACK_ANY_WORKSPACE', 'LDBO', 'NO');
END;
/
GRANT SELECT ANY DICTIONARY TO "LDBO" ;
GRANT SELECT ANY SEQUENCE TO "LDBO" ;
GRANT SELECT ANY TABLE TO "LDBO" ;
GRANT SELECT ANY TRANSACTION TO "LDBO" ;
GRANT SYSDBA TO "LDBO" ;
GRANT SYSOPER TO "LDBO" ;
GRANT UNDER ANY TABLE TO "LDBO" ;
GRANT UNDER ANY TYPE TO "LDBO" ;
GRANT UNDER ANY VIEW TO "LDBO" ;
GRANT UNLIMITED TABLESPACE TO "LDBO" ;
GRANT UPDATE ANY TABLE TO "LDBO" ;
GRANT "AQ_ADMINISTRATOR_ROLE" TO "LDBO" ;
GRANT "AQ_USER_ROLE" TO "LDBO" ;
GRANT EXECUTE ON DBMS_AQ TO "LDBO";
GRANT EXECUTE ON DBMS_AQADM TO "LDBO";
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO "LDBO";
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO "LDBO";
BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(privilege => 'ENQUEUE_ANY',grantee => 'LDBO',admin_option => FALSE);
END;
/
BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(privilege => 'DEQUEUE_ANY',grantee => 'LDBO',admin_option => FALSE);
END;
/
GRANT "CONNECT" TO "LDBO" ;
GRANT "DBA" TO "LDBO" ;
GRANT "EXP_FULL_DATABASE" TO "LDBO" ;
GRANT "IMP_FULL_DATABASE" TO "LDBO" ;
ALTER USER LDBO QUOTA UNLIMITED on USR;
ALTER USER LDBO QUOTA UNLIMITED on INDX;
GRANT execute ON utl_recomp TO "LDBO";
alter system set job_queue_processes=1000;
alter system set sec_case_sensitive_logon=False;
alter system set open_cursors=1000 scope=spfile;
alter system set session_cached_cursors=500 scope=spfile;
CREATE USER CMUSER PROFILE "DEFAULT" IDENTIFIED BY cmuser DEFAULT TABLESPACE "USR" TEMPORARY TABLESPACE "TEMPORARY" ACCOUNT UNLOCK ;
GRANT CREATE SESSION TO cmuser;
GRANT CONNECT TO cmuser;
create or replace directory LDDIGITAL as 'd:\ldoutput\lddigital';
grant all on directory LDDIGITAL to public;
-----------------Auditing
Audit user,system grant,role,alter system,profile whenever successful ;
audit create session by access;
audit audit system by access;
audit grant any privilege by access;
audit grant any object privilege by access;
audit grant any role by access;
audit system grant by access;
audit create user by access;
audit create any table by access;
audit create public database link by access;
audit create any procedure by access;
audit alter user by access;
audit alter any table by access;
audit alter any procedure by access;
audit alter database by access;
audit alter system by access;
audit alter profile by access;
audit drop user by access;
audit drop any procedure by access;
audit drop any table by access;
audit drop profile by access;
audit drop any index by access;
-----------------Profile Setting
ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;
-----------------Network Access
Exec dbms_network_acl_admin.create_acl ('utl_http_access.xml','Normal Access','LDBO',TRUE,'connect',NULL,NULL);
Exec dbms_network_acl_admin.add_privilege (acl => 'utl_http_access.xml', principal => 'LDBO',is_grant => TRUE, privilege => 'resolve');
Exec dbms_network_acl_admin.assign_acl ('utl_http_access.xml', '*',NULL,NULL);
commit ;
Exec dbms_network_acl_admin.create_acl ('utl_inaddr_access.xml','Normal Access','LDBO',TRUE,'resolve',NULL, NULL);
Exec dbms_network_acl_admin.add_privilege (acl => 'utl_inaddr_access.xml', principal => 'LDBO',is_grant => TRUE, privilege => 'resolve');
Exec dbms_network_acl_admin.assign_acl ('utl_inaddr_access.xml', '*',NULL,NULL);
commit;
Exec dbms_network_acl_admin.create_acl ('utl_mail.xml','Allow mail to be send','LDBO',TRUE,'connect' );
Exec dbms_network_acl_admin.add_privilege ('utl_mail.xml','LDBO',TRUE,'resolve');
Exec dbms_network_acl_admin.assign_acl('utl_mail.xml','*',NULL,NULL);
commit ;
Exec dbms_network_acl_admin.create_acl ('utl_http.xml','HTTP Access','LDBO',TRUE,'connect',null,null);
Exec dbms_network_acl_admin.add_privilege ('utl_http.xml','LDBO',TRUE,'resolve',null,null);
Exec dbms_network_acl_admin.assign_acl ('utl_http.xml','*',NULL,NULL);
commit;
Grant Execute on utl_inaddr to ldbo ;
Grant Execute on utl_http to ldbo ;
--------------Jobs
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'compile',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN UTL_RECOMP.recomp_serial; END;',
start_date => '01-APR-12 06:31.00.00 AM ASIA/CALCUTTA',
repeat_interval => 'freq=DAILY',
end_date => NULL,
enabled => TRUE,
comments => 'JOB to compile invalid objects');
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'analyze',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''LDBO'',CASCADE=>TRUE); END;',
start_date => '01-APR-12 04.00.00 AM ASIA/CALCUTTA',
repeat_interval=> 'FREQ=DAILY',
enabled => TRUE,
comments => 'JOB to gather LDBO statistics every DAY');
END;
/
CREATE OR REPLACE PROCEDURE Analyzetemp wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
2a2 168
oUpO82c0m3kRqNYu+ieu9+lzKuMwg+1pLdwdf3QBvGQwDJTIaaoOXtFSqGZSURSwYVZJQEip
BFRn/ggU7vLVLWjhHH7ZwpoSP6gj6SPjNjqS0uQIQs8kvDQ16OKw2mqkYSLmslJfGnAEsfRW
JnR5Cd9xq50LGMSTM6dgp0p75Bh50uKOVdktzWyKuSYvQdBw1x012GW+S9N4SxkHOSFtETUS
CYQbBMFa/ZN7qJODaoZfNvtZ66GKRb0KHS1vnE6ZPN7TaRkCGOzCR4FJjTbfVXhPUcjwJAgf
s3RPbdJG8TrBXhzYkK5UseiJMxXJJhBAKs0ftLEz+d2dQJkt27wDd6pLZMvivKZk5hqg
/
show errors
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'ANALYZE_TEMP',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN Analyzetemp; END;',
start_date => '01-APR-12 08.01.00 AM ASIA/CALCUTTA',
repeat_interval=> 'FREQ=DAILY',
end_date => NULL,
enabled => TRUE,
comments => 'Analyze TEMP tables indexes');
END;
/
CREATE OR REPLACE PROCEDURE AnalyzeFull wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
265 154
+WnW35aMpBSqlXDoysgRZr/L8jowg+1pLdwdf3QBvGQwumfmczfcvtuA72wzPvomAkPb3Z/g
Hml30Em9YungrX+PqGGHfp3BeGB/kbBaULtk2SmGeVfRmfX8IyPAaDjI54KVrfE3C2hJm5Fl
2AwxUj6Rco4sA7kA19gdMs6UhIlp8c2BaJIlHTSFtHuWVmJ9Z8mXNh/hk7AMYyH2teTmEcdp
3TrPXAPeR5Rp/YG2nd4+Taz3fDw7Ph5x6RMaS828znTwMfqNqp3vQF2klUabm/4+ekKqiDi6
bfGDvQUN+YmWDAeUOQ28nVd3OWs8BPXrmyg5XI+5tpcsjUQ=
/
show errors
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'ANALYZE_FULL',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN analyzefull; END;',
start_date => '01-APR-12 01:00.00.00 PM ASIA/CALCUTTA',
repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=01; BYMINUTE=01;',
end_date => NULL,
enabled => TRUE,
comments => 'Analyze all tables indexes');
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'purge_rkqueue',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE po_t dbms_aqadm.aq$_purge_options_t;
BEGIN dbms_aqadm.purge_queue_table(''LDBO.TBLRKQUEUE'', NULL, po_t); END;',
start_date => '28-APR-13 04.10.00 AM ASIA/CALCUTTA',
repeat_interval => 'freq=DAILY',
end_date => NULL,
enabled => TRUE,
comments => 'purge queue table');
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'purge_boqueue',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE po_t dbms_aqadm.aq$_purge_options_t;
BEGIN dbms_aqadm.purge_queue_table(''LDBO.TBLDIGITALBOQUEUE'', NULL, po_t); END;',
start_date => '28-APR-13 04.20.00 AM ASIA/CALCUTTA',
repeat_interval => 'freq=DAILY',
end_date => NULL,
enabled => TRUE,
comments => 'purge queue table');
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'purge_mainboqueue',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE po_t dbms_aqadm.aq$_purge_options_t;
BEGIN dbms_aqadm.purge_queue_table(''LDBO.TBLMAINBOQUEUE'', NULL, po_t); END;',
start_date => '28-APR-13 04.30.00 AM ASIA/CALCUTTA',
repeat_interval => 'freq=DAILY',
end_date => NULL,
enabled => TRUE,
comments => 'purge queue table');
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'analyze_rkqueue',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN dbms_stats.gather_table_stats(''LDBO'',''TBLRKQUEUE'' ,force=>TRUE); END;',
start_date => '28-APR-13 07.00.00 AM ASIA/CALCUTTA',
repeat_interval => 'freq=DAILY',
end_date => NULL,
enabled => TRUE,
comments => 'JOB to gather Queue Table statistics');
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'analyze_boqueue',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN dbms_stats.gather_table_stats(''LDBO'',''TBLDIGITALBOQUEUE'' ,force=>TRUE); END;',
start_date => '28-APR-13 07.11.00 AM ASIA/CALCUTTA',
repeat_interval => 'freq=DAILY',
end_date => NULL,
enabled => TRUE,
comments => 'JOB to gather Queue Table statistics');
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'analyze_mainboqueue',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN dbms_stats.gather_table_stats(''LDBO'',''TBLMAINBOQUEUE'' ,force=>TRUE); END;',
start_date => '28-APR-13 07.20.00 AM ASIA/CALCUTTA',
repeat_interval => 'freq=DAILY',
end_date => NULL,
enabled => TRUE,
comments => 'JOB to gather Queue Table statistics');
END;
/
---------------Backup job
host mkdir d:\expdp1314
create directory export_auto as 'd:\expdp1314';
CREATE USER dba_export_user PROFILE "DEFAULT" IDENTIFIED BY dba_export_user DEFAULT TABLESPACE "USR" TEMPORARY TABLESPACE "TEMPORARY" ACCOUNT UNLOCK ;
grant connect, create database link, resource, create view to dba_export_user;
grant unlimited tablespace to dba_export_user;
grant exp_full_database to dba_export_user;
grant read,write on directory export_auto to dba_export_user;
grant execute on dbms_flashback to dba_export_user;
grant create table to dba_export_user;
grant FLASHBACK ANY TABLE to dba_export_user;
ALTER USER dba_export_user QUOTA UNLIMITED on USR;
ALTER USER dba_export_user QUOTA UNLIMITED on INDX;
CREATE OR REPLACE PROCEDURE dba_export_user.start_export
IS
hdl_job NUMBER;
l_cur_scn NUMBER;
l_job_state VARCHAR2 (20);
l_status SYS.ku$_status1010;
l_job_status SYS.ku$_jobstatus1010;
BEGIN
begin
execute immediate 'drop table dba_export_user.AUTO_EXPORT';
exception when others then null;
end;
hdl_job := DBMS_DATAPUMP.OPEN ( operation => 'EXPORT', job_mode => 'FULL', job_name => 'AUTO_EXPORT' );
DBMS_DATAPUMP.add_file (handle => hdl_job,filename => 'EXPDP1314.dmp',directory => 'EXPORT_AUTO',filetype => DBMS_DATAPUMP.ku$_file_type_dump_file,reusefile => 1);
DBMS_DATAPUMP.add_file (handle => hdl_job,filename => 'EXPDP1314.log',DIRECTORY => 'EXPORT_AUTO',filetype => DBMS_DATAPUMP.ku$_file_type_log_file,reusefile => 1);
DBMS_DATAPUMP.start_job (handle => hdl_job);
DBMS_DATAPUMP.wait_for_job (handle => hdl_job, job_state => l_job_state);
DBMS_OUTPUT.put_line ('Job exited with status:' || l_job_state);
DBMS_DATAPUMP.detach(handle => hdl_job);
END;
/
show errors
begin
dbms_scheduler.create_job(
job_name => 'EXPORT_JOB'
,job_type => 'STORED_PROCEDURE'
,job_action => 'dba_export_user.start_export'
,start_date => '01-MAR-13 10.00.00.00 PM ASIA/CALCUTTA'
,repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN;'
,enabled => TRUE
,comments => 'EXPORT_DATABASE_JOB');
end;
/
spool off
job rebuild index 11G
create or replace procedure sp_rebuildindex as
vOwner dba_indexes.owner%TYPE; /* Index Owner */
vIdxName dba_indexes.index_name%TYPE; /* Index Name */
vTbName dba_indexes.table_name%TYPE; /* Table Name */
vAnalyze VARCHAR2(100); /* String of Analyze Stmt */
vCursor NUMBER; /* DBMS_SQL cursor */
vNumRows INTEGER; /* DBMS_SQL return rows */
vHeight index_stats.height%TYPE; /* Height of index tree */
vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */
vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */
vDLfPerc NUMBER; /* Del lf Percentage */
vMaxHeight NUMBER; /* Max tree height */
vMaxDel NUMBER; /* Max del lf percentage */
CURSOR cGetIdx IS SELECT owner,index_name,table_name
FROM dba_indexes WHERE OWNER NOT in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB','SYSMAN');
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3;
vMaxDel := 20;
/* For every index, validate structure */
OPEN cGetIdx;
LOOP
FETCH cGetIdx INTO vOwner,vIdxName,VTbName;
EXIT WHEN cGetIdx%NOTFOUND;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding? */
/* If so, then generate command */
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
FROM INDEX_STATS;
IF vDLfRows = 0 THEN /* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
EXECUTE IMMEDIATE 'ALTER INDEX ' || vIdxName || ' REBUILD ONLINE';
EXECUTE IMMEDIATE 'ANALYZE TABLE ' || vTbName || ' compute statistics';
EXECUTE IMMEDIATE 'ANALYZE INDEX ' || vIdxName || ' compute statistics';
END IF;
END LOOP;
CLOSE cGetIdx;
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'REBUILDINDEX',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN sp_rebuildindex; END;',
start_date => '01-APR-12 09:00.00.00 AM ASIA/CALCUTTA',
repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=09; BYMINUTE=01;',
end_date => NULL,
enabled => TRUE,
comments => 'rebuild index');
END;
/
EXEC DBMS_SCHEDULER.RUN_JOB('REBUILDINDEX');
Temporary Tablespace Cleanup 11g
alter tablespace temporary shrink space keep 1G;
exec dbms_scheduler.drop_job('tempshrink');
begin
dbms_scheduler.create_job
(job_name => 'tempshrink',
job_type => 'PLSQL_BLOCK',
job_action => 'begin execute immediate ''alter tablespace temporary shrink space keep 1G''; end;',
start_date => '01-APR-12 01:00.00.00 AM ASIA/CALCUTTA',
repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN;',
comments=>'Shrink Temporary Tablespace');
end;
/
exec dbms_scheduler.run_job('tempshrink');
========================
select distinct t1.tablespace_name TB,t2.file_name TempFile_name,t1.tablespace_size/1024/1024 Used_Allocated_size,t1.allocated_space/1024/1024 Allocated_size,t1.free_space/1024/1024 Free_space,t2.Autoextensible,t2.bytes/1024/1024 Used_space, t2.maxbytes/1024/1024 Max_TB_size from dba_temp_free_space t1,dba_temp_files t2 where t1.tablespace_name=t2.tablespace_name ;
select tablespace_size/1024/1024,allocated_space/1024/1024,free_space/1024/1024 from dba_temp_free_space;
select tablespace_name,Autoextensible,bytes/1024/1024,maxbytes/1024/1024 from dba_temp_files;
=================================
create or replace trigger tg_clear_tempTB
after startup
on database
declare
j integer;
pragma autonomous_transaction;
begin
dbms_job.submit (j, 'begin execute immediate ''alter tablespace temporary shrink space keep 5G''; end;');
commit;
end tg_clear_logindetails;
/
job OS audit files maintaince
For Oracle on unix .aud files are created whether you have auditing enabled or not. They record sys operations. If you've got a lot of shell scripts that connect / as sysdba you are going to get a lot of .aud files. (In windows they are written to the event viewer)
They will go to where you set audit_file_dest (aka adump) to be. If you don't set adump the first default value is ORACLE_BASE/admin/ORACLE_SID/adump. If this doesn't exist then they will go in ORACLE_HOME/rdbms/audit
If you didn't know about this they tend to raise their heads when $ORACLE_HOME fills up and you wonder why.
Please be clear, I am talking about the OS .aud files. This process will not touch $aud in your database.
Oracle at 11.2 provide a way to manage these .aud OS files using the audit management package DBMS_AUDIT_MGMT
Here's how:
Initialize DBMS_AUDIT_MGMT
Call just once the initialization procedure INIT_CLEANUP to set up the audit management infrastructure.
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
DEFAULT_CLEANUP_INTERVAL => 24 );
END;
/
Create the Procedure to delete files (over a year old) for a single instance
CREATE OR REPLACE procedure SYS.delete_OSaud_files
is
ThisProc VARCHAR2(30) := 'delete_OSaud_files';
ThisAppErr NUMBER := -20000;
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
last_archive_time => SYSTIMESTAMP-366);
dbms_audit_mgmt.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
use_last_arch_timestamp => TRUE
);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(ThisProc||' - '||SQLERRM);
ROLLBACK;
RAISE_APPLICATION_ERROR(ThisAppErr, ThisProc);
END;
/
Create the Procedure to delete files (over a year old) for RAC
CREATE OR REPLACE procedure SYS.delete_OSaud_files
is
ThisProc VARCHAR2(30) := 'delete_OSaud_files';
ThisAppErr NUMBER := -20000;
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
last_archive_time => SYSTIMESTAMP-366,
rac_instance_number => 1);
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
last_archive_time => SYSTIMESTAMP-366,
rac_instance_number => 2);
dbms_audit_mgmt.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
use_last_arch_timestamp => TRUE);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(ThisProc||' - '||SQLERRM);
ROLLBACK;
RAISE_APPLICATION_ERROR(ThisAppErr, ThisProc);
END;
/
If you don't need to keep a years worth, just change SYSTIMESTAMP-366
Feel free to moan at me about 'when others then' and post a better procedure in the comments - I'll readily admit my plsql is not what it should be and I'm happy to be corrected.
Create a Schedule
(I like this type of thing to run when I'm actually working so I don't get called out of hours if something goes wrong)
Begin
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'DELETE_OSAUD_FILES_SCHED',
repeat_interval =>'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=10;',
comments => 'Delete adump files');
END;
/
Schedule the Job
BEGIN
dbms_scheduler.create_job (
job_name =>'DELETE_OSAUD_FILES_JOB',
job_type =>'STORED_PROCEDURE',
job_action => 'SYS.delete_OSaud_files',
enabled => TRUE,
auto_drop => false,
schedule_name => 'DELETE_OSAUD_FILES_SCHED',
comments => 'Remove aud files from adump');
END;
/
Set up mail notifications
(I like to know when my jobs error)
BEGIN
DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'smtp.mycompany.com:25');
DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'do_not_reply@mydatabase');
END;
/
Create the events that I want mailing about
to test notifications work first set up events for everything
BEGIN
DBMS_SCHEDULER.add_job_email_notification (
job_name => 'DELETE_OSAUD_FILES_JOB',
recipients => 'me@mycompany.com',
events => 'job_all_events');
END;
/
run your job
BEGIN
dbms_scheduler.run_job (
job_name =>'DELETE_OSAUD_FILES_JOB');
END;
/
You should get an email saying it ran.
But you don't want an 'I ran' mail every day, I only want to know if it's failed so:
BEGIN
DBMS_SCHEDULER.remove_job_email_notification (
job_name => 'DELETE_OSAUD_DATA_JOB',
recipients => 'me@mycompany.com',
events => 'job_succeeded');
END;
/
BEGIN
DBMS_SCHEDULER.remove_job_email_notification (
job_name => 'DELETE_OSAUD_DATA_JOB',
recipients => 'me@mycompany.com',
events => 'job_started');
END;
/
BEGIN
DBMS_SCHEDULER.remove_job_email_notification (
job_name => 'DELETE_OSAUD_DATA_JOB',
recipients => 'me@mycompany.com',
events => 'job_completed');
END;
/
Which is the equivalent of :
BEGIN
DBMS_SCHEDULER.add_job_email_notification (
job_name => 'DELETE_OSAUD_DATA_JOB',
recipients => 'me@mycompany.com',
events => 'job_broken,job_chain_stalled,job_completed,job_disabled,job_failed,
job_over_max_dur,job_sch_lim_reached,job_stopped');
END;
/
So now your .aud files will be kept in check for you.
Create Database Structure / MetaData / scripts using EXPDP
Dont Use PLSQL Developer, it will corrupt code bodies
1) Take Export Full Dump of apx1213 DB using Data Pump
conn sys@apx1213srv as sysdba
CREATE DIRECTORY DPUMP_DIR1 AS 'D:\expdp1213';
GRANT read, write ON DIRECTORY DPUMP_DIR1 TO ldbo;
cmd
expdp ldbo/ldbo@apx1213srv FULL=y directory=dpump_dir1 dumpfile=apx1213.DMP LOGFILE=apx1213.LOG EXCLUDE=STATISTICS
2) Install Oracle 11g
3) Create apx1314 Database (Note: Don't Change oracle default parameters)
4) Create LDBO user using attached script (Note:only used attached, Don't use old createuserldbo.sql)
5) Login into LDBO user and create synonym
create synonym script from last year database apx1213 and run into current year apx1314
conn sys@apx1213srv as sysdba
set pagesize 1000
set linesize 150
set long 1000
col metadata for a145
SPOOL C:\YEP1314\SYNONYMNS1213.SQL
select dbms_metadata.get_ddl('SYNONYM',synonym_name, 'PUBLIC') || '/' metadata
from dba_synonyms
where owner = 'PUBLIC'
and table_owner = 'LDBO'
order by synonym_name;
SPOOL OFF
conn sys@apx1314srv as sysdba
@C:\YEP1314\SYNONYMNS1213.SQL
6) Login into LDBO user and create Database Link
Note: Please change the password and connect string as per client software
CREATE PUBLIC DATABASE LINK "LNK_RAKSHAK"
CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1314SRV';
CREATE PUBLIC DATABASE LINK "LNK_CCM"
CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1314SRV';
CREATE DATABASE LINK "LNK_DIGITAL"
CONNECT TO "LDBO" IDENTIFIED BY ldbo USING 'APX1314SRV';
CREATE DATABASE LINK "LNK_PREVIOUSYEARBALANCE"
CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1213SRV';
CREATE PUBLIC DATABASE LINK "CMLDLINK"
CONNECT TO cmuser IDENTIFIED BY cmuser USING 'CMMASTERSRV';
7) Login into LDBO user and create Sequence
Already exist in Createuserldbo sql
please drop all sequences and recreate them
select 'DROP SEQUENCE ' || Sequence_Name || ';' from dba_Sequences where Sequence_Owner='LDBO';
Create Sequence LDBO.Dematuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Saudauniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Billuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Jobbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Delbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Turnbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Fwdbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Extbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Dpuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Bankuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Payrequniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Riskuniquenumber minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Portuniquenumber minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Axiscms minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Pmsuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Debarreduniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Bkrecouniquenumber minvalue 50000000 maxvalue 9999999999 start with 50000000 increment by 1 nocache;
Create Sequence LDBO.Sq_Smsuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Offlinenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Phytodemnumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Ipouniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Trademodification minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_SpicePoa minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Idbicms minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Scripuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Clientuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Brokeruniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Nsdlcdsldpuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Orarupee minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Rtgssequence minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Bobbentopool minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Clientcode minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Familycode minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_NigPortclose Minvalue 1 maxvalue 9999999999 Start With 1 Increment by 1 noCache;
Create Sequence LDBO.Seqclientsequence minvalue 1 maxvalue 99 start with 1 increment by 1;
Create Sequence LDBO.Sq_collateral minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Nsdlkra minvalue 1 maxvalue 99999999 ;
Create Sequence LDBO.LdOraclebatchid minvalue 1 maxvalue 9999999999 nocache;
Create Sequence LDBO.Sq_Limittokenno minvalue 1 maxvalue 99999999 ;
Create Sequence LDBO.SQ_CDSLXMLUNIQUENUMBER minvalue 10000 maxvalue 99999;
8) Create Data pump Directory expdp1314 and copy apx1213 dump (last year dump file) and Create optable
Note: Run from Database server only
conn sys@apx1314srv as sysdba
host mkdir D:\expdp1314
CREATE DIRECTORY DPUMP_DIR1 AS 'D:\expdp1314';
GRANT read, write ON DIRECTORY DPUMP_DIR1 TO ldbo;
cmd
impdp ldbo/ldbo@apx1314SRV directory=DPUMP_DIR1 dumpfile=apx1213.DMP LOGFILE=apx1314metadata.LOG schemas=LDBO content=metadata_only EXCLUDE=TABLESPACE,DB_LINK,SEQUENCE,STATISTICS,USER,GRANT sqlfile=optable.sql
9) Create splogin,tglogin manually
Already exist in Createuserldbo sql
SELECT owner,object_name FROM Dba_Objects WHERE OBJECT_NAME IN ('SPLOGININFORMATION','TGLOGININFORMATION');
10) Compile Invalid Objects
conn sys@apx1314srv as sysdba
EXEC sys.UTL_RECOMP.recomp_serial;
select object_name from dba_objects where owner='LDBO' and status='INVALID';
select * from SYS.USER_ERRORS ;
11) Match Last and Current Year Database Objects (Run the following command in last and current year database and match the records)
Select Object_Type,Count(*) From User_Objects Group By Object_Type Order By 1;
select COUNT(*) from user_objects where status='INVALID';
Select * From Dba_Synonyms Where Table_Owner='LDBO';
select * from dba_Constraints where owner='LDBO';
SELECT * FROM DBA_DB_LINKS;
SELECT * FROM DBA_DIRECTORIES;
12) Create Users
conn sys@apx1213srv as sysdba
set head off
set pages 0
set long 9999999
spool c:\yep1314\user_script.sql
SELECT DBMS_METADATA.GET_DDL('PROFILE',profile) || '/' DDL
FROM DBA_PROFILES WHERE profile != 'DEFAULT' GROUP BY PROFILE;
UNION ALL
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_DDL('ROLE', role) || '/' DDL
FROM dba_roles
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select username from dba_ts_quotas)
UNION ALLs
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_role_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_sys_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_tab_privs);
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', GRANTEE) || '/' DDL
FROM DBA_TAB_PRIVS GROUP BY GRANTEE;
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', GRANTEE) || '/' DDL
FROM DBA_ROLE_PRIVS GROUP BY GRANTEE;
spool off;
conn sys@apx1314srv as sysdba
@c:\yep1314\user_script.sql
13) Create Roles using database link
Note: Please check database link (lnk_previousyearbalance) is working or not before running following script
conn sys@apx1314srv as sysdba
BEGIN
FOR cur_rec IN (SELECT role
FROM dba_roles@lnk_previousyearbalance) LOOP
BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE ' || cur_rec.role ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,privilege
FROM DBA_SYS_PRIVS@lnk_previousyearbalance where grantee not in ('SYS','SYSTEM','SYSMAN','TSMSYS','WMSYS','RECOVERY_CATALOG_OWNER','RESOURCE','OUTLN','ORACLE_OCM','OEM_MONITOR','OEM_ADVISOR','MGMT_USER','IMP_FULL_DATABASE','EXP_FULL_DATABASE','DBA','CONNECT','AQ_ADMINISTRATOR_ROLE','DBSNMP','SCHEDULER_ADMIN')) LOOP
BEGIN
EXECUTE IMMEDIATE ('Grant ' || cur_rec.privilege || ' to ' || cur_rec.grantee );
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,privilege,table_name
FROM dba_tab_privs@lnk_previousyearbalance Where Grantor='LDBO') LOOP
BEGIN
EXECUTE IMMEDIATE 'Grant ' || cur_rec.privilege || ' on ' || cur_rec.table_name || ' to ' || cur_rec.grantee ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,privilege,table_name,column_name
FROM dba_col_privs@lnk_previousyearbalance Where Grantor='LDBO') LOOP
BEGIN
EXECUTE IMMEDIATE 'Grant '|| cur_rec.PRIVILEGE || '('|| cur_rec.COLUMN_NAME ||') on '|| cur_rec.TABLE_NAME || ' to ' || cur_rec.GRANTEE ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,granted_role
FROM dba_role_privs@lnk_previousyearbalance Where Grantee!='SYSTEM' and Grantee!='SYS' and Grantee!='DBSNML' and Grantee!='REPADMIN') LOOP
BEGIN
EXECUTE IMMEDIATE 'Grant '|| cur_rec.granted_role || ' to ' || cur_rec.GRANTEE ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
impdp ldbo/ldbo@apx1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213.DMP LOGFILE=apx1314metadata.LOG schemas=LDBO content=metadata_only EXCLUDE=TABLESPACE,DB_LINK,SEQUENCE,STATISTICS,USER,GRANT sqlfile=optable.sql
D:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe LDBO/LDBO@ALP1314SRV DIRECTORY=DPUMP_DIR1 DUMPFILE=ALP1213.DMP LOGFILE=ALP1314METADATA1.LOG CONTENT=METADATA_ONLY SCHEMAS=LDBO EXCLUDE=TABLESPACE,DB_LINK,SEQUENCE,STATISTICS,USER,GRANT,ROLE,ROLE_GRANT,DIRECTORY,PROCACT_SCHEMA,AUDIT_OBJ sqlfile=optable1.sql
How to create Database structure same as another Database using Export Dump
If
you want to create a Database Skeleton (All tables (without Data), indexes and
other objects) same as another database
Then
You just need the full export dump (apx1213.dmp) of source database
Source
database A: apx1213
Target
Database B: apx1314
1)
Create
database using Database
Configuration Assistant (DBCA)
2)
And import dump (without data) using following
command
imp
ldbo@apx1314srv FILE=D:\apx1213.dmp ROWS=N FULL=Y CONSTRAINTS=Y GRANTS=Y
INDEXES=Y STATISTICS=NONE
Tuesday, December 18, 2012
sqlserver to oracle10g / 11g connection
Accessing SQL Server from Oracle with Database Gateway for ODBC (DG4ODBC)
To connect Oracle to a non-Oracle system through DG4ODBC:
Install and configure the ODBC driver on the machine where DG4ODBC is installed.
D:\app\Administrator\product\11.2.0\dbhome_1\BIN\dg4odbc.exe
D:\oracle\product\10.2.0\db_1\bin\hsodbc.exe
D:\oracle\product\10.2.0\db_1\bin\dg4odbc.exe
for 10g 32 bit listener
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\oracle\product\10.2.0\db_1)
(PROGRAM=hsodbc)
)
for 11g OR 10G 64 BIT listener
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\oracle\product\10.2.0\db_1)
(PROGRAM=dg4odbc)
)
Configure Oracle:
Database gateway (init*.ora). initdg4odbc.ora
Database listener (listener.ora).
Network client (tnsnames.ora).
Create a database link with SQL*Plus.
----------odbcad32---------------------
datasource name (DSN) : dg4odbc
description: dg4odbc
sqlserver : 172.168.0.1
database name: mysqlserverdatabase
username: sa(sqlserver user to connect to oracle)
password: sa
test connection successfully
--------------------oracle------
Select * from v$parameter where name like 'global_names%';
alter system set global_names=false scope = both;
create public database link lnk_sqlserver connect to "sa" identified by "sa" using 'dg4odbc';
username password should be double quotes
The db link name is sqlserver. Username and password must be in double quotes, because the username and password are case sensitive in SQL Server. 'DG4ODBC' points to the alias in the tnsnames.ora file that calls the HS subsystem.
rename init<>.ora to initdg4odbc.ora
D:\app\Administrator\product\11.2.0\dbhome_1\hs\admin\initdg4odbc.ora
D:\oracle\product\10.2.0\db_1\hs\admin\initdg4odbc.ora
HS_FDS_CONNECT_INFO = dg4odbc
HS_FDS_TRACE_LEVEL = off
-----------listener-----D:\oracle\product\10.2.0\db_1------10g
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\oracle\product\10.2.0\db_1)
(PROGRAM=hsodbc)
)
Please check hsodbc, dg4odbc at $oracle_home/bin
-----------listener-----D:\oracle\product\10.2.0\db_1------11g
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\oracle\product\10.2.0\db_1)
(PROGRAM=dg4odbc)
)
-----------------tnsnames.ora--------D:\oracle\product\10.2.0\db_1-----
Host,SID should be same as listener entry and should be of Oracle (not sql server)
dg4odbc =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.1)(PORT=1521))
(CONNECT_DATA=(SID=dg4odbc))
(HS=OK)
)
Now Check the connection
select * from all_catalog@lnk_sqlserver
select * from "systables"@lnk_sqlserver;
INSERT INTO DBO.AMR@LNKASIANTRANSMIT("Header","ClientCode","BaseDeposit","SegmentAccount")
values('03','ksh-001',1,'a');
INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit,SegmentAccount)
values('03','ksh-001',1,'a');
INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit)
values('03','ksh-001','a');
Enclose the column names in double quotes due to case sensitivitiy of the columns
INSERT INTO DBO.AMR@LNKASIANTRANSMIT("Header","ClientCode","BaseDeposit","SegmentAccount")
values('03','ksh-001',1,'a');
SQL> alter package pk_asianauto compile body;
Warning: Package Body altered with compilation errors.
SQL> sho err
Errors for PACKAGE BODY PK_ASIANAUTO:
LINE/COL ERROR
-------- -----------------------------------------------------------------
28/13 PL/SQL: SQL Statement ignored
28/80 PL/SQL: ORA-00904: "SEGMENTACCOUNT": invalid identifier
60/13 PL/SQL: SQL Statement ignored
60/114 PL/SQL: ORA-00904: "ACTION": invalid identifier
92/13 PL/SQL: SQL Statement ignored
92/85 PL/SQL: ORA-00904: "ISPRIMARYDEALER": invalid identifier
126/13 PL/SQL: SQL Statement ignored
127/13 PL/SQL: ORA-00904: "ADHOCCAP": invalid identifier
169/13 PL/SQL: SQL Statement ignored
171/70 PL/SQL: ORA-00904: "FLAG": invalid identifier
204/13 PL/SQL: SQL Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
204/57 PL/SQL: ORA-00904: "CLIENTCODE": invalid identifier
261/17 PL/SQL: SQL Statement ignored
262/48 PL/SQL: ORA-00904: "DAYSELLAMT": invalid identifier
266/17 PL/SQL: SQL Statement ignored
267/48 PL/SQL: ORA-00904: "DAYSELLAMT": invalid identifier
278/14 PL/SQL: SQL Statement ignored
278/90 PL/SQL: ORA-00904: "SEGMENTACCOUNT": invalid identifier
288/14 PL/SQL: SQL Statement ignored
288/90 PL/SQL: ORA-00904: "SEGMENTACCOUNT": invalid identifier
SQL> INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit,SegmentAccount)
2 values('03','ksh-001','a','a');
INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit,SegmentAccount)
*
ERROR at line 1:
ORA-00904: "SEGMENTACCOUNT": invalid identifier
SQL> INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit)
2 values('03','ksh-001','a');
INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit)
*
ERROR at line 1:
ORA-00904: "BASEDEPOSIT": invalid identifier
SQL> INSERT INTO DBO.AMR@LNKASIANTRANSMIT("Header","ClientCode","BaseDeposit","SegmentAccount")
2 values('03','ksh-001','a','a');
values('03','ksh-001','a','a')
*
ERROR at line 2:
ORA-28534: Heterogeneous Services preprocessing error
-------------
we are trying to insert char into number
--------------------
SQL> desc DBO.AMR@LNKASIANTRANSMIT
Name Null? Type
----------------------------------------- -------- ----------------------------
Header VARCHAR2(2)
ClientCode VARCHAR2(25)
BaseDeposit NUMBER(24,6)
SegmentAccount VARCHAR2(15)
SQL> INSERT INTO DBO.AMR@LNKASIANTRANSMIT("Header","ClientCode","BaseDeposit","SegmentAccount")
2 values('03','ksh-001',1,'a');
1 row created.
SQL> rollback;
Subscribe to:
Posts (Atom)