Tag Info

Hot answers tagged

15

There are a few different approaches depending on the details of your batch process and why you're trying to view the uncommitted changes. 1) Oracle Workspace Manager is a tool that was originally designed to allow people developing Spatial applications to have the equivalent of extremely long-running transactions (i.e. transactions that may require ...


14

I suspect they're part of a connection pool & therefore haven't idled out because they're being used frequently. INACTIVE in v$session merely means there isn't a SQL statement being executed at the exact moment you check v$session. If they're part of a connection pool they're doing their job properly by being logged in for long periods of time. The ...


13

I think this will work (based on this page ( http://psoug.org/definition/LEVEL.htm ) as a starting point): WITH counter AS ( SELECT LEVEL seq FROM DUAL CONNECT BY LEVEL <= 4 ) SELECT (2008 + seq - 1) myYear FROM counter ORDER BY 1 ; This should return: myYear ------ 2008 2009 2010 2011 Adjust 2008 and 4 to get different ...


12

At a guess I'd say Marian is right and this is caused by a unique index and constraint having the same name, eg: create table t( k1 integer, k2 integer, constraint u1 unique(k1,k2) using index(create unique index u1 on t(k1,k2)), constraint u2 unique(k2,k1) using index u1); select count(*) from user_indexes where ...


11

Inside pl/sql block: declare startdate number; begin select 20110501 into startdate from dual; end; / using a bind variable: var startdate number; begin select 20110501 into :startdate from dual; end; / PL/SQL procedure successfully completed. SQL> print startdate STARTDATE ---------- 20110501


10

30-90 minutes according to Oracle's Best Practices for Upgrading. This is about the closest estimate you will get given all the unknowns in this situation. The size of the database really matters very little in determining how long the upgrade will take. Here are the main factors effecting the duration (from the Oracle.com upgrade blog): Number of ...


9

The number of files is irrelevant from a performance perspective. The number of spindles those files are distributed over is, on the other hand, critically important to performance. If you are using a reasonably modern SAN and the additional files would be created on the same mountpoint, there will be no meaningful performance difference since the data is ...


8

What Oracle doesn't have is a read uncommitted isolation mode. In other words you will not be able to query uncommitted data in another transaction. There are ways of getting information out of a long running transaction - one not mentioned so far is autonomous transactions (which should be used with caution)


8

Yes - LogMiner can do this. In fact, if you only want committed transactions, you have to specifically filter the output! And there is TABLE_NAME in V$LOGMINER_CONTENTS, that's how you would look at a single table.


8

Similar to Kerri's answer, but without the with (and inspired by an SO answer): SELECT 2007 + LEVEL AS YEARS FROM DUAL CONNECT BY LEVEL <= 4; YEARS ---------- 2008 2009 2010 2011 Or if your aim is to get the current year the three preceding it, without hard-coding the start year: SELECT EXTRACT(YEAR FROM SYSDATE) + 1 - ...


8

Are there any other snags I should be aware of that might result in an insert, update, or deletion of a record not incrementing this value? ora_rowscn is always incremented when a row changes - but in a default configuration it can also be incremented when a row does not change If you need to check the whole table for udates, one method is to use ...


8

First, are you using "database" in the Oracle sense of the term? Or are you using it in the sense that other database vendors (such as SQL Server or MySQL) use the term? If you are using "database" in the Oracle sense, that would be the size of the SYSTEM and SYSAUX tablespaces at a minimum and would possibly include the size of the UNDO and TEMP ...


8

There is a built-in utility called Resource Manager exactly for that purpose: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dbrm.htm#i1010776


8

What is TEMP used for in Oracle? As a scratch area for doing sorts that won't fit into main memory. So the issue is that you need to do a big sort in order to create your index, and you don't have a large enough PGA for it. So your options are: a larger PGA or a "temporary temporary" tablespace; simply create one big enough, make it your user's temporary ...


8

Your problem might very well have to do with incorrect versions or wrong default connection. Make sure that you use the exp utility from the 10g installation and the imp utility from the 11g installation. Also make sure that the environment variables like ORACLE_HOME are correct and that PATH is adjusted for the correct ORACLE_HOME/bin How does your connect ...


7

The only way for a trigger on a table to prevent an INSERT operation from completing is to throw an error. It is, as you have stated, a huge hack, but you could Rename the table Create a view that has the same name as the original table Create an instead of trigger on that view that only does an INSERT on the table if you want the row to be persisted. ...


7

A trigger cannot simply stop working. A trigger can be disabled. A trigger can be dropped. A trigger will be made invalid if DDL is done to one of the objects it references but it will still be executed if the triggering statement is executed. If the trigger fails to recompile successfully, the triggering statement will get an error ORA-04098: trigger ...


7

If you script the procedures out to a file, the search/replace can be trivially dome with a sed script along the lines of s/\/file\/folder\/documents/directory_name/g (note not tested, just off the top of my head, but you can fiddle with it). Then you can re-load the stored procedures. Note that if you're frigging with the code base you should really ...


7

A 10g file can be loaded into an 11g database. It is throwing a networking error and TNSPING doesn't go all the way to the database, just as far as the listener port. I suspect you'd have the same trouble connecting via SQLPLUS. Check your tnsnames.ora and that the database and listener are available


7

edit: this was written before the question was clarified You could use flashback queries to see the table without your own uncommited data. Consider: SQL> CREATE TABLE my_table 2 AS SELECT ROWNUM ID FROM dual CONNECT BY LEVEL <= 5; Table created SQL> INSERT INTO my_table VALUES (6); 1 row inserted To see the difference between the table ...


7

You need to shrink the data files, which can be tricky. However, possibly the easier method to describe and implement is to create a new tablespace of approximately the right size, use ALTER TABLE to move the table segments, and ALTER INDEX to rebuild the indexes in the new tablespace. When the old tablespace is empty of objects you can drop it. Oracle ...


7

You can delete databases with DBCA which takes care of most of it. Or you can do as below, but this will do the same as removing the datafiles, redo logs, controlfiles manually. sqlplus / as sysdba startup mount exclusive restrict exit rman target / drop database including backups noprompt; exit After this, you still have to remove the entry that belongs ...


6

A function-based index adds a virtual column to the table (This column is then indexed). Dropping the index removes the virtual column, which leads to a cleanup that takes time (same amount of work as the removal of a non-virtual column).


6

Would it be possible to find out which line of a stored procedure is causing these row lock contentions? Not exactly but you can get the SQL statement causing the lock and in turn identify the related lines in the procedure. SELECT sid, sql_text FROM v$session s LEFT JOIN v$sql q ON q.sql_id=s.sql_id WHERE state = 'WAITING' AND wait_class != 'Idle' ...


6

This is not what triggers do -- they act on an event, such as a change to data. Use DBMS_Scheduler to set up a repeating event. Consider applying a function-based index to include only those rows that need updating, and make sure the query is constructed to be able to use it.


6

Short answer: No, you can't, and not because it's a system type. You can't anchor a freestanding type to any table's column data type. %TYPE is a PL/SQL construct. CREATE [OR REPLACE] TYPE is SQL. You can't use %TYPE in SQL. It somewhat makes sense that you can't. If you use MYTABLE.MYCOLUMN%TYPE in PL/SQL, you have anchored that PL/SQL type to the table, ...


6

Getting the max(ora_rowscn) will require a full table scan each time you do it. It may be faster just to refresh the entire cache each time. It sounds like you need a way to notify the other service that a change took place and what the change was. You could maintain a log table with a column that indicates which system needs to consume the change. The ...


6

I don't believe you can. The expression you use to create a virtual column must be deterministic, i.e. always return the same value when called with the same arguments. Involving the current date is fundamentally incompatible with that requirement. You could use a plain old view though. create table mytab (hiredate date); create view myview as select ...


6

You are receiving an Oracle ORA-03297 error because the HWM (High Water Mark) of a table is beyond the size you tried to shrink a datafile to. First try and "shrink space" for each affected table: alter table fragmentedtable enable row movement; alter table fragmentedtable shrink space; Next, check to see how much space can be freed from each of the ...


6

Are you really trying to use Windows 8? No version of Oracle is supported on Windows 8 yet and, if history is any guide, I wouldn't expect any of them to work without patches that aren't available yet. Is Windows 8 even in public beta yet? I'd hate to try to take a class using an early beta operating system. Oracle 11.2 is supported on Windows 7 (which ...



Only top voted, non community-wiki answers of a minimum length are eligible