Friday, 30 September 2016

Oracle Flashback Query and Flashback Table

FLASHBACK TABLE

Purpose
Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.
Prerequisites
To flash back a table to an earlier SCN or timestamp, you must have either the FLASHBACK object privilege on the table or the FLASHBACK ANYTABLE system privilege. In addition, you must have the SELECTINSERTDELETE, and ALTER object privileges on the table.
Row movement must be enabled for all tables in the Flashback list unless you are flashing back the table TO BEFORE DROP. That operation is called a flashback drop operation, and it uses dropped data in the recyclebin rather than undo data. Please refer to row_movement_clausefor information on enabling row movement.
To flash back a table to a restore point, you must have the SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privilege or theSELECT_CATALOG_ROLE role.
To flash back a table to before a DROP TABLE operation, you need only the privileges necessary to drop the table.
Restrictions on Flashing Back Tables This statement is subject to the following restrictions:
  • Flashback Table operations are not valid for the following type objects: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.
  • The following DDL operations change the structure of a table, so that you cannot subsequently use the TO SCN or TO TIMESTAMP clause to flash the table back to a time preceding the operation: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).

TO SCN Clause
Specify the system change number (SCN) corresponding to the point in time to which you want to return the table. The expr must evaluate to a number representing a valid SCN.
SELECT current_scn FROM v$database;
TO TIMESTAMP Clause
Specify a timestamp value corresponding to the point in time to which you want to return the table. The expr must evaluate to a valid timestamp in the past. The table will be flashed back to a time within approximately 3 seconds of the specified timestamp.
TO RESTORE POINT Clause
Specify a restore point to which you want to flash back the table. The restore point must already have been created.
FLASHBACK QUERY EXAMPLE:

SELECT COUNT(*)
FROM   TableName AS OF TIMESTAMP TO_TIMESTAMP
('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');



SELECT COUNT(*)
FROM   TableName  AS OF SCN 722452;
FLASHBACK TABLE EXAMPLE:

ALTER TABLE TableName ENABLE ROW MOVEMENT;

FLASHBACK TABLE TableName TO TIMESTAMP TO_TIMESTAMP('2004-03-03 10:00:00', 'YYYY-MM-DD HH:MI:SS');

FLASHBACK TABLE TableName TO SCN 715315;



Wednesday, 28 September 2016

Overriding Default Sorting in Informatica Lookup Transformation

Informatica adds the default order by clause for all ports present in the lookup condition. If you need to override the Order By clause, just put the SQL comment sign at the end of query. 

e.g. Select a, b, c, d from table order by b, c, a, d -- 

and ports b and c are present in lookup condition in that order, the query Informatica will generate will then be 
Select a, b, c, d from table order by b, c, a, d -- order by b, c 

Here because of your comment sign, the order by clause added by Infa will get commented out.

Wednesday, 14 September 2016

Create index with substring in SQL Server


In one of my earlier post , I have written about the multi row based constraint implementation in Oracle
http://mybicommunity.blogspot.in/2016/06/multi-row-based-constraints-in-oracle_8.html..

When  I try to implement the same in SQL Server 2008 R2 , I'm not able to achieve it as a straight forward like in oracle but it is achieveable through computed column/view .

Below Link : Holds the approach suggested by MSDN people.


https://social.msdn.microsoft.com/Forums/sqlserver/en-US/392ebaa0-2546-4abf-a1ff-6a9199be4a32/help-create-index-with-substring?forum=transactsql

Sunday, 11 September 2016

Query to Get the Count of All tables in a SQL Server Database



select 'select count(*) C,'+''''+table_name+''' from '+TABLE_SCHEMA+'.'+Table_name+ ' union all'  from INFORMATION_SCHEMA.tables 


Monday, 5 September 2016

Oracle Next Day Function

NEXT_DAY returns the date of the first weekday named by char that is later than the date date. The return type is always DATE, regardless of the datatype of date. The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date.
Examples
This example returns the date of the next Tuesday after February 2, 2001:
SELECT NEXT_DAY('02-FEB-2001','TUESDAY') "NEXT DAY"       FROM DUAL;   
 NEXT DAY  -----------  06-FEB-2001