Showing posts with label oracle scripts. Show all posts
Showing posts with label oracle scripts. Show all posts

Friday, August 24, 2012

Oracle alert.log to Table



The following script creates two tables: read_alert andread_alert_disk.
read_alert_disk is an external table and contains the content of the alert log.
read_alert will be empty after this script has been executed. It is used by the update_alert_log script, shown further below.
define alert_length="2000"

drop table alert_log;

create table alert_log (
  alert_date date,
  alert_text varchar2(&&alert_length)
)
storage (initial 512k next 512K pctincrease 0);

create index alert_log_idx on alert_log(alert_date)
storage (initial 512k next 512K pctincrease 0);

column db    new_value _DB    noprint;
column bdump new_value _bdump noprint;

select instance_name db from v$instance;

select value bdump from v$parameter 
 where name ='background_dump_dest';


drop   directory BDUMP;
create directory BDUMP as '&&_bdump';

drop table alert_log_disk;

create table alert_log_disk ( text varchar2(&&alert_length) )
organization external (
  type oracle_loader
  default directory BDUMP
      access parameters (
          records delimited by newline nologfile nobadfile
          fields terminated by "&" ltrim
      )
  location('alert_&&_DB..log')
)
reject limit unlimited;

update_alert_log.sql

Now, after the two tables are created, the alert_log table can be filled with the following script. It only loads those records that are greater than the last time it loaded. And it loads the date/time on every line for convienance. It also helps when the alertlogs get rotated. You still keep the history within an Oracle table. Finally, it also strips out all the «crap» that is really not needed to see if you are looking for errors.
update_alert_log.sql
set serveroutput on 

declare
  
  isdate         number := 0;
  start_updating number := 0;
  rows_inserted  number := 0;
  
  alert_date     date;
  max_date       date;
  
  alert_text     alert_log_disk.text%type;

begin
  
  /* find a starting date */
  select max(alert_date) into max_date from alert_log;
  
  if (max_date is null) then
    max_date := to_date('01-jan-1980', 'dd-mon-yyyy');
  end if;
  
  for r in (
    select substr(text,1,180) text from alert_log_disk
     where text not like '%offlining%' 
       and text not like 'ARC_:%' 
       and text not like '%LOG_ARCHIVE_DEST_1%'
       and text not like '%Thread 1 advanced to log sequence%'
       and text not like '%Current log#%seq#%mem#%'
       and text not like '%Undo Segment%lined%'
       and text not like '%alter tablespace%back%'
       and text not like '%Log actively being archived by another process%'
       and text not like '%alter database backup controlfile to trace%'
       and text not like '%Created Undo Segment%'
       and text not like '%started with pid%'
       and text not like '%ORA-12012%'
       and text not like '%ORA-06512%'
       and text not like '%ORA-000060:%'
       and text not like '%coalesce%'
       and text not like '%Beginning log switch checkpoint up to RBA%'
       and text not like '%Completed checkpoint up to RBA%'
       and text not like '%specifies an obsolete parameter%'
       and text not like '%BEGIN BACKUP%'
       and text not like '%END BACKUP%'
  )
  loop
  
    isdate     := 0;
    alert_text := null;
  
    select count(*) into isdate 
      from dual 
     where substr(r.text, 21) in ('2003','2004','2005','2006','2007')
       and r.text not like '%cycle_run_year%';
  
    if (isdate = 1) then  
  
      select to_date(substr(r.text, 5),'Mon dd hh24:mi:ss rrrr') 
        into alert_date 
        from dual;
  
      if (alert_date > max_date) then
        start_updating := 1;
      end if;
  
    else
      alert_text := r.text;
    end if;
  
    if (alert_text is not null) and (start_updating = 1) then
     
      insert into alert_log values (alert_date, substr(alert_text, 1, 180));
      rows_inserted := rows_inserted + 1;
      commit;
  
    end if;
  
  end loop;
  
  sys.dbms_output.put_line('Inserting after date '||to_char(max_date, 'MM/DD/RR HH24:MI:SS'));
  sys.dbms_output.put_line('Rows Inserted: '||rows_inserted);
  
  commit;

end;
/
Let's execute the script:
SQL> @update_alert_log

Inserting after date 01/01/80 00:00:00
Rows Inserted: 17361

PL/SQL procedure successfully completed.
The alert_log table now contains the errors as recorded in thealert.log file:
select alert_date, substr(alert_text,1, 69) 
  from alert_log;

Posted by at No comments:
Labels: ,

Tuesday, August 7, 2012

restrict USER any software other than SQLPlus to connect to Oracle database


/*restrict any software other than SQL*Plus to connect to Oracle database. */

/* Oracle Job Scheduler  DBMS_JOB */
create or replace procedure p_jobscheduler is
begin
/* Submit a job to DBMS_JOB  as below */
/* jobno is a  bind variable you need to declare before running this block*/
/* SYSDATE + 1/18000 makes the job run after every 5 seconds */
    DBMS_JOB.SUBMIT (:jobno,'P_SCHEDULE;', SYSDATE, 'SYSDATE+1/18000');
end;
/
/* Procedure should run in INTERNAL/SYS/SYSTEM in order to function*/
CREATE OR REPLACE PROCEDURE P_SCHEDULE IS
    CURSOR C_SESSION IS  SELECT SID,SERIAL#,USERNAME,MODULE
                FROM V$SESSION
                WHERE USERNAME IS NOT NULL
                AND     USERNAME NOT IN ('SYS','SYSTEM','LDBO');
/* Users SYS,SYSTEM,INTERNAL have been allowed to connect via any software */
/* Retrieve all the relevant columns from v$session*/
BEGIN
     FOR C_KS IN C_SESSION LOOP
/* Cursor For loop */          
          IF NOT C_KS.MODULE = 'SQL*Plus' THEN
            dbms_output.put_line(C_KS.MODULE);
/* You should be in Oracle 8i and above to make this statement work. */          
            EXECUTE IMMEDIATE ('ALTER SYSTEM KILL SESSION ''' || C_KS.SID || ',' || C_KS.SERIAL# ||
'''');
        END IF;
     END LOOP;
EXCEPTION
/* Oops..something went wrong  !! Have a look ... turn on your serveroutput */          
     WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('ERROR OCCURRED' || SQLERRM);
END;
/

Posted by at No comments:
Labels:

Tuesday, July 24, 2012

Finding NULL in column


select column_name, num_nulls from all_tab_columns where table_name = 'SOME_TABLENAME' and owner = 'SOME_OWNER';

---------column contain Null------------------
set serveroutput on
declare
  l_count integer;
begin
  for col in (select table_name, column_name
              from user_tab_columns where table_name='&Enter_Table_Name')
  loop
    execute immediate 'select count(*) from '||col.table_name
                      ||' where '||col.column_name
                      ||' is null and rownum=1'
      into l_count;
    if l_count != 0 then
      dbms_output.put_line ('Column '||col.column_name||' contains nulls');
    end if;
  end loop;
end;
/


----column contain Only Null----------------------------------
set serveroutput on
declare
  l_count integer;
begin
  for col in (select table_name, column_name
              from user_tab_columns where table_name='&Enter_Table_Name')
  loop
    execute immediate 'select count(*) from '||col.table_name
                      ||' where '||col.column_name
                      ||' is not null and rownum=1'
      into l_count;
    if l_count = 0 then
      dbms_output.put_line ('Column '||col.column_name||' contains only nulls');
    end if;
  end loop;
end;
/

----------------------------------------------------------
Posted by at No comments:
Labels:

Finding Any Character / NULL in All tables, Columns in Schema

create or replace function find_in_schema(val varchar2)
return 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('  rowid: ' || v_rowid || ' in ' || v_old_table);
        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;
  return 'Success';
end;
/


set serveroutput on size 1000000 format wrapped

select find_in_schema('KSHITIJ') from dual;

it provide the rowid with table

select find_in_schema(NULL) from dual;

select find_in_schema(NULL) from TABLE_NAME;


---------------------------------------------------
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "LDBO.FIND_IN_SCHEMA", line 45
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:  
*Action:
--------------------------------------------------------------------
v_where     Varchar2(4000);       to        v_where     Varchar2(32766);
you might try to load a column in a record field that is defined with a smaller size than the data returned from the select.


Posted by at No comments:
Labels:

Tuesday, May 22, 2012

Database Link Creation Script



set pagesize 0
set linesize 30000
set long 500000
set longchunksize 500000
set trimspool on
set feed off


------------Drop Database link

Select 'DROP '||Decode(U.Name,'PUBLIC','public ')||'database link '||Chr(10)
||Decode(U.Name,'PUBLIC',Null, U.Name||'.')|| L.Name||Chr(10) ||';' TEXT
From Sys.Link$ L, Sys.User$ U
Where L.Owner# = U.User#;


------------Create Database link

SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||Decode(U.Name,'PUBLIC',Null, U.Name||'.')|| L.Name||Chr(10)
||' connect to ' || L.Userid || ' identified by values '''
||L.PASSWORDX||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
From Sys.Link$ L, Sys.User$ U
WHERE L.OWNER# = U.USER#;


------------------------CHECK LINK

select 'SELECT INSTANCE_NAME,HOST_NAME FROM V$INSTANCE@' ||Db_Link || ';' from dba_db_links;

Posted by at No comments:
Labels:

Wednesday, May 2, 2012

Oracle 11g Embed Text to Sound translator



CREATE TABLE EMP (EMPNO VARCHAR2(10),ENAME VARCHAR2(20), SAL VARCHAR2(10), JOB VARCHAR2(10) ,HIREDATE DATE,DEPTNO VARCHAR2(10));


 INSERT INTO EMP VALUES('10','KSHITIJ','100','DBA','01-DEC-2011','12');
COMMIT;

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


begin
        dbms_network_acl_admin.create_acl (
                acl             => 'utlpkg.xml',
                description     => 'Normal Access',
                principal       => 'CONNECT',
                is_grant        => TRUE,
                privilege       => 'connect',
                start_date      => null,
                end_date        => null
        );
  dbms_network_acl_admin.add_privilege (
  acl => 'utlpkg.xml',
  principal => 'DPCDSL',
  is_grant => TRUE,
  privilege => 'connect',
  start_date => null,
  end_date => null);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'utlpkg.xml',
    host        => 'translate.google.com',
    lower_port  => 80,
    upper_port  => NULL);

  COMMIT;
END;
/

--------------------------------------------------------------------------------------------------------------------------------
create or replace
package mytranslator
as

function getMP3
( p_text in varchar2
, p_language in varchar2 default 'en'
) return blob
;

procedure play_sound
( p_text in varchar2
);

procedure play_employee
( p_empno    in number
, p_language in varchar2 default 'en'
);

procedure soundcheck;
end;
/

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

create or replace
package body mytranslator
as

function load_binary_from_url (p_url  IN  VARCHAR2)
return blob
AS
  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;
  l_blob           BLOB;
  l_raw            RAW(32767);
BEGIN
  -- Initialize the BLOB.
  DBMS_LOB.createtemporary(l_blob, FALSE);
  -- Make a HTTP request and get the response.
  l_http_request  := UTL_HTTP.begin_request(p_url);
  l_http_response := UTL_HTTP.get_response(l_http_request);
  -- Copy the response into the BLOB.
  BEGIN
    LOOP
      UTL_HTTP.read_raw(l_http_response, l_raw, 32767);
      DBMS_LOB.writeappend (l_blob, UTL_RAW.length(l_raw), l_raw);
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.end_of_body
THEN
      UTL_HTTP.end_response(l_http_response);
  end;
  return l_blob;
end load_binary_from_url;


function getMP3
( p_text in varchar2
, p_language in varchar2 default 'en'
) return blob
is
  l_base_url varchar2(2000):= 'http://translate.google.com/translate_tts?tl=';
begin
  return load_binary_from_url
  ( p_url=> l_base_url||p_language||chr(38)||'q='
  ||utl_url.escape(p_text)
  );
 -- note: chr(38) is the ampersand
end getMP3;

procedure download_blob
( p_blob in out nocopy blob
) as
  l_mime_type varchar2(30):= 'audio/mpeg';
  l_blob_size number(10);
begin
l_blob_size := dbms_lob.getlength(p_blob);
owa_util.mime_header(l_mime_type, FALSE, NULL);
    htp.p('Content-length: '|| l_blob_size);
  owa_util.http_header_close;
  -- download BLOB
wpg_docload.download_file(p_blob);
end download_blob;

procedure play_sound
( p_text in varchar2
) is
  l_blob blob;
begin
  l_blob:= getMP3 ( p_text);
  download_blob (l_blob);
end play_sound;

procedure play_employee
( p_empno    in number
, p_language in varchar2 default 'en'
) is
  l_blob blob;
  l_blob2 blob;
begin

  l_blob:= getMP3 ( 'Details on Employee', p_language);
 
  for emp in (select ename, sal, job, to_char(hiredate, 'MONTH YYYY') hireyear, deptno
              from   emp
              where  empno = p_empno
              ) loop
    l_blob2:= getMP3 ( 'Employee is called '||emp.ename||' his job is '||emp.job, p_language);
    DBMS_LOB.APPEND
    ( dest_lob => l_blob
    , src_lob  => l_blob2
    );
    l_blob2:= getMP3 ( 'He works in department '||emp.deptno||' and earns  '||emp.sal||' dollar', p_language);
    DBMS_LOB.APPEND
    ( dest_lob => l_blob
    , src_lob  => l_blob2
    );
    l_blob2:= getMP3 ( 'He was hired back in '||emp.hireyear, p_language);
    DBMS_LOB.APPEND
    ( dest_lob => l_blob
    , src_lob  => l_blob2
    );
  end loop;
 
  download_blob (l_blob);  
exception
  when others
  then
    l_blob:= getMP3 ( 'Exception occurred '||sqlerrm, p_language);
    download_blob (l_blob);  
end play_employee;


procedure soundcheck
is
  l_blob blob;
  space varchar2(4):='%20';
begin

  l_blob:= getMP3 ( 'Test one two three test', p_language=> 'en');
  download_blob (l_blob);  
exception
  when others
  then
    l_blob:= getMP3 ( 'Exception'||space||'occurred', p_language =>'en');
    download_blob (l_blob);  
end soundcheck;


end;

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

select mytranslator.getmp3('hello how r u what is your name. my name is john') from dual

select mytranslator.play_sound('hello how r u what is your name. my name is john') from dual


BEGIN
  DBMS_EPG.create_dad
  ( dad_name => 'hrmreport'
  , path => '/hrm/*'
  );
  DBMS_EPG.AUTHORIZE_DAD('hrmreport','DPCDSL');
end;
/


http://blog.mclaughlinsoftware.com/2009/12/01/oracle-11g-xdb-dads/

http://www.oracle-base.com/articles/10g/dbms_epg_10gR2.php

http://psoug.org/reference/dbms_epg.html

SELECT * FROM dba_epg_dad_authorization;

Posted by at No comments:
Labels: ,

Monday, April 16, 2012

Compile Invalid Objects

SET HEADING OFF

spool c:\temp\invalid.sql ;
select OBJECT_NAME from dba_objects where STATUS='INVALID';

select
'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '."' || OBJECT_NAME || '" COMPILE;' from dba_objects where status = 'INVALID'
and object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW','TRIGGER');

Select decode( object_type, 'PACKAGE BODY', 'ALTER PACKAGE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;')
from dba_objects
where status = 'INVALID' and object_type in ('PACKAGE BODY') order by object_type;

select 'ALTER ' || OWNER || ' ' || OBJECT_TYPE || ' "' || OBJECT_NAME || '" COMPILE;' from dba_objects where status = 'INVALID'
and object_type in ('SYNONYM');

Select decode( object_type, 'TYPE', 'ALTER TYPE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;')
from dba_objects where status = 'INVALID' and object_type in ('TYPE') order by object_type;

Select decode( object_type, 'TYPE BODY', 'ALTER TYPE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;')
from dba_objects
where status = 'INVALID' and object_type in ('TYPE BODY') order by object_type;

spool out ;
@ c:\temp\invalid.sql ;

Posted by at No comments:
Labels:

Wednesday, April 11, 2012

Find Duplicate records (Unique Index columns) in all Tables

SELECT 'SELECT COUNT(*),' || wm_concat(column_name) || ' from ' ||table_name || ' group by ' || wm_concat(column_name) || ' having count(*)>1;'
FROM user_ind_columns where index_name in (select index_name from user_indexes where uniqueness='UNIQUE')
GROUP BY index_name,table_name;


Posted by at No comments:
Labels: ,

Saturday, March 24, 2012

PGA Top Consumer

set pagesize 1000
set lines 100
col sid format 9999
col username format a12
col module format a30
column pga_memory_mb format 9,999.99 heading "PGA MB"
column max_pga_memory_mb format 9,999.99 heading "PGA MAX|MB"
col service name format a20
col sql_text format a70 heading "Currently executing SQL"
set echo on

WITH pga AS
(SELECT sid,
ROUND(SUM(CASE name WHEN 'session pga memory'
THEN VALUE / 1048576 END),2) pga_memory_mb,
ROUND(SUM(CASE name WHEN 'session pga memory max'
THEN VALUE / 1048576 END),2) max_pga_memory_mb
FROM v$sesstat
JOIN v$statname USING (statistic#)
WHERE name IN ('session pga memory','session pga memory max' )
GROUP BY sid)
SELECT sid, username,s.module,
pga_memory_mb,
max_pga_memory_mb, substr(sql_text,1,70) sql_text
FROM v$session s
JOIN (SELECT sid, pga_memory_mb, max_pga_memory_mb,
RANK() OVER (ORDER BY pga_memory_mb DESC) pga_ranking
FROM pga)
USING (sid)
LEFT OUTER JOIN v$sql sql
ON (s.sql_id=sql.sql_id and s.sql_child_number=sql.child_number)
WHERE pga_ranking <=5
ORDER BY pga_ranking
/
Posted by at No comments:
Labels: , ,

Thursday, March 15, 2012

Database Growth Monitoring

Step : 1 Calculate total Size of tablespace

select sum(bytes)/1024/1024 "TOTAL SIZE (MB)" from dba_Data_files;


Step : 2 Calculate Free Space in Tablespace

select sum(bytes)/1024/1024 "FREE SPACE (MB)" from dba_free_space;

Step : 3 Calculate total size , free space and used space in tablespace

select t2.total "TOTAL DISK USAGE",t1.free "FREE SPACE",(t1.free/t2.total)*100 "FREE (%)",(t2.total-t1.free) "USED SPACE", (1-t1.free/t2.total)*100 "USED (%)"
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 , (select sum(bytes)/1024/1024 total from dba_Data_files) t2 ;


Step : 4 Create table which is store all free/use space related information of tablespace

create table db_growth
as select *
from (
select sysdate,t2.total "TOTAL_DISK_USAGE",t1.free "FREE_SPACE",(t2.total-t1.free) "USED_SPACE",(t1.free/t2.total)*100 "FREE% "
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
);

Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)

insert into db_growth
select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t2.total-t1.free) "USED_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
);

COMMIT;


Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)


create view v_db_growth
as select *
from
(
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t2.total-t1.free) "USED_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
;

Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view


insert into db_growth select *
from v_db_growth;
COMMIT;


Step : 8 Check everything goes fine.

select * from db_growth;

Check Result

Step : 9 Execute following SQL for more time stamp information

alter session set nls_date_format ='dd-mon-yyyy hh24:mi:ss' ;
Session altered.

Step : 10 Create a DBMS jobs which execute after 24 hours

declare
jobno number;
begin
dbms_job.submit(
jobno, 'begin insert into db_growth select * from v_db_growth;commit;end;', sysdate, 'SYSDATE+ 1', TRUE);
commit;
end;
/


PL/SQL procedure successfully completed.

Step: 11 View your dbms jobs and it's other information

select * from user_jobs;


-----If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically

exec dbms_job.run(ENTER_JOB_NUMBER)
exec dbms_job.run(23);



PL/SQL procedure successfully completed.

exec dbms_job.remove(21); ------to remove a job


Step: 12 Finally all data populated in db_growth table

select * from db_growth;
Posted by at No comments:
Labels: ,

Get DDL


GET_DEPENDENT_DDL(object_type, base_object_name, base_object_schema, version, model, transform, object_count)

GET_GRANTED_DDL(object_type, grantee, version, model, transform, object_count)


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

select DBMS_METADATA.GET_DDL('TABLE','ACCOUNTS')||'/' from dual;

----------GET_DEPENDENT_DDL
select DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','ACCOUNTS') aa from dual;

select DBMS_METADATA.GET_DEPENDENT_DDL('TRIGGER','ACCOUNTS') aa from dual;

select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','ACCOUNTS') aa from dual;

SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT','ACCOUNTS') from dual;

SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','ACCOUNTS') from dual;


--------------------------------
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema>') from dual;

select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<schema>') from dual;

select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','<schema>') from dual;

select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','KSH') aa from dual;

-----------------------------------------------------------------------------------------------------------------------------
SET LONG 1000000

select dbms_metadata.get_ddl( 'USER', 'LDBO' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'LDBO' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'LDBO' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'LDBO' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'LDBO' ) from dual;


-----------------------------------------------------------------------------------------------------------------------------
CREATE TABLE my_ddl (owner VARCHAR2(30),
                     table_name VARCHAR2(30),
                     ddl   CLOB);
INSERT INTO my_ddl (owner, table_name, ddl)
SELECT owner, table_name,
DBMS_METADATA.GET_DDL('TABLE', table_name, owner) ddl
 FROM DBA_TABLES WHERE OWNER = 'LDBO';
Posted by at No comments:
Labels:

Table Actual Size

SELECT
owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
WHERE owner ='LDBO'
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;

Posted by at No comments:
Labels: ,

Role Recreation

set heading off verify off feedback off echo off term off linesize 200 wrap on

spool c:\temp\roles_creation.sql

SELECT 'Create Role '|| ROLE ||' ;' from dba_roles;

SELECT 'Grant '|| PRIVILEGE || ' to ' || GRANTEE || ';' FROM DBA_SYS_PRIVS 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');

SELECT 'Grant '|| PRIVILEGE ||' on '|| TABLE_NAME || ' to ' || GRANTEE || ';' from dba_tab_privs Where Grantor='LDBO';

SELECT 'Grant update('|| COLUMN_NAME ||') on '|| TABLE_NAME || ' to ' || GRANTEE || ';' from dba_col_privs Where Grantor='LDBO';

spool off


Posted by at No comments:
Labels: ,

Friday, October 21, 2011

Data Pump EXPDP

expdp userid="'sys/oracle@ari1112srv as sysdba'" FULL=y directory=dpump_dir2 dumpfile=ari1112.DMP LOGFILE=ari1112.LOG



expdp ldbo/ldbo$123@ari1112srv directory=dpump_dir1 schemas=ldbo dumpfile=ari1112_tab1.DMP LOGFILE=ari1112_tab.LOG INCLUDE=TABLE:\"IN \(\'BILLCHARGESFILE2\',\'BILLINCLUSIVECHARGESFILE2\',\'CBILLFILE2\',\'DBILLFILE2\',\'FALEDDRCR2\',\'FALEDGERDESCRIPTION2\',\'FOCBILLFILE2\',\'FODBILLFILE2\',\'FOTRANSACTIONS2\',\'TRANSACTION2\'\)\"



expdp ldbo/ldbo$123@ari1112srv full=Y directory=dpump_dir1 dumpfile=ari1112_meta.DMP LOGFILE=ari1112_meta.LOG content=metadata_only EXCLUDE=TABLE:\"IN \(\'CLIENTSCANNEDIMAGE\',\'BILLCHARGESFILE\',\'BILLINCLUSIVECHARGESFILE\',\'CBILLFILE\',\'DBILLFILE\',\'FALEDDRCR\',\'FALEDGERDESCRIPTION\',\'FOCBILLFILE\',\'FODBILLFILE\',\'FOTRANSACTIONS\',\'TRANSACTIONS\',\'MV_CAPABILITIES_TABLE\',\'MV_RKCASHCLISCRIPTRANSACTION\',\'MV_RKCASHTRANSACTION\',\'MV_RKCLIENT\',\'MV_RKEXCHANGETURNOVER\',\'MV_RKFOCLISCRIPTRANSACTION\',\'MV_RKFOTRANSACTION\'\)\"



expdp ldbo/ldbo$123@ari1112srv directory=dpump_dir1 dumpfile=ari1112_package.DMP LOGFILE=ari1112_package.LOG INCLUDE=PACKAGE_BODIES,PACKAGE,PROCEDURE,FUNCTION
Posted by at No comments:
Labels: ,

Tuesday, September 20, 2011

Remove partition without delete data

1)

ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1000, BR1050 INTO PARTITION BR1050 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1050, BR1100 INTO PARTITION BR1100 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1100, BR1150 INTO PARTITION BR1150 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1150, BR1200 INTO PARTITION BR1200 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1200, BR1250 INTO PARTITION BR1250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1250, BR1300 INTO PARTITION BR1300 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1300, BR1350 INTO PARTITION BR1350 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1350, BR1400 INTO PARTITION BR1400 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1400, BR1450 INTO PARTITION BR1450 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1450, BR1500 INTO PARTITION BR1500 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1500, BR1550 INTO PARTITION BR1550 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1550, BR1600 INTO PARTITION BR1600 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1600, BR1650 INTO PARTITION BR1650 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1650, BR1700 INTO PARTITION BR1700 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1700, BR1750 INTO PARTITION BR1750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1750, BR1800 INTO PARTITION BR1800 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1800, BR1850 INTO PARTITION BR1850 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1850, BR1900 INTO PARTITION BR1900 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1900, BR1950 INTO PARTITION BR1950 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR1950, BR2000 INTO PARTITION BR2000 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2000, BR2050 INTO PARTITION BR2050 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2050, BR2100 INTO PARTITION BR2100 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2100, BR2150 INTO PARTITION BR2150 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2150, BR2200 INTO PARTITION BR2200 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2200, BR2250 INTO PARTITION BR2250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2250, BR2300 INTO PARTITION BR2300 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2300, BR2350 INTO PARTITION BR2350 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2350, BR2400 INTO PARTITION BR2400 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2400, BR2450 INTO PARTITION BR2450 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2450, BR2500 INTO PARTITION BR2500 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2500, BR2550 INTO PARTITION BR2550 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2550, BR2600 INTO PARTITION BR2600 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2600, BR2650 INTO PARTITION BR2650 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2650, BR2700 INTO PARTITION BR2700 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2700, BR2750 INTO PARTITION BR2750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2750, BR2800 INTO PARTITION BR2800 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2800, BR2850 INTO PARTITION BR2850 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2850, BR2900 INTO PARTITION BR2900 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2900, BR2950 INTO PARTITION BR2950 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR2950, BR3000 INTO PARTITION BR3000 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3000, BR3050 INTO PARTITION BR3050 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3050, BR3100 INTO PARTITION BR3100 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3100, BR3200 INTO PARTITION BR3200 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3200, BR3250 INTO PARTITION BR3250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3250, BR3350 INTO PARTITION BR3350 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3350, BR3450 INTO PARTITION BR3450 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3450, BR3550 INTO PARTITION BR3550 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3550, BR3650 INTO PARTITION BR3650 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3650, BR3750 INTO PARTITION BR3750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3750, BR3850 INTO PARTITION BR3850 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3850, BR3950 INTO PARTITION BR3950 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR3950, BR4000 INTO PARTITION BR4000 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4000, BR4050 INTO PARTITION BR4050 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4050, BR4100 INTO PARTITION BR4100 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4100, BR4200 INTO PARTITION BR4200 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4200, BR4250 INTO PARTITION BR4250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4250, BR4350 INTO PARTITION BR4350 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4350, BR4450 INTO PARTITION BR4450 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4450, BR4550 INTO PARTITION BR4550 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4550, BR4650 INTO PARTITION BR4650 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4650, BR4750 INTO PARTITION BR4750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4750, BR4850 INTO PARTITION BR4850 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4850, BR4950 INTO PARTITION BR4950 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR4950, BR5050 INTO PARTITION BR5050 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5050, BR5100 INTO PARTITION BR5100 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5100, BR5200 INTO PARTITION BR5200 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5200, BR5250 INTO PARTITION BR5250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5250, BR5350 INTO PARTITION BR5350 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5350, BR5450 INTO PARTITION BR5450 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5450, BR5550 INTO PARTITION BR5550 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5550, BR5650 INTO PARTITION BR5650 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5650, BR5750 INTO PARTITION BR5750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5750, BR5850 INTO PARTITION BR5850 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5850, BR5950 INTO PARTITION BR5950 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR5950, BR6000 INTO PARTITION BR6000 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR6000, BR6250 INTO PARTITION BR6250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR6250, BR6500 INTO PARTITION BR6500 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR6500, BR6750 INTO PARTITION BR6750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR6750, BR7000 INTO PARTITION BR7000 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR7000, BR7250 INTO PARTITION BR7250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR7250, BR7500 INTO PARTITION BR7500 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR7500, BR7750 INTO PARTITION BR7750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR7750, BR8000 INTO PARTITION BR8000 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR8000, BR8250 INTO PARTITION BR8250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR8250, BR8500 INTO PARTITION BR8500 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR8500, BR8750 INTO PARTITION BR8750 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR8750, BR9000 INTO PARTITION BR9000 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR9000, BR9250 INTO PARTITION BR9250 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR9250, BR9500 INTO PARTITION BR9500 tablespace USR;
ALTER TABLE FALEDDRCR MERGE PARTITIONS BR9500, BRXXXX INTO PARTITION BRXXXX tablespace USR;


2)

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where table_name='FALEDDRCR';


3)

Create table faledrcr1 as select * from faleddrcr where 1=2;

or

CREATE TABLE "LDBO"."FALEDDRCR1"
(
"FIRMNUMBER" CHAR(10 BYTE) NOT NULL ENABLE,
"OOWNCODE" CHAR(10 BYTE) NOT NULL ENABLE,
"DTOFTRAN" DATE NOT NULL ENABLE,
"VOUCHER" CHAR(14 BYTE) NOT NULL ENABLE,
"VCNUMBER" NUMBER(5,0) NOT NULL ENABLE,
"DRCR" CHAR(1 BYTE) NOT NULL ENABLE,
"DAMOUNT" NUMBER(14,2) NOT NULL ENABLE,
"CAMOUNT" NUMBER(14,2) NOT NULL ENABLE,
"ENTRYCODE" NUMBER(2,0) NOT NULL ENABLE,
"BRANCHCODE" CHAR(4 BYTE) NOT NULL ENABLE,
"FAMILYCODE" CHAR(10 BYTE),
"SUBCODE" CHAR(10 BYTE),
"EXCHANGE" CHAR(4 BYTE) NOT NULL ENABLE,
"GROUPCODE" CHAR(10 BYTE),
"SPECIAL" CHAR(1 BYTE) DEFAULT 'N'
/* Contains Y or N indicating whether it is Special Entry or Normal */
,
"BOOKTYPE" CHAR(2 BYTE) DEFAULT ' '
/* Contains the Booktype Code */
,
"VALLAN" NUMBER(7,0) DEFAULT 0
/* Contains the Vallan Number */
,
"CHEQUE" CHAR(10 BYTE) DEFAULT ' '
/* Contains the Cheque Number */
,
"BANKCODE" CHAR(10 BYTE) DEFAULT ' '
/* Contains the Bank Code */
,
"BANKRECO" NUMBER(8,0) DEFAULT 0
/* Contains the Entry on Which Bank Reco is done */
,
"NPRODUCTCODE" NUMBER(3,0) DEFAULT 0
/* Contains the Product Code on Which the Ledger Entry is based */
,
"NORACLEFINANCIAL" NUMBER(1,0) DEFAULT 0
/* Contains the Flag for E-Financial Transfer */
,
"NENTRYTYPE" NUMBER(2,0) DEFAULT 0
/* Contains the Flag for Entry Type */
,
"NSUBPRODUCTCODE" NUMBER(3,0) DEFAULT 0
/* Contains the Sub-Product code */
,
CONSTRAINT "LEDGERPRIMARY" PRIMARY KEY ("FIRMNUMBER", "OOWNCODE", "VOUCHER", "VCNUMBER") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 838860800 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDX" ALTER INDEX "LDBO"."LEDGERPRIMARY" UNUSABLE;
ENABLE,
CONSTRAINT "CK_LEDGERDRCR" CHECK (Drcr IN ('D','C',' ')) ENABLE,
CONSTRAINT "CK_LEDGERENTRY" CHECK (Entrycode IN (1,2,3,4,5,6,7)) ENABLE,
CONSTRAINT "CK_LEDGERAMOUNT" CHECK ((Drcr ='D' AND Damount>0 AND Camount=0) OR (Drcr='C' AND Camount>0 AND Damount=0) OR (Drcr=' ' AND Camount=0 AND Damount=0)) ENABLE NOVALIDATE,
CONSTRAINT "LEDGERCODE" FOREIGN KEY ("FIRMNUMBER", "OOWNCODE") REFERENCES "LDBO"."ACCOUNTS" ("FIRMNUMBER", "OOWNCODE") ENABLE,
CONSTRAINT "LEDGERGROUP" FOREIGN KEY ("FIRMNUMBER", "GROUPCODE") REFERENCES "LDBO"."ACCOUNTGROUP" ("FIRMNUMBER", "OOWNCODE") ENABLE,
CONSTRAINT "LEDGEREXCHANGE" FOREIGN KEY ("FIRMNUMBER", "EXCHANGE") REFERENCES "LDBO"."ALLEXCH" ("FIRMNUMBER", "CODE") ENABLE,
CONSTRAINT "LEDGERBRANCH" FOREIGN KEY ("FIRMNUMBER", "BRANCHCODE") REFERENCES "LDBO"."BRANCHHO" ("FIRMNUMBER", "BRANCHCODE") ENABLE,
CONSTRAINT "LEDGERPRODUCT" FOREIGN KEY ("FIRMNUMBER", "NPRODUCTCODE") REFERENCES "LDBO"."TBLPRODUCTINFORMATION" ("CFIRMNUMBER", "NPRODUCTCODE") ENABLE ) PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 1048576000 NEXT 1048576000 BUFFER_POOL DEFAULT) TABLESPACE "USR"
) ;
CREATE UNIQUE INDEX "LDBO"."LEDGERPRIMARY" ON "LDBO"."FALEDDRCR1"
(
"FIRMNUMBER", "OOWNCODE", "VOUCHER", "VCNUMBER"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 838860800 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "INDX" ;
ALTER INDEX "LDBO"."LEDGERPRIMARY" UNUSABLE;
CREATE INDEX "LDBO"."VOUCHER" ON "LDBO"."FALEDDRCR1"
(
"FIRMNUMBER",
"VOUCHER"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 838860800 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "INDX" ;
ALTER INDEX "LDBO"."VOUCHER" UNUSABLE;
CREATE OR REPLACE TRIGGER "LDBO"."TG_FINANCE" AFTER
INSERT ON FALEDDRCR1 FOR EACH row DECLARE counter NUMBER;
BEGIN
SELECT COUNT(*)
INTO counter
FROM tblFintable
WHERE cFirmnumber =:new.firmnumber
AND cClientcode =:new.oowncode
AND cBranchcode =:new.Branchcode ;
IF NVL(counter,0) = 0 THEN
INSERT
INTO tblFintable
(
cFirmnumber,
cClientcode,
cBranchcode,
cDailychange
)
VALUES
(
:new.firmnumber,
:new.Oowncode,
:new.Branchcode,
'Y'
) ;
END IF ;
END ;
/
ALTER TRIGGER "LDBO"."TG_FINANCE" ENABLE;
CREATE OR REPLACE TRIGGER "LDBO"."TG_RKRECOUPDATE" AFTER
UPDATE OF BankReco ON FALEDDRCR1 FOR EACH row BEGIN Sp_RkBankreco(:Old.Firmnumber,:Old.Oowncode,:New.Bankreco,:Old.Bankreco,:Old.Voucher,:Old.Damount,:Old.Camount,:Old.Exchange,:New.Exchange) ;
END ;
/
ALTER TRIGGER "LDBO"."TG_RKRECOUPDATE" ENABLE;
CREATE OR REPLACE TRIGGER "LDBO"."TG_RKFINUPDATE" AFTER
INSERT OR
UPDATE OF Firmnumber,Oowncode,Damount,Camount,Exchange OR
DELETE ON FALEDDRCR1 FOR EACH row DECLARE lcOperationtype CHAR(1) ;
ldTransactiondate DATE ;
BEGIN
SELECT to_date(TO_CHAR(sysdate,'dd/mm/yyyy'),'dd/mm/yyyy hh:mi:ssam')
INTO ldTransactiondate
FROM dual ;
IF (((:Old.Vcnumber<10000 OR :New.Vcnumber<10000) AND (:Old.Entrycode=5 OR :New.Entrycode=5)) OR :New.Entrycode!=5 OR :Old.Entrycode!=5) THEN
IF Inserting THEN
lcOperationtype:='I' ;
INSERT
INTO tblFinentries
(
cOperationtype,
dTransactiondate,
cOldFirmnumber,
cNewFirmnumber,
cOldAccountcode,
cNewAccountcode,
cNewVoucher,
nOldDramount,
nNewDramount,
nOldCramount,
nNewCramount,
cOldExchange,
cNewExchange
)
VALUES
(
lcOperationtype,
ldTransactiondate,
:Old.Firmnumber,
:New.Firmnumber,
:Old.Oowncode,
:New.Oowncode,
:New.Voucher,
:Old.Damount,
:New.Damount,
:Old.Camount,
:New.Camount,
:Old.Exchange,
:New.Exchange
) ;
ELSE
IF Updating THEN
lcOperationtype:='U' ;
INSERT
INTO tblFinentries
(
cOperationtype,
dTransactiondate,
cOldFirmnumber,
cNewFirmnumber,
cOldAccountcode,
cNewAccountcode,
cNewVoucher,
nOldDramount,
nNewDramount,
nOldCramount,
nNewCramount,
cOldExchange,
cNewExchange
)
VALUES
(
lcOperationtype,
ldTransactiondate,
:Old.Firmnumber,
:New.Firmnumber,
:Old.Oowncode,
:New.Oowncode,
:New.Voucher,
:Old.Damount,
:New.Damount,
:Old.Camount,
:New.Camount,
:Old.Exchange,
:New.Exchange
) ;
ELSE
lcOperationtype:='D' ;
INSERT
INTO tblFinentries
(
cOperationtype,
dTransactiondate,
cOldFirmnumber,
cNewFirmnumber,
cOldAccountcode,
cNewAccountcode,
cNewVoucher,
nOldDramount,
nNewDramount,
nOldCramount,
nNewCramount,
cOldExchange,
cNewExchange
)
VALUES
(
lcOperationtype,
ldTransactiondate,
:Old.Firmnumber,
:New.Firmnumber,
:Old.Oowncode,
:New.Oowncode,
:Old.Voucher,
:Old.Damount,
:New.Damount,
:Old.Camount,
:New.Camount,
:Old.Exchange,
:New.Exchange
) ;
END IF ;
END IF ;
END IF ;
END tg_Rkfinbal;
/
ALTER TRIGGER "LDBO"."TG_RKFINUPDATE" ENABLE;



4)

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where table_name='FALEDDRCR1';



5)

ALTER TABLE FALEDDRCR EXCHANGE PARTITION BRXXXX WITH TABLE FALEDDRCR1;


6)

Drop table FALEDDRCR;

7)

ALTER TABLE FALEDDRCR1 RENAME TO FALEDDRCR;



8)

ALTER TABLE org_table_name
RENAME CONSTRAINT new_constraint_name TO org_constraint_name;



9)

select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where table_name='FALEDDRCR';

-------------------------second method-----------------

create table transactions2 as select * from transactions;


drop table transactions;


@c:\transaction_struct.sql


alter table transactions disable all triggers;


select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where table_name='TRANSACTIONS';


insert into transactions select * from transactions2;


alter table transactions ENABLE all triggers;


select 'alter table '||table_name||' ENABLE constraint '||constraint_name||';' from user_constraints where table_name='TRANSACTIONS';


--------------------------
Posted by at No comments:
Labels:

Wednesday, April 6, 2011

Constraint Check

SPOOL C:\CONS.TXT

select 'select '||cc.column_name-
||' from '||c.owner||'.'||c.table_name-
||' a where not exists (select ''x'' from '-
||r.owner||'.'||r.table_name-
||' where '||rc.column_name||' = a.'||cc.column_name||')'
from dba_constraints c,
dba_constraints r,
dba_cons_columns cc,
dba_cons_columns rc
where c.constraint_type = 'R'
and c.owner not in ('SYS','SYSTEM')
and c.r_owner = r.owner
and c.owner = cc.owner
and r.owner = rc.owner
and c.constraint_name = cc.constraint_name
and r.constraint_name = rc.constraint_name
and c.r_constraint_name = r.constraint_name
and cc.position = rc.position
and c.owner = 'LDBO'
and c.table_name = 'CEXIST'
and c.constraint_name = 'CEXISTBOOK';


SPOOL OFF

Posted by at No comments:
Labels: , ,
Subscribe to: Posts (Atom)

Followers