Data issue in a Oracle Table

25 pts.
Tags:
Oracle Tables
Oracle Tablespaces
I have a 180 GB size table. When I select the table,it returns some columns with null data. But if I Select that particular record showing NULL column with a Primary key filter,it is showing data for that column. Please let me know what might be the problem with that table.
ASKED: October 8, 2009  8:55 AM
UPDATED: October 9, 2009  5:36 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

NULLS are sort of tricky. There is some value in the column. Every byte must contain something; something between x’00′ and x’FF’. What makes it NULL to the RDBMS is the NULL Indicator that is appeneded to the data column.

When working with NULLs, you must always keep several things in mind.
> in the WHERE clause always use proper syntax to check the NULL condition – e.g. WHERE col_a IS NULL. Do not make the mistake of saying WHERE col_a = NULL.
> after the row is in your application, always test the NULL Indicator before accessing the column itself.
> PL/SQL syntax for checking for NULL is different than in other code such as COBOL.
> the NVL function can help relieve some of the problems when dealing with NULLs

Hope this helps.

—————— kccrosser
Have you checked the database to determine that there are no corrupt blocks?
While that does not happen often, it is possible for the database to have corrupt blocks – either in the data table or in the index tables. If the index is corrupt, it is possible that a query using an index would falsely return or fail to return records.

You didn’t specify an Oracle version, and there are some different tools and techniques for detecting and repairing problems based on the version. If you Google the following, you will get numerous useful hits:
Oracle repair corrupt block <version>
e.g.
Oracle repair corrupt block 10g

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Could you please provide more details ? What tool are you using to query the table ? is it SQL*Plus ? Sql Developer ? other ? What database version are you working on ? What is the data type of the column ? Can you post your queries ? Thanks,
    65,110 pointsBadges:
    report
  • Ctnaveenkumar
    Tool used : I tried with SQL plus and TOAD DB version :Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi Datatype of the column in Number I have an SCD type 2 table RDM_ACTIVITY_FACT in oracle database which is having both Partition and Sub partition. There are 132 columns in this table. I have created a new SCD type 1 table RDM_ACTIVITY_FACT_MIG with the same partition and sub partition and this table contains 99 columns. Now I am migrating data from SCD type 2 to SCD type 1 table partition wise. For e g: If SCD type 2 table has partition JAN_2003 then I am migrating as below Insert into RDM_ACTIVITY_FACT_MIG Partition (JAN_2003) Select Columns from RDM_ACTIVITY_FACT Partition (JAN_2003) where CURR_RECRD_FLG=’Y’ I am giving the filter CURR_RECRD_FLG=’Y’ in the Source so that only latest Record is inserted into SCD type 1 fact. While migrating like this, I am facing issue like some data is getting missed or wrongly fetched. The same record if I fetch by the table Primary Key then I am not facing the issue. COLUMN NAME PRIMARY_KY PRIRTY_NO CLUST_ID_SK DATA IN SOURCE 1 NULL -99 DATA IN TARGET 2 102 NULL I have given a scenerio above how my data looks in Source tabel and the target table. PRIMARY_KY --- Primary key column PRIRTY_NO and CLUST_ID_SK are 2 columns PRIRTY_NO is NULL data in source but while migrating, some value 102 is inserted into target table. Similarly CLUST_ID_SK is -99 in source but it inserts as a NULL in target. When i insert these 2 records with the help of Primary key, then correct value is getting migrated
    25 pointsBadges:
    report
  • carlosdl
    Thanks for the details. Regarding to this:
    COLUMN NAME 	PRIMARY_KY 	PRIRTY_NO 	CLUST_ID_SK
    		
    DATA IN SOURCE 		1 	NULL 		-99
    
    DATA IN TARGET 		2 	102 		NULL
    If the primary key is not the same, then you are comparing two different records.
    65,110 pointsBadges:
    report
  • Ctnaveenkumar
    Sorry Carlosdl. The data should look like this. COLUMN NAME PRIMARY_KY PRIRTY_NO CLUST_ID_SK DATA IN SOURCE 1 NULL -99 DATA IN TARGET 1 102 NULL
    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following