How block splitting will happen in oracle block.
Suppose, I am having Oracle BLOCK_SIZE of 16k and my Linux OS level BLOCK SIZE is 4k. then How 16k oracle block will store in OS level? and
What will be the internal block splitting process?
Aim: Architecture change in existing application
Domain: Health Care
Background: There are 2 application ( Front end: one in oracle forms - deals with accounts module and another in some legacy application - deals with patient, clinical and diagnose module) using and sharing the same Oracle 9i database.
Patient related modules are moved into another database ( java as a front end, oracle 10g as backend ) which is normalized - eliminating duplicate tables and column, also its tables and columns names are not matching with existing (patient)system.
Now the requirement is making the existing application related only to Accounts module ( having complicated business logic written in packages ) to work as it is without changing the code, design drastically.
Questions:
1. Now how best this task can be completed without affecting existing Accounts system drastically ( with minimal changes )?
2. what are the possible best approach to achieve this ?
3. what are the best way for communicating the 2 DB in this scenario ( may be creating synonym, views etc ) ?
4. What are challenges that needs to be addressed ?
I have a table Product as;
desc product
Name Null Type
--------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER
INGREDIENT VARCHAR2(20)
The data in Ingredient is separated by ','.
PRODUCT_ID INGREDIENT
---------------------- --------------------
1 A,B,C
2 A,D
3 E,F
I need to write a sql statement which will retrieve a pair of product and ingredient in each row as;
PRODUCT_ID INGREDIENT
---------------------- --------------------
1 A
1 B
1 B
2 A
2 D
3 E
3 F
write this sql ?
I was wondering if there is an Oracle function available to split a string based on a delimiter character. For example, if I have a table consisting of:
HOST
-----
emerald.test.com
ruby.test.com
diamond.test.com
I would like to only return ('emerald', 'ruby', 'diamond') by getting all data leading up to the first '.' character.
I need to split the given string into muliple sub strings based on one special character
Ex : Speace is Special character
with data as (
select 'ab cd ef gh ' from dual )
select * from data
Required Output :
ab
cd
ef
gh
I need to dump the contents of a very large table into text files for archiving as we retire this old DB. The table has about 16 million rows, and a few of the columns are up to 4000 characters wide (varchar2(40000)). I've got 2 problems:
1) How can I select records that occur in a certain month of a year (there is a date column) and put the selected records into a file?
2) I don't have access to the server OS, so UTL_FILE is not possible. The output is also so large that I'm having trouble with the DBMS_OUTPUT.PUT_LINE.
I'm trying to get the first block of the IF working first, so the rest is just placeholders.
DECLARE
v_mm number (2);
v_yyyy number (4);
min_mm number (2);
min_yyyy number (4);
max_mm number (2);
max_yyyy number (4);
min_date date;
[code]....
Table Name : Trans
chitta_enn number(10,0)
varavu_patti varchar2(100)
pattru_patti varchar2(100)
Thogai number(10,2)
where in the data's are as follows
chitta_enn varavu_patti pattru_patti Thogai
101 panam null 101.00
101 null sambalam 51.00
101 null kamishan 50.00
I need to create the view as follows
View Name : Pattiyal
vivaram varchar2(2000)
varavu number(10,2)
pattru number(10,2)
The view data should get display as follows
vivaram varavu pattru
sambalam kamishan null 101.00
panam kamishan 51.00 null
panam sambalam 50.00 null
Logic:
Each table row will have only one value either in varavu_patti or in pattru_patti. On selecting the row, thogai must be posted in varavu when varavu_patti is not null or should be posted in pattru when pattru_patti is not posted.on selecting the table row, vivaram should contain all other rows varavu_patti and pattru_patti on equating chitta_enn
Is it possible to create a view as above
I have string like 'PRASAD,ALLEN,STEWART,SMITH'.
LIKE
COL1 COL2 COL3 COL4
-------------------------------
PRASAD ALLEN STEWART SMITH
I want to store the data into columns using SELECT statement only
11.2.0.3...just trying to learn the syntax. I have not worked with IOTs and I am exploring a feature I have not really used to try to learn something new. I know about intervals.This exact split syntax below, works on a heap table without errors. When I run the following split against a regular heap table it works.
CREATE TABLE MYTABLE (
INSERT_DATE date,
myfield1 varchar2(50),
myfield2 varchar2(50),
myfield3 varchar2(50),
[code]....
alter table MYTABLE split partition "FUTURE" at ( to_date('09_MAY_2013_13','DD_MON_YYYY_HH24' ) ) into ( partition "B4_09_MAY_2013_13", partition "FUTURE" ) update global indexes
*
ERROR at line 1: ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
I have a table which has a column that stored concatenated data.
Sample test case is as below:
SQL> create table tst (
2 col1 varchar2(20));
SQL> insert into tst values ('one,two,three');
1 row created.
SQL> commit;
Commit complete.
Is there any way i could write a sql to split the text of this column into rows? Sample output im expecting is as below
col1
-------
one
two
three
I need to join ISSUED_REMOVED Table with ITL Table. having each quantity each row.
Eg. If a unit Serial no '354879019900009' has a part (1015268) issued 8 times and then unissued 4 times so finally the part was issued 4 times. so I need 4 rows to show for each qty 1 for that part and unit serial number.
-- ITL Table
Create table ITL_TEST (
ITEM_SERIAL_NO, ITEM_BCN, ITEM_ID, ITEM_PART_NO, OPER_ID,
ISSUED_REMOVED_PARTNO, ISSUED_REMOVED_QUANTITY, QUANTITY, SHIPMENT_ID)
[code]....
-- Issued Removed table
create table ISSUED_REMOVED_ITEM
(REPAIRED_ITEM_ID, ISSUED_REMOVED_ITEM_ID, ISSUED_PART_ID, OPER_ID, ISSUED_REMOVED_QUANTITY)
as select
122013187, 1323938, 1015268, 308, 2 from dual union all select
122013187, 1323939, 1015269, 308, 2 from dual union all select
122013187, 1323940, 1015268, 308, 2 from dual union all select
[code]....
-- The way I need to join the Issued_Removed Table
select * from ITL_TEST ITL
left join
issued_removed_item iri
on iri.REPAIRED_ITEM_ID = ITL.ITEM_ID --ITL.ITEM_ID --rlsn2.item_id --126357561
and iri.oper_id = 308 --in ( 308, 309)
[code]....
I see that I can use SDO_LRS.SPLIT_GEOM_SEGMENT to split a line at a single point (and get 2 resulting lines).
However, how I could split a line, at multiple points, into multiple segments? I need to do this for many rows, therefore a function or procedure would be good if any exists.
I need to split a column into multiple columns. The data in my column is separated by a Comma (,). But the data is dynamic and I could have any number of data separated by (,).
Quote:FOR Ex:
If COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION, ITEM_DATA_TYPE
RESULT: should be 4 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION and ITEM_DATA_TYPE
Is COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION
RESULT:
should be 3 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION
splitting a table partition without making its primary key index ar any other indexes unusable.
I think it is possible to do so 10g onwards.
DB Details:
Oracle RDBMS 11.2.0.3, HP-Ux B.11.31, OLTP
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
i have a master detail form, In Master block we have one field cheque amount and in Detail block we have field receiveable amount invoice wise. if company paid us a cheque amount we will enter this amount in Master block field Cheque amount and in detail block there will be invoice wise receivable amounts. i want to distribute the cheque amount in detail block invoice wise for example
Cheque amount in master block = 291
Invoice wise receiveable amount is as follows
Invoice No , receivable amount , Received amount
10, 196 , 0
20 , 95 , 0
30 , 54 , 0
Result should be as follows:
Invoice No , receivable amount , Received amount
10, 196, 196
20 , 95 , 95
30 , 54, 0
Received amount field should be distributed according to the receivable amount when recevied amount = cheque amount then remaining will be 0.
i have an error with block corruption
Error: Corrupt block relative dba: 0x01c12a58 (file 7, block 76376)
What all the ,methods I can go for if we are working on a production environment with out any down time.
I can go for dbms repair package and restore and recover
I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.
I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".
I need to add a try and throw an exception in an oracle package. Im refreshing a mv from local to a remote database. I need to do the following.
try thrice for 15 minutes and after that throw exception while refreshing the mv.
I have created Master Detail form of Sales offer. I would like to enter item detail under Sales offer detail.
Example :
Sales Offer Header -> Sales Offer Detail -> Sales Offer Detail Detail.
I have tried to make a form in form 6i to fulfill above mention scenario. But when two or multiple users tried to enter data from this form all users entered into deadlock or hang stage.
I have two block....both are the control block.. in first block i select the date and in second block the data of that date is populated.but the data is populated using cursor in when-button-pressed trigger of that first block button.in cursor the data is selected and placed in field of detail block using into clause.... each field and one item of detail block is srno which is create in post-query of detail block using :sysyem.trigger_record.
Now i want after populated the detail block the data is sorted desc one of the field of the detail block..Can this possible using set_block_property() of block although the block is control block if yes where i should do this?
Oracle 10gR2 on red hat.
I have an application that writes blobs via pro*c. It is running at several sites without errors for a few years now. One of the newer sites has started reporting "ora-01578 ORACLE data block corrupted".
I've tried using rman backup validate - it does not report any errors.
I've tried using rman restore and recover to correct the problem.
I've tried deleting the file with the corrupt block and using rman restore and recover to recreate it.
In any of the above cases, I do not see any errors being encountered, and when I restart the application, it works for a while and then starts reporting "ora-01578 ORACLE data block corrupted".
I've had the client do a physical check of the disk and it indicated the media was okay.
We've failed over to the standby system, so I have a wee bit of breathing room - but I'm going to be away for 4 days and will have to get back on this when I get back. I'm a little puzzled that I can fix it for a short while and then it breaks again.
I have created a data Bolck Manually.I had used select query to fetch records into the data block. There's and add button in the form to add more records to this block.
Now When i press add button and enter new records i want to ensure the Primary key i.e. the the new record entered does not repeat across the block. I cannot loop through and check as the number of records fetched is quite huge.
we are running oracle 9iR2 ( 9.2.0.1.0) on windows server 2003 R2 and facing ORA-01578: ORACLE data block corrupted (file # 17, block # 312316)
SQL> select * from payrol.slip;
select * from payrol.slip
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 17, block # 312316)
ORA-01110: data file 17: 'E:ORACLEORADATAMTMASYSMTM.ORA'
ORA-26040: Data block was loaded using the NOLOGGING option
1) SELECT name FROM v$datafile WHERE file#=17;
output
E:ORACLEORADATAMTMASYSMTM.ORA
2) SELECT block_size FROM dba_tablespaces
WHERE tablespace_name =
(SELECT tablespace_name FROM dba_data_files WHERE file_id=17);
output
8192
3) SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = &AFN
and &BL between block_id AND block_id + blocks - 1;
file_id=17, BL=312316
output
SYSMTM TABLE PAYROL SLIP
I am not using RMAN. How to recover payrol.slip table ?
I am having 3 oracle database instances running on 3 seperate Linux Node(RHEL Node).
Instance -1 named - DS
Instance -2 named - MIS
Instance -3 named - OAS
Among of these 3 nodes, we are facing Block Corruption issues with sysaux tablespace.Error in Instance name DS is
Errors in file /u01/home/dba/oracle/diag/rdbms/ds4db/DS/trace/DS_j000_655388.trc (incident=300847):
ORA-01578: ORACLE data block corrupted (file # 2, block # 38428)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
GATHER_STATS_JOB encountered errors. Check the trace file.
[code]....
for this I googled and found some solution as oracle doc [430230.1] related to sysaux couruption.After this again we are facing the same issue in sysaux tablespace.
What i have to do for this error:
ORA-01578: ORACLE data block corrupted (file # 3, block # 42348)
ORA-01110: data file 3: '+DGMETA/metadb/datafile/sysaux.257.783871811'
Wed Nov 21 09:30:00 2012
Corrupt Block Found
TSN = 2, TSNAME = SYSAUX
RFN = 3, BLK = 42348, RDBA = 12625260
OBJN = 50439, OBJD = 50439, OBJECT = MGMT_STRING_METRIC_HISTORY_PK, SUB
OBJECT = SEGMENT OWNER = SYSMAN, SEGMENT TYPE = Index Segment
Database version is :- Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
the following proble.The emp table is having 14 records.
SELECT * FROM emp ORDER BY empno;
EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
[code]...
The emp table is having 10 records.
SELECT * FROM emp_10 ORDER BY empno;
EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
7566JONES200020
[code]...
I have written the following PL/SQL block logic tofetch the records from the emp table and compare the records with emp_10 table to perform insert if the records are newelse to perform update the existed records in the emp_10 table.
DECLARE
CURSOR tranche_balance_cur
IS
SELECT empno,
ename,
sal,
[code]...
Execution scenario 1:
I have commented insert and update statements in that case I got the following out put.
Inserted Records4
Updated Records10
As per the logic it's giving the correct output because the cursor is fetching 14 records in that already 10 records are existed in emp_10 tableand 4 are new records.so that it's showing the count for inserted records as 10 and updated records as 4.
Execution scenario 2:
I have uncommented insert and update statements in that case I got the following out put.
Inserted Records13
Updated Records1
As per the logic it's not giving the correct output.
I tried with using TRIM function in the comparision logic to avoid spaces.
TRIM(emp_10.empno) = TRIM(tranche_balance_rec.empno)
AND TRIM(emp_10.ename) = TRIM(tranche_balance_rec.ename)
AND TRIM(emp_10.sal) = TRIM(tranche_balance_rec.sal)
AND TRIM(emp_10.deptno) = TRIM(tranche_balance_rec.deptno)
We are facing block corruption error and it's refer to system datafile (SYSTEM01.DBF).Below is the script through it, we can come to know about the extent.
select segment_name, segment_type from dba_extents
where file_id=1 and 134144 between block_id and block_id+blocks-1;
select owner,index_name,index_type, table_name from dba_indexes
where index_name='I_CDEF3';
How to resolve the problem as it is related to system datafile? We tried to drop the index but system is not allowing to do.
ORA-00701 - object necessary for warmstarting database cannot be altered.
The block size of my db(10gr2) is 8k, and the db is on the raw device file system on a aix machine.
OS block size is 512k.
How much block will be read from disk when a single oracle io call occurs? Which one is currect, 8K or 512K?
If the block size for a single io call depends on the os block size (512k), I think os block size need to be tuned for oracle block size (8k). If we use raw device file system, os level block size has no meaning?
how can i corrupt a block to mock up BMR (block media recovery) workaround , I want to corrupt some block of Oracle seeded table "emp" within scott schema.
View 1 Replies View Related