Finally home from Chicago

Morning

Morning

Morning

The review queues need some attention, folks.

morning

TLC applied to queue

Any oracles here?

TSQL JOIN Types Poster (Version 4) (Steve Stedman's Blog)

@hot2use I used to be one back in the late Jurassic. @JackDouglas is way more up to date than I am.

There is some chance that I could help.

Ok.

I'm trying to retrieve any hidden values from a column in a table.

If I run a simple SELECT * FROM <table_name> then it returns a value.

If I dump the table with expdp and try to import the schema where the table is located, then I get an ORA-39126:

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PREPARE_DATA_IMP [176]
TABLE_DATA:"SCHEMA"."TABLE_NAME"
ORA-01403: no data found

I guess there could be a hidden value (UNICODE, ASCII 1-10 character, ...) in the data.

Hmmm, I just did a SELECT TID, LEGNTHB(TID) FROM TABLE_NAME and it seems to match. (815, 3)

@hot2use exp is a binary stream format that is amenable to editing with tools like sed if you mind your p's and q's. You could first try searching for that (although it sounds unlikely). On older versions it didn't like being restored into a different schema name from its source, so one could use sed to edit that and then restore. I have no idea whether this is still an issue with Oracle, but your error could be a similar type of issue.

Bear in mind that the last time I did this was more than 15 years ago so the details are a bit fuzzy and it may be out of date.

Cheers

I'll keep on poking my stick around in the dark room until I find the cobra.

Hopefully, I'll be able to clobber the cobra before it bites me

@hot2use that shouldn't make any difference to expdp/impdp, as @Concerned said it's a binary dump format.

it looks like you are dealing with corrupt data or a bug. Have you tried exporting the table with another tool (eg exp)?

@JackDouglas Values are what they seem to be after verifying the data.

Strange thins is: the error is variable. At first it was the table TMP11. Then it was TMP12. Currently at TMP14.

Does anybody recall which tablespace IMPDP uses? Was it SYSAUX or SYSTEM? Possible space issue?

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PREPARE_DATA_IMP [176]
TABLE_DATA:"SCHEMA"."TMP21"
ORA-01403: no data found

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 116
ORA-06512: at "SYS.KUPW$WORKER", line 12105
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 116
ORA-06512: at "SYS.KUPW$WORKER", line 31150

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
00007FF735E58C38     32239  package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION
00007FF735E58C38     12119  package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR

bbc.com/news/world-europe-46133262

> "We live in a time when you can change your name and change your gender. Why can't I decide my own age?"

> "When I'm on Tinder and it says I'm 69, I don't get an answer. When I'm 49, with the face I have, I will be in a luxurious position."

Perception is reality o_O

Good morning

@PaulWhite yah, must have been muscle memory. I don't know what I was thinking after I look at the question for the fourth time.

I really have no idea how it happened but some old legacy code is inserting null characters in my sqlserver table

Why would someone need to store invisible characters inside an nvarchar datatype

@A_V that might be a serialized object that contains multiple strings copied directly from memory or something weird.

We run a lot of coldfusion code, probably some deprecated database driver inserting poorly

CockroachDB: How We Built a Cost-Based SQL Optimizer

I don't know anything about "CockroachDB," but this is a really interesting article about cost-based optimization. I thought you all might enjoy it. Especially @PaulWhite and @Forrest.

@jadarnel27 Thanks! Will read.

@Forrest no problem!

Oh interesting, they use a Memo structure too.

Yeah. The way they talk about it, it sounds like the Memo is a standard data structure used by database engines, rather than something specific to SQL Server.

Which I didn't realize 🤷‍♂️

@jadarnel27 Sounds a bit like SQL Server ahem

Hm, apparently the author of that blog post worked at Microsoft for 16 years (up until 2012), including working on Azure SQL Database.

Interesting observation, @hot2use ^_^

I would love to find an explanation of Oracle's optimizer somewhere. I wonder how it works.

@Forrest Take a look at Tom Kyte's blog. You might find some useful stuff there.

Or Jonathan Lewis: jonathanlewis.wordpress.com

@JackDouglas Running a fiddle and it's taking too long. I'm a little afraid I might have broken something, could you please take a look?

Does anyone think this is a useful question? It's closed, but has lots of views. No answers, and comments from the OP are essentially as useful as "have you rebooted?" In my opinion it should either be reopened so someone can add an answer or deleted to avoid wasting visitors time on a page with no answer.

I suppose the real problem is the two upvotes it has mean it's never going away

Net one upvote, now.

Perhaps the upvoters considered the problem in general to be useful to have here and have an answer to. But the OP is unlikely to ever submit details requested in the comments, and I'm not an expert so I'm not sure if absence of those details doesn't make the question too broad.

@AndriyM I agree. 7 years later, I don't expect details from the OP

@ypercubeᵀᴹ I'm primarily judging by this comment:

Whoever encounters the same problem won't be editing this question anyway but submit their own.

The only potential is if an expert found it and if they found the question answerable with at least some guidance. Those are two big ifs.

Of course, in order for the expert to answer it, the question would need to be reopened. So if we don't delete it, then we decide that it has potential, but then we should reopen it. Otherwise, it should go. That's how I see it at the moment.

mee too

added to Drop Question Queue

Removed from the queue

@AndriyM sure, which database?

@JackDouglas SQL Server 2017, although it seems to be working again.

oh good :)

cross join, eh, @AndriyM :-D

@MaxVernon Something less obvious (for me) this time, I'm afraid :)

@AndriyM I was just teasing

I was trying something to do with @A_V's question and my guess it might have been caused by a null character in the output or something.

This and this appear to be duplicates -> I haven't voted to close as dupe because of my Thor's hammer, which I'm reluctant to use in this case. Eerily similar, but different users?

@AndriyM I've stopped trying myself, we will simply try to sanitize the database input if it happens again. For now we just fixed the weird data using the "finder" query and it worked well.

I didn't use the "removeNullCharacters" function I created, after seeing your example it kind of scared me to run this on a production database table with millions of records

I'm hoping this doesn't turn into a mess for the OP.

> There is nothing like getting free advice from the internet only to find you've lost everything

LOL, well said @MaxVernon!

@jadarnel27 it takes ballz to reboot your instance under duress like that. I've done it, and know how painful it can be. Fingers crossed he doesn't have millions of VLFs in the log file.

now, we wait with bated breath to see what happens. 50/50 we never see the OP again.

> do I need to contact the virtually unobtainable DBAs?

on this

@MaxVernon oops

@TomV lol, nice one