752 Actions

How to track DML and DDL daily eventsIf you want to know what data changed in the table, you'd need to enable auditing or create triggers to track the changes depending on what exactly you're looking for.

Apr24

comment

How to track DML and DDL daily eventsWhat would it mean to you to report on changes to an index? An index is defined on a table so any change to an indexed column of the table would necessitate index maintenance operations. Those operations might affect the index entries of many different rows in the table (a 50/50 split of a leaf block for example). If all_tab_modifications is giving you the information you want about tables, why do you list tables again in your list of objects you're looking for more information about?

Apr22

comment

How to exceute view without DBLink in oracle serverYou don't. I assume you are saying that you have a client application (i.e. SQL Developer) that can connect to each database. If there is no database link, you can't join data between the two short of writing client-side code that fetches all the data from each table and implements join-like logic on the client. That is generally very slow.

Apr16

comment

Multi Tenant IdentityWhy would you want to? If you're using an auto_increment, you're generating a synthetic primary key. By definition, that means that the key isn't meaningful for anything other than identifying the row. Why would you care if one tenant sees more than the expected number of gaps in the record_id for a single tenant if the record_id isn't meaningful?

SQL - Optimize table population with dynamic queriesAre you saying that it will loop 1 million times each time inserting 10,000 rows so that it inserts a total of 10 billion rows? Or are you saying that it inserts a total of 1 million rows? Is there a reason that you're looping in the first place rather than simply dynamically constructing the entire INSERT SELECT statement? Do you need the interim commits?

Stored Procedure result is not showing in Datagrid pane in Toad for Oraclep_recordset is a local variable that goes out of scope as soon as the procedure returns. If you want an object that executes a query and returns a sys_refcursor you probably want a function not a procedure. If you really want to use a procedure, you'd need to add an OUT parameter of type sys_refcursor (or types.cursor_type if that is something meaningfully different).

Improve sql server lookup table performanceWhat does the query plan look like? What does "very slow" mean (minutes? hours?)? What sort of performance are you hoping to achieve? A single-table query using a single IN list predicate with 1000 values should just do 1000 index seeks using the primary key index which should be pretty efficient (though that's relative-- if you're counting milliseconds, efficiency is relative).

How to perform same DML statements on two different servers (Oracle and SQL-Server) simultaneouslyDo you really mean simultaneously? So every transaction will incur the overhead of a two-phase commit and the system will be unusable if either database is unavailable? Generally, that's not what you want. Generally, you want to replicate data from one system to the other asynchronously which brings up the question of what sort of lag you can tolerate and whether the replication needs to be bidirectional.

Oracle: Inserting data using a database link@KellyCook - The error you are getting cannot possibly be the result of something being wrong on the client machine (assuming that it is actually the INSERT statement that throws the error). The error you're getting indicates that the database link is set up incorrectly.

Oracle: Inserting data using a database linkAre there three machines involved here? A client machine, a machine for DB1, and a machine for DB2? Do you have one machine with three Oracle Homes (one for the client, one for the DB1 database, and one for the DB2 database)? It seems unlikely that you'd do an Instant Client install on a machine that is already running the database so I'm guessing multiple machines are involved. If so, the tnsnames.ora file that needs to have DB2's connection information would need to exist on the machine (and Oracle Home) running DB1. Your client's TNS settings are irrelevant.

Feb17

comment

Oracle alter column if existsdba_tab_columns has data for every table in the database. all_tab_columns has data for whatever tables the user you're logged in as has access to. Every user has access to all_tab_columns (though each will see different data), not every user has access to dba_tab_columns. I would expect, though, that a user that is installing an application would have full access to the data dictionary.