22

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

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


11

Object types in the same namespace as a table are: Stand-alone procedures Stand-alone stored functions Packages User-defined types Sequences Views Private Synonyms Materialized Views Therefore it is probably one of those types. If you can select from it then it rules out the first five leaving it to be either a table, view, private synonym or materialized ...


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

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


9

From AskTom (in 9i, there is a new "index skip scan" -- search for that there to read about that. It makes the index (a,b) OR (b,a) useful in both of the above cases sometimes!) So, the order of columns in your index depends on HOW YOUR QUERIES are written. You want to be able to use the index for as many queries as you can (so as to cut down on the ...


8

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


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

If you decide to use DBCA to delete the database, you can do this: If you want to completely remove an Oracle 10.2g instance from an Oracle home directory you first need to identify the instance in the oratab file. For example, this entry shows that the testdb Oracle database instance is associated with the following ORACLE_HOME: testdb:/u01/app/oracle/...


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

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


7

If all these columns are from the same table, you can use something like this: SELECT 'Authorized' AS StatusCol, authTime AS lastEventTimeCol, authUser AS lastEventUserCol FROM test WHERE testStatus = 'A' UNION ALL SELECT 'Completed', cmplTime, cmplUser FROM test ...


7

Even in Oracle (and in fact in the SQL standard), CASE is an expression that returns a single value. It is not used for control of flow like it is in some other languages. Therefore, it can't be used to conditionally decide among multiple columns or other operations. I'd say put the longer version of the code (that already works) in a view, and don't worry ...


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

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/


7

Unless I am missing something, your query would be something like this: select created, count(*) CreatedCount from yourtable group by created order by created; See SQL Fiddle with Demo Or if you have a time associated with the date, you can use TRUNC: select trunc(created), count(*) CreatedCount from yourtable group by trunc(created) order by trunc(...


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

The green bug you're seeing (look closely) means the package have been compiled with DEBUG option. It is also seen on bare procedures and functions. It is a coincidence they don't expand - maybe a bug (duh) in SQLdeveloper :) On mine, these expand without problem.


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

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


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

v$sql and v$sqlarea are views into the SQL area in the library cache (which is part of the shared pool, in the SGA). What you see in those views are parsed/"compiled" statements that are in the cache. When a new statement comes in, Oracle checks to see if it already has it in that cache. If it does, it re-uses that (called a soft parse). (See SQL Sharing ...


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

No that is not possible, only session accessing the GTT can see it. IF you wish, you can use a physical table and model it as if it s a GTT. It isn't difficult, but isn't very efficient either.


6

What you described is not normal. As already mentioned in comments, in Oracle databases, empty/zero-length strings are treated as NULL. https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm#i59110 Note: Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true ...


6

For the simple case you are right and there is no need to have the separate link table for a one<->many relationship. Off the top of my head I can only think of a couple of examples where it might be a requirement to have a separate table for the relationship: If you are tracking the history of the rows (in your business logic layer, via triggers, or "...


5

Some options: Persist the sorted version of your data to a table via trigger, and use it. Use Oracle Locale Builder to build a custom sort order. (Caveat: I have never used this, so I do not know what gotchas may exist there.) You could then use the NLSSORT function with that custom sort order.


5

It looks like the OP was attempting to solve the problem using a recursive subquery. This won't work in 10g because that functionality wasn't added until 11.2, but in 11.2+ the following would also be a valid solution to the problem. WITH T3(Years) AS ( SELECT 2008 Years FROM dual UNION ALL SELECT Years + 1 FROM T3 WHERE Years < 2011 ) ...


5

John has pointed you at the Flashback Query options useful from a developers point of view. If I read you correctly then you are probably looking for the Flashback Database and Restore Point functionality documented here which allows you to do precisely this. Flashback Database does have some requirements over and above the flashback query functionality (for ...


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