Hot answers tagged

19

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


15

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


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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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


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

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

The error in you script is that the script now expects a table named tablename, having a column named columnname. In this case you don't know the table and column names so you should use dynamic sql to run this. Next to that, if possible, forget about LONG and implement lobs instead. For docu see http://download.oracle.com/docs/cd/B28359_01/appdev.111/...


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

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