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'
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')
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:
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 )
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.
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
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
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.
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?
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.
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);
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.
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.
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.
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?
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 ?
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.
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.
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.
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 .
