Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

MICROSOFTDTPROPERTIES table in Oracle Database

Highlighted
Jonathon Druce
Honored Contributor

MICROSOFTDTPROPERTIES table in Oracle Database

SD 4.5 SP16
I am trying to do a backup of a SD database from oracle 9i using the -internal switch and am getting an error during the backup. It seems to be failing when it hits a database table called MICROSOFTDTPROPERTIES which I understand is is used by Oracle Data transaction service. The backup errors at this point and skips the "Create table" process for any other tables so I miss all the rep_ tables being created. This then creates thousands of errors during the restore.

Should this table be in the database space for Service Desk (ps I am normally using SQL not oracle so I am unsure of the Oracle terms)? Can it be deleted or moved?
4 REPLIES
Mihai Tita
Occasional Visitor

Re: MICROSOFTDTPROPERTIES table in Oracle Database

Hi,

Try to do a full backup by using Oracle tools like rman (recovery manager) or export.

I think that you got an error because the table contains a column which is of type long.

Initially this table isn't in the database space for Service Desk.
For example, this table is created when the users use Microsoft Access with a Oracle ODBC driver to access the database, or the users connect with the database using MS Visio, etc.

Related to MICROSOFTDTPROPERTIES table Microsoft says:

"In Oracle there is a MICROSOFTDTPROPERTIES table for each schema (for example, user name) in the same way that there is one DTPROPERTIES table for each database in Microsoft SQL Server. Since database diagrams do not support cross-schema tables (in other words, you cannot put a Joe.Table1 on a diagram in Mary's schema), there is no way to accomplish a centralized store of diagrams for the whole Oracle database. This is a fundamental difference between SQL Server and Oracle. An Oracle server has one database with multiple schemas, whereas a SQL Server server has multiple databases, each of which can have objects owned by different users.

When using the Data Tools to Oracle besides creating a table called MICROSOFTDTPROPERTIES, two procedures also get created:
DT_DROPUSEROBJECTBYID
DT_SETPROPERTYBYID
And, a function called DT_ADDUSEROBJECT is created."

Regards,
Mihai
Jonathon Druce
Honored Contributor

Re: MICROSOFTDTPROPERTIES table in Oracle Database

Thanks for that. The reason that I want to use the -internal backup is because I want to move the database to a SQL machine for testing - you suggested options won't allow me to do that as I understand. You are right that the data type in that table is not recognised and that is what breaks the export but the question is will I break the database if I get the DBA to move or remove that table from the SD tablespace? If it is alright to do that then the SD backup should work.
Mihai Tita
Occasional Visitor

Re: MICROSOFTDTPROPERTIES table in Oracle Database

Hi,

The table can be deleted or moved if you don't need it.

But you can
1. do a full backup of Oracle DB
2. delete or move the table MICROSOFTDTPROPERTIES from Oracle database
3. perform a backup of SD database using the -internal switch
4. restore Oracle DB from 1.

Regards,
Mihai
Jonathon Druce
Honored Contributor

Re: MICROSOFTDTPROPERTIES table in Oracle Database

Thanks for that I'll try that.
//Add this to "OnDomLoad" event