Hot answers tagged

18

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 ...


16

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 ...


15

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 ...


14

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


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 ...


10

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 - ...


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

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 ...


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 ...


9

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 ...


9

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 ...


8

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 ...


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 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

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 ...


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

Welcome to the world of Database Administration... and good luck. You're going to want to read up on as much Oracle documentation as you can, as well as other good technical sources (O'Reilly has always been good), and subscribe to lots of Oracle blogs. I'll answer your questions here, but you're really going to want to get a solid foundation in RMAN ( For ...


7

I suspect you want to put the whole thing in an anonymous PL/SQL block and run that, i.e. BEGIN INSERT INTO E_PRODUCT VALUES ('PCD2', 'PC Dual Core', 499, 22, 475, 'PC', NULL); INSERT INTO E_PRODUCT VALUES ('PCL4', 'Laptop PC', 599, 9, 225, 'PC', NULL); INSERT INTO E_PRODUCT VALUES ('PCQ5', 'PC Quad Core', 699, 25, 41, 'PC', NULL); INSERT INTO ...


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

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' ...


7

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).


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

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 ...


6

You need to use escaped single quotes in your EXECUTE IMMEDIATE statement, like so: EXECUTE IMMEDIATE 'INSERT INTO ET_SEVERITY_LEVEL(ID_SEVERITY_LEVEL, CODE, DESCRIPTION) VALUES (1, ''DEBUG'', ''Debugging'')';


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 ...


6

Are there other processes operating on the same tables? Indexes being rebuilt? If so, then you could be hitting the situation that Jonathan Lewis describes here: http://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/


6

You have the basics right. There is only one type of commit (no normal, fast...). from the concepts doc: When a transaction commits, the following actions occur: A system change number (SCN) is generated for the COMMIT. The internal transaction table for the associated undo tablespace records that the transaction has committed. The ...



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