User table and their corresponding numbers [message #601528] |
Thu, 21 November 2013 18:13 |
|
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 #601531 is a reply to message #601528] |
Thu, 21 November 2013 19:20 |
|
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 #601641 is a reply to message #601596] |
Sat, 23 November 2013 11:27 |
|
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
|
|
|