Tag Info

Hot answers tagged

4

This will delete anything three years older than January 1st of this year. DELETE table_name WHERE change_date < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -36);


4

You can generate a customized index table, for example, that has a row for all year&week pairs that the date range (a certain vacation etc) encompasses. Then you can join dateranges by going through that index table. It will be large, but does avoid large scans as you can just list all vacations that have any weeks that are in common with another ...


3

http://www.oracle.com/technetwork/database/features/oci/ic-faq-094177.html#A8434 Can LDAP be used with Instant Client? Yes, LDAP can be used with Instant Client, as of 10.1.0.4.


2

Overlapping date ranges can be found thus WHERE FromDate <= QToDate AND ToDate >= QFromDate You will get one row returned for each partially or fully overlapping range. For example, if you had separate pay rates for morning, afternoon and evening shifts and someone worked all three, there would be three rows returned. The same basic pattern ...


2

Solved this with help from http://stackoverflow.com/questions/3664903/how-can-i-return-multiple-identical-rows-based-on-a-quantity-field-in-the-row-it The resulting answer with the original table looks like this: with qtys as ( select 3 as qty from dual union all select 10 as qty from dual union all select 9 as qty from dual union all select 7 as ...


2

You can use extract() to get the actual year and compare those: DELETE FROM table_name WHERE extract(year from change_date) <= extract(year from current_date) - 5; If today is 2015 this will delete anything that is in 2010 or older. Replace the - 5 with the "X" you want to use. Note that extract(year from change_date) will not make use of an index ...


1

The backup operation will run on the database server, and the backup pieces may be created on the database server, attached storage disk, a backup server through a 3rd party library (Networker, TSM, etc.), or any other server through network shares (NFS, samba), depending on your configuration.


1

The problem with a FromDate / ToDate format is that the query generates an index scan. For example, a staff member takes vacation and returns two weeks later. ID FromDate ToDate 15 2011-01-03 2011-01-17 Imagine many staff members, some with 20 or 30 years of such work history, maybe taking only a day or two at a time several times each year. ...


1

Technically, you could, but it is incomplete, so better repeat the export after performing the necessary actions to avoid ORA-01555 errors (have an appropriately sized undo tablespace, set undo_retention and retention guarantee).


1

The session(s) using the temporary table can be queried as: select sid from v$lock where type = 'TO' and id1 = (select object_id from dba_objects where object_name = '&temp_table_name'); Then you can kill these sessions or have them release the locks gracefully.


1

You should not install more than one Oracle Client (per architecture) on one machine. For sure they do not work in parallel. By heart I know several issues: You would have to change PATH settings in order to determine which Client you want to use. TNS_ADMIN environment variable is used by both versions and must be set accordingly. Oracle OleDB provider ...


1

I don't know a 'legal' way to find out the tables visible in the ALL_TABLES view of another user, so I do some reengineering of the SYS.ALL_TABLES dictionary view and found the following expression in the where clause: and (o.owner# = userenv('SCHEMAID') or o.obj# in (select oa.obj# from sys.objauth$ oa where grantee# in ...


1

Try this... select owner,table_name,grantable from dba_tab_privs where grantee = 'SCOTT' and privilege = 'SELECT'; Change SCOTT to the user you are interested in.


1

Are you using Enterprise Edition? It looks like this is a EE feature: http://docs.oracle.com/cd/B28359_01/license.111/b28287/editions.htm CTRL + F the docs and search for result cache and supported editions.


1

The only difference between the two queries is that the 2nd will always return 1 row. Even if the table is empty or just has 0 rows with reference=X, the result will be 1 row with null in the min(date) column. The first query will return no row in this case. The and rownum=1 is useless and does not affect anything for the specific query. Test at SQLfiddle. ...


1

You can use a hierarchical query to generate a sequence of dates, something like this: insert into my_table (my_date_column) select dt + (level - 1) from (select date '<your start date>' dt from dual) connect by dt + (level - 1) <= date '<your end date plus 1 day>'; Date arithmetics: dt + (level - 1) -- are smart enough to know when one ...



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