problem creating table

[Arsalan]

Sir

I have problem to create partition table it give error like below,
kindly guide me


SQL> CREATE TABLE temp_sales
  2  ( ID number(25),
  3  PNAME varchar2(23),
  4  SALE_date date)
  5  PARTITION BY RANGE (SALE_date)
  6  (
  7  PARTITION sales_AUG VALUES LESS THAN (TO_DATE('01-jan-2016','dd-MON-yyyy')),
  8  PARTITION sales_SEP VALUES LESS THAN (TO_DATE('01-feb-2016','dd-MON-yyyy')),
  9  PARTITION sales_OCT VALUES LESS THAN (TO_DATE('01-OCT-2016','dd-MON-yyyy')));
CREATE TABLE temp_sales
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kqlInvObj:user], [104], [], [], [],
[], [], [], [], [], [], []

[ErmanArslansOracleBlog]

ORA-600 as you know, is an internal error.
Bad news is the function named "kqlInvObj:user" (this is where the code is failing) is not documented in Oracle Support. That's why I don't even need to ask your DB version..

However, I think this is caused by a bug and it is related with some problematic records in sys.obj$ table.
I guess the second argument "104" is the owner# which is stored in sys.obj$.

So, let's take a look..

Login with sys, execute the following and upload the result.

select * from sys.obj$ where owner#=104

[Arsalan]

Sir this is the result of your query
kindly check excel file.

result.xls

select * from sys.obj$ where owner#=104;

[ErmanArslansOracleBlog]

Actually, there is a workaround for this and it may work...
However, it is risky and it is not documented in a formal way.


That is , there is a similar impdp bug : Bug 10256218 : IMPDP FAILS WITH ORA 600[KQLINVOBJ:USER], [94] and there is a workaround written in definition of this bug:

1. SHUTDOWN IMMEDIATE or NORMAL
  2. STARTUP RESTRICT
  3. Create a new user called PATCH_USER:

  create user PATCH_USER identified by p;
   
  4. Update obj$
  update sys.obj$
  set owner# = (select user#  
               from sys.user$  
               where name = 'PATCH_USER')
  where owner# in (88,94);
 
   5. COMMIT;
  6. Shutdown abort;
  7. STARTUP
  8. Drop user created in step 3:
   
  drop user PATCH_USER cascade;


 --- Note that: This is too risky! Requires a full backup as well.

My questions are;

What are those objects?
Are the owner/schmea of these objects recreatable?
If so, please drop the schema and recreate it. (this will require downtime)
If not, you can try the following -> export the schema, drop the schema, import the schema once again..

Anyways, I suggest you to open a SR for this one.. (especially if you are working on a critical Production environment)

Update me with the outcome.

[Arsalan]

MY ANSWER

1-the database which i want to create table that is test db not production db.

2-I also drop user and create other db user but i can't create object in any user in db give me same error.

I did practical the below script , it does not give any type of error but while i want to create table it give error
1. SHUTDOWN IMMEDIATE or NORMAL
  2. STARTUP RESTRICT
  3. Create a new user called PATCH_USER:

  create user PATCH_USER identified by p;
   
  4. Update obj$
  update sys.obj$
  set owner# = (select user#  
               from sys.user$  
               where name = 'PATCH_USER')
  where owner# in (88,94);
 
   5. COMMIT;
  6. Shutdown abort;
  7. STARTUP
  8. Drop user created in step 3:
   
  drop user PATCH_USER cascade;

[ErmanArslansOracleBlog]

did you replace the numbers (with your numbers) in that script?

4 Şub 2017 13:30 tarihinde "Arsala [via Erman Arslan's Oracle Forum]" <[hidden email]> yazdı:

MY ANSWER

1-the database which i want to create table that is test db not production db.

2-I also drop user and create other db user but i can't create object in any user in db give me same error.

I did practical the below script , it does not give any type of error but while i want to create table it give error
1. SHUTDOWN IMMEDIATE or NORMAL
  2. STARTUP RESTRICT
  3. Create a new user called PATCH_USER:

  create user PATCH_USER identified by p;
   
  4. Update obj$
  update sys.obj$
  set owner# = (select user#  
               from sys.user$  
               where name = 'PATCH_USER')
  where owner# in (88,94);
 
   5. COMMIT;
  6. Shutdown abort;
  7. STARTUP
  8. Drop user created in step 3:
   
  drop user PATCH_USER cascade;

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/problem-creating-table-tp2084p2089.html

To start a new topic under Database, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

[Arsalan]

Thanks Erman Arsala.

The script which u sent me that work well and solved my problem
now i can create table in any db user.


I am very thankful that you support me every time .




Arsala khan
 

[ErmanArslansOracleBlog]

Good to know that it is solved Arsala.

My surname is "Arslan" bytheway :)