67 questions
4
votes
3
answers
88
views
Insert with (CTE and) select ... as of scn (flashback) and table alias(es) does not work in PL/SQL - ORA-00984: column not allowed here
In a legacy system we want to copy a lot of productive data/tables in other tables in a stored procedure.
That all copied data is consistent with each other, we want to use the <tableName> as of ...
0
votes
0
answers
74
views
How to Exclude Specific Columns in Oracle APEX Interactive Grid Flashback?
I am working with Oracle APEX and using an Interactive Grid with Flashback functionality enabled. One of the columns in my grid is a query-only column (it is calculated using an SQL query and does not ...
2
votes
1
answer
234
views
In Oracle 18c+, Is it possible to use Table Flashback with a reference partitioned table?
From the documentation, it's not very clear to me whether flashback is supported for reference partitioned tables:
Restrictions on Flashing Back Tables
This statement is subject to the following ...
- 3,306
0
votes
1
answer
76
views
How can I retrieve the value of an attribute supported by Oracle?
Tablespace FlashBack
CREATE TABLESPACE TS_Physical
DATAFILE
'\physical_df.dbf'
SIZE 1024000
AUTOEXTEND OFF
BLOCKSIZE 8192
LOGGING
FORCE LOGGING
...
user13746660
0
votes
2
answers
236
views
What is going on underneath FDA queries?
Let's say I want to pull data for the TEST_TABLE table for some date. I create a query with FDA syntax:
select * from TEST_TABLE as of timestamp (timestamp 2021.05.05 15:00:15);
I want to check how ...
1
vote
1
answer
921
views
How do I properly use Oracle Flashback to audit table changes with user information?
My organization has chosen to use Oracle Flashback to replace manually created audit tables. However, the DBA set the TUNED_AUTORETENTION values and UNDO_RETENTION to only 14400 seconds. ...
- 35
0
votes
1
answer
254
views
Using ORACLE Flashback with QueryDSL
We are using QueryDSL to bulk read data from an Oracle database with our SpringBoot application.
final QFps fps = QFps.fps;
final SQLQuery<Tuple> query = queryFactory.select(fps.fpId, fps.fieldA,...
- 835
3
votes
2
answers
1k
views
Cannot drop Oracle flashback data archive
Wanted to do a clean test on Flashback Data Archive so had to remove the existing.
I disabled FDA on all enabled tables and dropped FDA.
But still i see the record in DBA_FLASHBACK_ARCHIVE And ...
- 61
2
votes
0
answers
971
views
Maximum number of days SCN retains, to query archived data
I have a table which is flashback enabled. I want to query the data using AS OF clause as following:
SELECT *
FROM employees AS OF TIMESTAMP TO_TIMESTAMP('2019-09-11 00:00:00', 'YYYY-MM-DD HH24:MI:...
- 377
9
votes
3
answers
734
views
Tune Oracle Database for faster startup (flashback)
I'm using Oracle Database 11.2.
I have a scenario where I issue FLASHBACK DATABASE quite often.
It seems that a FLASHBACK DATABASE cycle does a reboot of the database instance which takes approx. 7 ...
- 16.1k
3
votes
2
answers
991
views
FLASHBACK_TRANSACTION_QUERY View in Oracle 12c returning no rows
I am learning Flashback Transaction Query in Oracle 12c.
I executed some DDL and DML statements.
At the end, I executed a select statement on the FLASHBACK_TRANSACTION_QUERY view in order to see the ...
- 31
2
votes
1
answer
113
views
how to know the previous length of a column after altering that
I have altered the size of a column in Oracle , and now i want to get the prev size of that column, so is there anyway we can get the prev size of that column.
Thanks.
- 199
1
vote
1
answer
717
views
Do enabling flashback slows down updates/inserts in oracle?
I have two doubts around oracle flashback queries:
Is the flashback feature enabled by default in Oracle? Because in a few of my tables I see null values in versions_startscn and others version ...
- 2,064
0
votes
1
answer
1k
views
Oracle Undo tablespace after commit and record stored after undo_retention is crossed?
When I commit a transaction in oracle, it looks like my undo segment is released. Where does my old values go after commit?
What happens to undo tablespace after we commit a transaction?
And when ...
- 403
1
vote
0
answers
643
views
Disable Oracle recycle bin for specific users
To keep aligned with organizational policies and developmental framework and also keep the Database storage & space management clean, I want to disable Oracle Recycle Bin for non-DBA users.
Is ...
- 147
4
votes
1
answer
14k
views
Postgresql equivalent of flashback [duplicate]
I'm Oracle Dba and want to learn Postgresql. Does it have equivalent of flashback in Postgresql?
- 55
0
votes
1
answer
63
views
Recover existing object code in greenplum database
One of my procedure in greenplum database is replaced by a wrong code.I want the old and correct version of the procedure. Is it possible in greenplum database to recover the code?
Is there any ...
- 11
1
vote
0
answers
83
views
From where is my Java code executing?
I've got a generic Java problem baked into a specific roadblock.
I am attempting to set up Flashback on my Win10 box, but am hitting this stack trace on the command ./startAdminServer.sh -port 1234 --...
- 1,696
1
vote
0
answers
770
views
How to use a flashback query to get deleted rows?
I am trying to test a stored procedure which is deleting data from a table and have a setup similar to this:
Oracle Setup:
CREATE TABLE table_name ( id NUMBER );
PL/SQL Script:
DECLARE
v_scn V$...
- 173k
0
votes
1
answer
167
views
Rewind a database table in Oracle and return to the current state in real time
We are working on a new release of our product and we want to implement a feature where a user can view older data for a disease prediction made by the application. For example, the user would have an ...
- 21
0
votes
1
answer
306
views
Flashback, backup, checkpoint, redo logs
I was searching over the internet to find good explanations of flashback, backup and checkpoint, but I find it hard to understand the difference.
Both flashback and backup can revert database to the ...
- 1
0
votes
1
answer
41
views
How to recover a database that has been deleted by installing a theme plugin for UI?
I am currently building a database in my class in SQL PLSQL.
Using Apex Oracle, I made a huge mistake,installed a new apex theme plugin and set it as my current theme to enhance the UI. This plugin ...
- 1
0
votes
0
answers
274
views
Flashback Oracle Database - Shutdown abort
If I killed a long transaction that is rolling back millions of deleted records, is there any risk to use SHUTDOWN ABORT on the DB before issuing a flashback command on it? Since the Database needs ...
- 495
0
votes
1
answer
899
views
Records Deleted. Commit executed. Can we have that records back from anywhere?
Recovered Records Should be up to date as when they were deleted.
2
votes
1
answer
503
views
Oracle: how to flash back a specific column?
How do I flash back a specific column for all rows in a table?
For example, given this table:
select * from t as of scn 1201789714628;
a b
- -
x 1
y 2
z 3
select * from t;
a b
- -
x 4
y 5
z ...
- 306k