SQL & PL/SQL :: How To Get Valid Data After Skip Bad Block

Mar 19, 2011

I have a table and no related backup available.If some block is broken, then , how can I get the data with the blocks that are not broken?

Test environment:

SQL> create tablespace test datafile '/u01/app/oracle/oradata/test/test.dbf' size 1m;

Tablespace created.

SQL> create table scott.test_bad_block (id int, name varchar2(20)) tablespace test;

Table created.

SQL> r
1 begin
2 for i in 1..1000000 loop
3 insert into scott.test_bad_block values (i, 'test' || i);
4 commit;
5 end loop;
6* end;
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.TEST_BAD_BLOCK by 8 in tablespace TEST
ORA-06512: at line 3
SQL> select count(*) from scott.test_bad_block;

COUNT(*)
----------
40984

Now I know, I have 40984 rows in the table.In order to simulating the block corrupt, I shutdown the database and I used the bvi (a binary file editor to modify a line of record) , then start the database, when I am issuing the same statement, the problem shows:

SQL> select count(*) from scott.test_bad_block;
select count(*) from scott.test_bad_block *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted[b] (file # 7, block # 72)[/b]
ORA-01110: data file 7: '/u01/app/oracle/oradata/test/test.dbf'

It says that the block is corrupted.Now, here is what I can do to get the data right now:

#get the object id
SQL> r
1* select OBJECT_NAME, DATA_OBJECT_ID from dba_objects where owner='SCOTT' and object_name='TEST_BAD_BLOCK'

OBJECT_NAME DATA_OBJECT_ID
-------------------- --------------
TEST_BAD_BLOCK 10327
[code]....

Here are my questions:

1. How can I know, that if there is valid data(the block is not corrupted) after this bad block?
2. How can I get the data after this bad block?

View 21 Replies


ADVERTISEMENT

SQL & PL/SQL :: Select The Valid Data From VARCHAR2 Data

Sep 23, 2011

I need the output like...

OUTPUT(MM/DD/YYYY): 2/03/2011
02/03/2011

create table test (trans_date varchar2(1000));

Insert into test values ('2/03/2011')
Insert into test values ('02/03/2011')
Insert into test values ('12/33/2011')
Insert into test values ('xxx')
Insert into test values ('33/33/2011')
Insert into test values ('03/03/11')

View 4 Replies View Related

Data Guard :: How To Skip Delete On Logical Standby

Oct 11, 2013

On next month i will have plan to delete history data on my Primary database, but i still want to keep them in logical standby as the data warehouse. So, how can i skip delete statement from primary database apply to logical standby.

Oracle SQL apply only support for Skip DML but not for keep delete statement

(on 10g and 11g)SQL>exec dbms_logstdby.skip(statement => 'DELETE',schema_name => 'SCOTT', object_name => 'DEPT');BEGIN dbms_logstdby.skip(statement => 'DELETE',schema_name => 'SCOTT', object_name => 'DEPT'); END;  *ERROR at line 1:ORA-06550: line 1, column 7:PLS-00306: wrong number or types of arguments in call to 'SKIP'ORA-06550: line 1, column 7:PL/SQL:

View 15 Replies View Related

Server Utilities :: Skip Column While Loading Data Using SQL Loader

Feb 5, 2006

Data

Sl#Emp_noNameAddress
00101Tom1/B-XYZ street
00202Jon1/C-XYZ Street

Employee Datafile
001, 01, Tom, 1/B-XYZ street
002,02,Jon, 1/C-XYZ Street

Above is a sample data file. Now I would like to import the data into an Oracle table called employee using Oracle 9i SQL Loader utility. But the table has only 3 fields (Emp_no,Name & Address), so I would like to skip Sl# while loading data. I do not want to manually modify data file. How should I write .ctl file.

Sample .ctl file.

load data
INFILE 'dataEmployee'
BADFILE 'Employee.bad'
DISCARDFILE 'Employee.dis'
into table Employee
fields terminated by ','
TRAILING NULLCOLS
(Emp_no NULLIF Emp_no = BLANKS,
Name NULLIF Name = BLANKS,
Address NULLIF Address = BLANKS
)

View 4 Replies View Related

SQL & PL/SQL :: Insert Data Into Valid Table When Validation Fulfilled?

Feb 19, 2013

I have a requirement like to validate the data in PL/SQL script dynamically.

I have 4 tables

TEST_TBL: Data available in this table
TEST_VALID_TBL: Contains field names of TEST_TBL and condition
VALID: Need to insert valid records
INVALID: Need to insert invalid records

I have to insert data into valid table when validation are full filled otherwise it should be insert invalid table .

Validation are based on TEST_VALID_TBL

While checking the data validations FIELD_NAME should be passed dynamically,because i have four columns in TEST_TBL but at present I am validating only 3 columns in feature it may be add more columns to validate.

View 4 Replies View Related

Forms :: Multi Data Block And Checkbox Field Based On Control Block

Nov 19, 2010

i have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field

e.g

item11 item21 item31 chkbox1
item12 item22 item32 chkbox2

Scenario like this :

My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field

when i checked chkbox1 , my cursor goes to item31...not item32

View 12 Replies View Related

Forms :: 6i - One Data Block Based On Table / Fetching Data

Nov 3, 2011

I am working on forms 6i. I have one data block based on table EMP.

Datablock-A: Empno, Ename, Sal

I have FIND Screen: Empno, Ename, FIND Button.

I can search the data through FIND Screen and populate data in Datablock A.

I am using below logic to search data through FIND Screen.

When-Button-Pressed(FIND Button): calling 'EXECUTE_QUERY'.

In Pre-query trigger of DatablockA:

copy(:FIND.EMPNO,'BLOCKA.EMPNO');
copy(:FIND.ENAME,'BLOCKA.ENAME');

It's working fine. But below case is failing.

Let us say, if i enter empno 10 (which is not there in database) in FIND Screen --> press FIND Button, it's showing up 'QUERY CAUSED NO RECORDS', Till this point it's working fine;. But after this, if i press CTR+F11 in block A, it's not pulling records. only this case it's not pulling records.

But if i enter something else in FIND Screen, if it returns any data, then if i press CTR+F11,it's pulling all records.

why it's failing to pull records if i try to query data in first case only.

View 2 Replies View Related

Loader Skip Blank Line?

Mar 21, 2012

I am running Oracle 9 and using sql loader to import text file into table. Can sql loader skips the record which blank line or carriage return? Do I need to set up with options?

View 1 Replies View Related

Enabling Oracle OEM Job To Skip Errors And To Run Continuously

Sep 26, 2010

I have scheduled a ADHOC Oracle JOB in OEM to gather statistics for the database with 100% estimation during weekend. This JOB currently fails because of a batch Job that runs at the same time and drops few old partitions. This OEM job runs for 1 hour and fails because few partitions are getting dropped at the same time.

How i can enable this statistics JOB to skip/Ignore errors and to continue to run to gather complete statistics of the entire database. I have scheduled this job in OEM. how can i enable this SKIP/IGNORE errors for this job.

View 1 Replies View Related

SQL & PL/SQL :: How To Skip Some Records In Open Dynamic Cursor

Jan 3, 2011

After opening a dynamic cursor, usually fetch hit record into some variables. However, if I do not want to "FETCH INTO " operate Just only skip this record.

DECLARE
TYPE weak_cur_type IS REF CURSOR;
weak_cur_1 weak_cur_type;
weak_cur_2 weak_cur_type;
vs_dsql VARCHAR2(2048);
vd_create_time DATE;
vn_count NUMBER(8);
vn_total_amount NUMBER(13);

[Code]...

View 7 Replies View Related

Server Utilities :: Skip One Table While Import

May 12, 2010

How to skip one table while import in traditional exp/imp not in DP.

View 4 Replies View Related

Forms :: How To Skip Trigger Only When Button Is Pressed

Mar 9, 2013

in my form I have a button that updates a table and I have a pre-update trigger.

is it possible to skip the pre-update trigger only when the button is pressed.

View 2 Replies View Related

Reports & Discoverer :: Skip The Repeating Frame?

Feb 23, 2011

I have a Requirement in the Report.

During the Report Runtime the Each Page Contain 55 Lines. Their as Lot of Item Groups are available in the Report Each Items Group have Maximum 10 Lines in the Report with Summary Contain in the Repeating Frame

My Requirement is During the Report Preview if the Report Not Fitted in the Page then Whole Item Group is Require to Skip to Next Page.

View 1 Replies View Related

How To Insert A New Record In A Data Block

Sep 18, 2012

How do I insert a new record in a Data Block, and the standard is always insert a line below, you could add a record above?

View 1 Replies View Related

Forms :: Populate Data Block

Aug 14, 2013

i have created on query block , upon pressing the button there is a wehre clause in the block which will filter the records based on all alike items from two tables but my problem is , in one table there is no information of struct and in other there is struct information or data, what i want is even if i pass a parameter in the where clause all the records should be filtered.

CREATE TABLE OT_ACTUAL_ACT (ITEM_CODE VARCHAR2(30),ITEM_NAME VARCHAR2(30),WO VARCHAR2(12),STRUCT VARCHAR2(12))
INSERT INTO OT_ACTUAL_ACT(ITEM_CODE,ITEM_NAME,WO,STRUCT)
VALUES ('1001','HEA100X10','300',null);

[Code]...

I am getting this

ITEM_CODEITEM_NAMEWOSTRUCT
1002 HEA100X2230030010

i want all the records

ITEM_CODEITEM_NAMEWOSTRUCT
1002 HEA100X2230030010
1003 HEA100 300
1001 HEA100X10 300

View 1 Replies View Related

Forms :: 11g - Create Data Block?

Jul 6, 2010

I m using Forms 11g. when I m trying to create Data Block & Select table Name then I received the following error.

View 5 Replies View Related

Forms :: Data Block Using Query?

Mar 1, 2010

I wanna to load data in a data block using sql which using multiple table, views based on condition

View 7 Replies View Related

Two Database - How To Change Indexing Of Primary Key To Skip Some Numbers

May 3, 2011

I have two databases called

a) Old Retailer database
b) New Retailer Database

We cant delete old database but we are using and we want to use both of them.

Now problem

Both dbs have a table called retailer.

I want to prevent duplication in them.

I.e it there is a retailer 12 created in old one new one shldnt allowo us to create retailer

id---12

similarly if there is a retailer 13 in new one it shouldnt be created in old table

retailer ids are unique. its not the case that with retailer id for example 5 there is a

different retailer in these two tables of diffferent DBS. Its unique

Now I have to put this criteria

how can i do it?

Second questions===================

if a primary key is auto increment. how can i skip some numbers lets say it is 1,2,3,4

i want that when next record shld be inserted it shld be 6 not 5. i dont want to feed the value 6. i want to know how to change indexing of primary key to skip some numbers when its set to auto increments.

View 3 Replies View Related

Server Utilities :: Sql Loader Skip Blank Line?

Mar 20, 2012

I am running Oracle 9 and using sql loader to import text file into table. Can sql loader skips the record which blank line or carriage return? Do I need to set up with options?

View 1 Replies View Related

Server Utilities :: Skip Last Line In External Table

Jun 15, 2012

how to skip last record while loading external table.

the example below I don't want to load the 000000005 in the AAA external table.

My Data is

100001***04252012***06:02:40***CignaGlobalHealthBenefits
201441424_7076551_OLC_1234567899.aaa
201441424_7075703_OLC_3456789134.aaa
201442669_7075775_RIE_5432167891.aaa
700223567_7077646_ECS_2345678912.aaa
700331352_7078197_RIE_5678901234.aaa
000000005

[Code]...

View 4 Replies View Related

Server Utilities :: Skip Records On Condition SQL Loader

May 4, 2012

I tried to find about sql loader, tried googling and ended up with Orafaq .

1) Is there a way that you can skip few records in the control file.

Assume the control file is loading a file with three records.

CREATE TABLE emp_tab
(
Emp_id NUMBER(15,0),
Name CHAR(25),
Age NUMBER(15,0)
);

The text file is like this name.txt

1;sam;19;
2;jai;22;
;pam;33;

LOAD DATA
INFILE 'C:
ame.txt'
BADFILE 'C:
ame.bad'
DISCARDFILE 'C:
ame.dsc'
APPEND
INTO emp_tab
fields terminated by ";"
TRAILING NULLCOLS
( Emp_id ,
name,
age)

I want to skip the record 3 in the text file as it has no id, is there a way to do this.Can we skip a record based on a condition?

2) What needs to be included in the control file in order to get a return code?

3) Assume the return code = 0 for success and return code = 1 for failure, what will be the return code if 60 out 100 records are loaded and 40 are discarded and written to .bad file?

4) SQL loader does a auto commit, meaning the moment you run the control file, the records are inserted and commited, is there a way to avoid it ?

View 3 Replies View Related

Server Utilities :: How To Skip Last N No Of Records In Sql Loader Load

Aug 3, 2011

I have data file emp.dat in that i have 10000 records. My requirement is i want to skip last 100 records when i am loading it into EMP table using SQL *LODER.

View 6 Replies View Related

RMAN :: Optimization - Ensure Skip Those Files Already Taken Backup

Feb 28, 2013

I would like to know that if I enable backup Optimization on then incremental full backup skip any files which was earlier backup? Because we may know that backup Optimization on ensure skip those files which are already taken backup.

Database : oracle 10g 10.2.0.3 .

View 4 Replies View Related

Server Utilities :: Skip Empty Tables While Export?

Sep 22, 2011

How can we skip empty tables when we are doing export.

View 7 Replies View Related

SQL & PL/SQL :: Connect Tables Using Valid From / To

Dec 13, 2011

I would like to join 2 tables which are both using valid_from valid_to.

CREATE TABLE TEST_HIST1
(
ID INTEGER,
DESC VARCHAR2(7 BYTE),
VALID_FROM DATE,
VALID_TO DATE

[code]...

The end result I would like to see is:

ID | Name | Descr | Valid_from | Valid_to
1 | Name1 | Descr1 | 2001/01/01 | 2001/12/31
1 | Name2 | Descr2 | 2002/01/01 | 2002/12/31
1 | Name2 | Descr3 | 2002/06/01 | 2002/12/31
1 | Name3 | Descr3 | 2003/01/01 | 2003/12/31
1 | Name3 | Descr4 | 2004/01/01 | 3000/12/31

View 10 Replies View Related

SQL & PL/SQL :: ORA-01843 / Not A Valid Month

Jul 25, 2011

I have a date field with varchar datatype. My data look like :

ID Amend_DATE
1 4/24/1974
1 4/18/2001
1 8/2/1971
2 8/4/1965
3 11/9/2001
4 8/15/2001

I need to find the minimum amend_date for each ID.When i calculate min(amend_date), it gives me error:

ORA-01843 : not a valid month

Query i am using is :

select min(to_date(amend_date ,'mm/dd/yyyy') ) from t group by id;

My data on application appears as 24 April 1974.

View 2 Replies View Related

ORA-01843 / Not A Valid Month

Sep 14, 2012

I have 2 cursors. Cursor 1 selects a number and 2 dates (values in the Table are in the format mm/dd/yyyy for both the columns).

I pass these values to Cursor 2.

v_promotion_id promotion.promotion_id%TYPE;
v_promotion_start_date promotion.start_date%TYPE;
v_promotion_end_date promotion.end_date%TYPE;
CURSOR c_promotion_list IS
(SELECT DISTINCT p.promotion_id,
p.start_date - 5,
p.end_date + 5
FROM promotion p,

[code]....

I am getting this error: ORA-01843: not a valid month

Initially I was passing just the variables without To_date. But I got the same error.

View 2 Replies View Related

Forms :: Populate Record In Data Block

Jul 14, 2010

My Requirements:

1. i want to create form for student's attendance.

2. for this purpose i create a form . and create 2 Data Blocks
1. Non Database Data Block (layout type: form)
2. Database Data Block (layout type: Tabular)

3. in Non Database Data Block i add 3 Items.. on the basis of i get the records from database and show them into my into my 2nd Data Block...

4. my problem is this that how can i get records from database and show them into my forms and then save thats records into my table by using Database Datablock.

View 2 Replies View Related

Forms :: To Move Data Block Detail

May 4, 2011

i made a master-deatil data block and when i completed all field on master-data block i can`t move to data block detail.

when i press (TAB OR ENTER) on last field, the cursor go back to first field.

View 3 Replies View Related

Forms :: Use Cursor To Get Data From DB To Control Block

Feb 7, 2010

i want to use cursor to get data from db to "control block "(db item =no ) this data had where clause depend on item on other block

this my code :

declare
cursor get_sol is
select SOL_STEP,PROB_ID
from MI_SOLUTION
where PROB_ID=:MI_FORM_PROB.PROB_ID;
begin
go_block('control');
[code]......

when am using when_validate_item trigger error raise :

FRM-40737:Illegal restricted procedure next_record in when_validate_item

that's the trigger ? or how to solve ? in case of execute query in what trigger i will write the same code to get data in case of execute query by user .

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved