I'am using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit with the following:
db_flashback_retention_target 1440
undo_management AUTO
undo_retention 900
Is it possible to enable flashback only to some specific tables?
For example, we are only interested in the capability of flashback tables scott.emp and scott.dept, no more.
I fired following commands .
>create table t1(id number);
>create index indx_t1 on t1(id);
>drop table t1;
>flashback table t1 to before drop;
but when i fired
>select index_name , table_name from dba_indexes where table_name='T1';
it shows index name something like "BIN$QvTWVG3dTxmBBJ607f9CWQ==$0" so is their any way to recover that index name
I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :
AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;
I get an error, where is my syntax error ?
i want to know if it's possible to do it without trigger ?
I need a query to find list all tables in a schema which does not have 'ADDRESS', 'CITY', 'STATE' columns.
View 8 Replies View RelatedI need to exclude several tables with specific prefixes, all the tables where names do not starts with 'A_' or 'B_' or 'C_'I tried this:
EXCLUDE=TABLE:"NOT LIKE 'A!_%' ESCAPE '!' and NOT LIKE 'B!_%' ESCAPE '!' and NOT LIKE 'C!_%' ESCAPE '!'"
I have this errors:
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression
ORA-06512: at "SYS.KUPM$MCP", line 3174
I have table A.Security:
CouponIssue_nameQuantity_typeCurrencySec_TypeSec_alias
1 a 56 USD AB 676
2 b 45 INR AB 143
3 c 32 JPY BA 200
and Table B.Security:
CouponIssue_nameQuantity_typeCurrencySec_TypeSec_alias
1 a 33 USD OB 676
9 b 21 NZD AC 143
8 c 19 GBP CA 200
build a Stored Proc which compares sepecific fields in the 2 tables and reports into a new table as:
A.Security.sec_aliasB.Security.sec_aliastable fieldA-valueB-value Compare
676 676 Security Currency USD USD Match
143 143 Security Currency INR NZD Not-Match
Fields to be compared are:
Quantity_type
Currency
Sec_type
The join between the tables A and B can be made on Sec_alias.Also, this is just a single pair of tables. Further I need to compare 5 more pairs of tables after this and report out of the new table.
I have a database which consists of various orders and various field.
I have a variable called createddatetime . I want that whenever i should run the database it should display records from
Yesterday 06:00:00 am to Current Date 05:59:59 am
Now to implement this i tried to put this syntax
and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'
But nothing comes up
where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.
I have used FLASHBACK TABLE option in my oracle 10g database.
It works successfully but the indexes got system generated name after flashback table option.
how i get all the indexes with same name which was before flashback.
Can I use flashback in application, especially DBMS_FLASHBACK.TRANSACTION_BACKOUT. I import some information (from files) in my database and after that I want to revert that transaction(delete imported data). I did some test I took xid of the transaction from v$transaction and then executed
DBMS_FLASHBACK.TRANSACTION_BACKOUT
with that XID for parameter. Everything is fine but that time there was only one transaction, when there are many transaction how can I get the exact XID. I can use dbms_transaction.local_transaction_id to get information for transaction which is running. And when i tried to use flashback that way a deadlock occur and db killed the other transaction.
I've just found out that 12cR1 will not (in all likelihood) allow "flashback database" for pluggable DBs. Am I the only one disappointed by that ? I use flashback db (+replay) a lot to revert and replay automated tests and I had plans to consolidate tenths of test environments into PDBs.
View 3 Replies View RelatedI heard about flaskback data archive. can i get some short example to understand in practical way
View 11 Replies View Relatedone of my user POM create one table using this statement
12:55:59 SQL> create table hello_world (emp_id number(4), emp_name varchar2(30))
;
Table created.
then insert some raw into this hello_world table
12:56:41 SQL> insert into hello_world values (1, 'Bill Gates');
1 row created.
12:57:51 SQL> insert into hello_world values (2, 'Steve Jobs');
1 row created.
13:00:32 SQL> insert into hello_world values (3, 'Chris Eaton');
1 row created.
13:01:19 SQL> commit;
Commit complete.
then i have drop this hello_world table
13:02:08 SQL> drop table hello_world;
Table dropped.;
then i have to connect with sys user
13:03:03 SQL> conn sys as sysdba
Enter password:
13:08:21 SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
13-MAY-11 01.10.21.691000 PM +05:30
[code].....
i flashback my this HELLO_WORLD table but why i cannot Flashback RAW of this table .what can i do if i want to flashback raw of this table which query i have to run,
Since tablespaces can be enabled/disbaled for flashback is there a query that can tell me the flashback status of each tablespace in my DB.
View 2 Replies View RelatedI need store history for two tables in my system. I thought that Flashback Data Archive will be the best option. There is also another ways to do this but don't focus on this. I need to to this by FDA (Flashback Data Archive);
So my prerequisite was to create tablespace and flash back archive, and alter table to be archived.
create tablespace audit_archive datafile 'd:oradata etaaudit_archive.ora' size 100M;
create flashback archive audit_flash_archive
tablespace audit_archive quota 10G retention 10 year;
alter table teta_admin.t_prac flashback archive audit_flash_archive;
and everything works fine but on sys user.
i can query this table using "as of timestamp" clause
select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-23 08:20:00','yyyy-mm-dd hh24:mi:ss')
but final construction of idea was to create additional user (interface), grant select on teta_admin.t_prac object and query archive data from interface user. and this is point of my failure. this don't work on new user.
interface user have such sys privs:
SQL> SELECT * FROM dba_sys_privs
2 WHERE grantee = 'INTERFACE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
INTERFACE CREATE SESSION NO
and table privs:
SQL> SELECT * FROM dba_tab_privs
2 WHERE grantee = 'INTERFACE';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN INSERT
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN DELETE
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN ALTER
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN FLASHBACK
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN SELECT
what i need to do in order to query this flashback table from interface user. when i try to do this from this user oracle says ORA-00942.
what happen in standby when enable flashback in the primary database?
View 1 Replies View RelatedHow to delete flashback log and to release the space?
I guess that it maybe like archive log ,it can release the space using RMAN.But when i try a test ,it fail.
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE FLASHBACK OFF;
here's my settings
SYS@boston>SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
ESTIMATED_FLASHBACK_SIZE
------------------------
45195264
SYS@boston>select name, value, issys_modifiable from v$parameter where name='db
_recovery_file_dest_size';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
ISSYS_MOD
---------
db_recovery_file_dest_size
9663676416
IMMEDIATE
SYS@boston>select * from v$recovery_file_dest;
NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
D:Ora102chicagooston_recover
9663676416 1671241216 1103842304 50
SYS@boston>select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG .04 0 1
ARCHIVELOG .16 .01 21
BACKUPPIECE 16.84 11.41 22
IMAGECOPY 0 0 0
FLASHBACKLOG .25 0 6
6 rows selected.
SYS@boston>
I have configured a flash recovery area of 9g yet the system keeps complaining that i have not enough flashback logs?
SPACE_RECLAIMABLE 1103842304
ESTIMATED_FLASHBACK_SIZE 45195264
the space_reclaimable is more than ESTIMATED_FLASHBACK_SIZE, so how is it possible the system always complain not enough space?
[URL].....
Quote:
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
it returns me 0, I try to flashback the now defunct former primary db chicago, the system complains. so how much is really enough?
from
[URL]......
Quote:
V$FLASHBACK_DATABASE_LOG
I have read it in books that flashback uses undo data to create the flashback data or to flashback the database to a time in the past.Then, what is the role of archive files in flashback operation. Why it is mandatory to turn on archiving before turning on flashback. Also, if you remove the latest archive files, you can NOT flashback the data to a time in past (Oracle complains of missing archive files).
View 8 Replies View RelatedFlashback table operations are not supported for the SYS user . I have found the above line in one of the article.
I don't know why the SYS user can not able to flash back the table.
My Data Guard 's Enviroment:
Primary: 11.1.0.7 RAC 2 Nodes
Standby: 11.1.0.7 Single Instance
I want to implement Fast-Start Failover at my enviroment. Reading requirements, I found that Oracle Flashback is needed (I don't use Flashback on my databases).
Best Practices recommends 60 min (1 hr) for DB_FLASHBACK_RETENTION_TARGET if I just want to use Flashback to fast-start failover.
My questions:
What 's better, setup up flash recovery area on ASM or O.S. filesystem?
What is the recommended size for flash recovery area?
Is there anyway to backup Flashback Data Archive (FBDA) data and can be restore on new database. I cannot find Oracle's document or any document explain about backing up this data.
View 6 Replies View RelatedWhat is difference between the flashback database(10g later) and incomplete recover?
View 5 Replies View RelatedI delete some of the data from a table unfortunately and make commit on that on my production server.
getting back my all data of this table only of last 15 minutes.
As per Client requirement, i need to configure archive log and backups in flashback.
View 3 Replies View RelatedIf flashback is enable in physical standby database 1. If we failover at 11AM can I flash back NEW primary database to 6 AM ? 2. if I convert physically standby database to snapshot standby database at 11AM , Can I flashback snapshot standby database to 6 AM and do some works on it (DML operations) then converting the snapshot standby database into physical standby database ?
View 1 Replies View RelatedThe undo_retention is used for read consistency, to avoid snapshot too old.Flashback database is using files in the db_recovery_file_dest.But whether undo_retention has any influence on Flashback drop; Flashback table or Flashback query?Let's say we have set undo_retention = 3600 = 1 hour.And
db_flashback_retention_target = 1440 = 24 hours.
Will it work Flashback drop; Flashback table; Flashback query to get 12 hours back?Additional question. Why Oracle sets undo_retention in seconds and db_flashback_retention_target in minutes?To use the same measure would be more user-friendly to DBAs .
problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.
we have found something called table_exist_action=append in impdp.
I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
View 1 Replies View RelatedI want to insert only specific number of columns into a table by using Bulk collect and Forall.
SQL> create or replace procedure bifa_proc
2 is
3 type etab is table of emp%rowtype index by binary_integer;
4 erec etab;
5 cursor c is select * from emp;
6 begin
7 open c;
[code]...
Warning: Procedure created with compilation errors.
PLS-00382: expression is of wrong type
PLS-00436: implementation restriction: cannot reference fields of
BULK In-BIND table of records
How to insert specific number of columns without declaring multiple table type definitions for each column by using bulk collect and forall.