I am interested in what methods of logging is frequent in an Oracle database. Our method is the following: We create a log table for the table to be logged. The log table contains all the columns of the original table plus some special fields including timestamp, modification type (insert, update, delete), modifier's id. A trigger on th...
Is it possible to use a CHECK constraint to prevent any date that falls on a Sunday? I don't want to use a trigger. ...
Which of the two databases is most popular in business/industry? I ask this because in terms of straight revenue or market share, I suspect they're about 50-50, because US government is big on DB2. So, discounting government, military, and academia, which database vendor has the greatest share of the business segment? ...
A fairly simple question here. Where is the location of my oracle event log (on a default Linux installation)? Many thanks in advance. ...
Hi everyone, I'm having a bit of trouble with the UTL_MAIL package in Oracle 10g, and was wondering if anyone had any solutions? I connect to my DB as SYSMAN and load the following two scripts; @C:\oracle\product\10.2.0\db_1\rdbms\admin\utlmail.sql @C:\oracle\product\10.2.0\db_1\rdbms\admin\prvtmail.plb I set up the SMTP server; AL...
The in-house application framework we use at my company makes it necessary to put every SQL query into transactions, even though if I know that none of the commands will make changes in the database. At the end of the session, before closing the connection, I commit the transaction to close it properly. I wonder if there were any particu...
I am interacting with an Oracle DB using SQL over an ODBC connection. Is there an SQL command I can use to get the MAC address of the server, or something that uniquely identifies the server hardware or software installation. This is so I can be sure (or at least fairly sure) that I'm talking to the same database all the time. ...
Ok, so it's almost as easy as pie already. But it really should be as easier than it is. I think I should be able to connect to another database just by putting a JDBC connection string into TNSNAMES. Every database vendor has a type-4 JDBC driver and there's usually a good, free alternative. With Oracle being such keen Java fans, and ...
I have two users Bob and Alice in Oracle, both created by running the following commands as sysdba from sqlplus: create user $blah identified by $password; grant resource, connect, create view to $blah; I want Bob to have complete access to Alice's schema (that is, all tables), but I'm not sure what grant to run, and whether to...
Is it possible to show other processes in progress on an Oracle database? Something like Sybases sp_who ...
Newish to Oracle programming (from Sybase and MS SQL Server). What is the "Oracle way" to avoid filling the trans log with large updates? In my specific case, I'm doing an update of potentially a very large number of rows. Here's my approach: UPDATE my_table SET a_col = null WHERE my_table_id IN (SELECT my_table_id FROM my_table WHE...
I have access to an Oracle server that has some databases that I would like to access. However, the machine that I have access from has none of the oracle client software. Is there any alternative to oracle's client software the provides the functionality of something like MySQL's mysql or Postgres' psql? I'd like to be able to poke arou...
i have a bunch of sql scripts that should upgrade the database when the java web application starts up. i tried using the ibatis scriptrunner, but it fails gloriously when defining triggers, where the ";" character does not mark an end of statement. now i have written my own version of a script runner, which basically does the job, but...
I have an Oracle table which contains event log messages for an application. We don't want this table to grow out of control and eat up too much space, so we'd like to set a cap on the number of rows that it can contain to some big number like one million. So we'd like to tell Oracle that when the table grows too large, it should delet...
how to find out mal characters(unsupported eg. Ω ) when inserting using sqlloader? or list the mal characters to avoid junk data? ...
Is it correct to say that typically user_dump_dest is on a local drive? If so, are there issues with mounting a NAS volume to both Unix and Windows and pointing user_dump_dest at that? If so, what are they? Are any issues worth not doing this in prod? ...
This is what I currently have: CREATE OR REPLACE TRIGGER MYTRIGGER AFTER INSERT ON SOMETABLE FOR EACH ROW DECLARE v_emplid varchar2(10); BEGIN SELECT personnum into v_emplid FROM PERSON WHERE PERSONID = :new.EMPLOYEEID; dbms_output.put(v_emplid); /* INSERT INTO SOMEOTHERTABLE USING v_emplid and some of the other values...
Is there an Oracle equivalent to MS SQL's full text search service? If so, has anyone implemented it and had good / bad experiences? ...
I've seen the question (and answer) when posed for MS SQL Server, though I don't yet know of one for Oracle and PL/SQL. Are there xUnit style testing frameworks for Oracle's PL/SQL? What are they? ...
How do you use enums in Oracle using SQL only? (No PSQL) In MySQL you can do: CREATE TABLE sizes ( name ENUM('small', 'medium', 'large') ); What would be a similar way to do this in Oracle? ...