Home » SQL & PL/SQL » SQL & PL/SQL » User table and their corresponding numbers
User table and their corresponding numbers [message #601528] Thu, 21 November 2013 18:13 Go to next message
anncao
Messages: 87
Registered: August 2013
Member
Hi, We have a vendor product.

The application gives a unique table number for the tables in the database.
when we do import from user interface we can see the numbers right next to the table name.

I know some of them, but would like to generate from database a full list of the table name and its corresponding table number. But I searched all the tables in the database, and I cannot find where is the data stored.

I also did a select * from all_objects, there is a object_id column , but by comparing the data, it is not the table number we have.

Where else could I find the data possible? by the way the product is Powerschool.
I also searched in the data dictionary of their document, and I cannot find that either.

Thanks

[Updated on: Thu, 21 November 2013 18:15]

Report message to a moderator

Re: User table and their corresponding numbers [message #601530 is a reply to message #601528] Thu, 21 November 2013 19:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I suspect that the desired data resides in application table because by default Oracle does not have a "unique table number".
Re: User table and their corresponding numbers [message #601531 is a reply to message #601528] Thu, 21 November 2013 19:20 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Basically, you have a table (specific to the application) mapping a unique number to a table name. This mapping table, is itself, also a normal database table which I think should be found in all_tables.

Obviously, if you knew the owner (which is a column in all_tables) of that mapping table, that is, the technical database user of your application kernel, you could limit the scope of your search.

And I would also look for a table having a few number of columns (a mapping table)

Another way of searching, if both column names are known, could be by looking inside all_tab_columns
Re: User table and their corresponding numbers [message #601596 is a reply to message #601531] Fri, 22 November 2013 11:04 Go to previous messageGo to next message
anncao
Messages: 87
Registered: August 2013
Member
Yes, I think it should be a user table too .

Actually I did find one table named TableNumberMap. In the documentation of their database dictionary, it said this table contains legacy table names and corresponding number.
There is only one record in it. I guess it may be replaced by some other new tables, but just can find it anywhere in the database.
Re: User table and their corresponding numbers [message #601641 is a reply to message #601596] Sat, 23 November 2013 11:27 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I suppose there is always this, if you can see it. But it is unreliable in that the numbers will as far as I know anyway change for export/import and drop/create etc.

Utility tools maybe? But I would never use this kind of object number for anything approaching a production piece of code.

This also relies on Oracle's definition of what an object is and this as we know changes when new technology comes along. For example, is a PARTITION and object?

SQL> desc sys.obj$
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 OBJ#                                      NOT NULL NUMBER
 DATAOBJ#                                           NUMBER
 OWNER#                                    NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(30)
 NAMESPACE                                 NOT NULL NUMBER
 SUBNAME                                            VARCHAR2(30)
 TYPE#                                     NOT NULL NUMBER
 CTIME                                     NOT NULL DATE
 MTIME                                     NOT NULL DATE
 STIME                                     NOT NULL DATE
 STATUS                                    NOT NULL NUMBER
 REMOTEOWNER                                        VARCHAR2(30)
 LINKNAME                                           VARCHAR2(128)
 FLAGS                                              NUMBER
 OID$                                               RAW(16)
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             NUMBER
 SPARE4                                             VARCHAR2(1000)
 SPARE5                                             VARCHAR2(1000)
 SPARE6                                             DATE

SQL> select obj#,owner#,name from sys.obj$ where rownum < 10;

      OBJ#     OWNER# NAME
---------- ---------- ------------------------------
     34042          0 /1000323d_DelegateInvocationHa
     44844          0 /1000e8d1_LinkedHashMapValueIt
     23397          0 /1005bd30_LnkdConstant
     19737          0 /10076b23_OraCustomDatumClosur
     45460          0 /100c1606_StandardMidiFileRead
     46289          0 /10128284_OpenMBeanAttributeIn
     32968          0 /101419a4_NormalDataCollector
     28794          0 /1020ed5e_Param
     15377          0 /1023e902_OraCharsetUTFE

9 rows selected.

SQL>



Kevin

[Updated on: Sat, 23 November 2013 11:29]

Report message to a moderator

Previous Topic: dump the result of SELECT query in ARRAY
Next Topic: multiple select from one table
Goto Forum:
  


Current Time: Fri Mar 29 00:04:40 CDT 2024