4 ms·

I just taught one of my developers how to use a basic Oracle Flashback query to pull a historical version of a table. This is my cheat sheet: ALTER SESSION

by chasil 3y ago

I just taught one of my developers how to use a basic Oracle Flashback query to pull a historical version of a table. This is my cheat sheet: ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; select count(*) from dual AS OF TIMESTAMP TO_TIMESTAMP('2010/01/01 00:00:00'); I was aware that Postgres had "time travel," but I don't know if it used this syntax, but I am aware that the feature has been removed. I don't know if "AS OF" is still supported. In Oracle, it has been quite helpful. Edit: Flashback came to Oracle in 9i, when "rollback segments" were replaced by the "undo tablespace" that could present any table as it appeared in the past, limited by the amount of "undo" available. The "FLASHBACK ANY TABLE" privilege is required to see this history on tables that you do not own, and that also conveys the privilege to fully revert tables to their previous contents. You must be a DBA to exercise flashback on tables owned by SYS.

renegade-otter 3y ago
Oracle, with all the baggage attached to the namesake company, is a remarkable database. I was working in a dual MySQL/Oracle environment 20 years ago exactly, and MySQL - still at version 3 o 4 - was a toy DB, compared. I was writing Oracle queries that could make your head spin, with optimizer hints, for example: https://renenyffenegger.ch/notes/development/databases/Oracle/SQL/hints/index https://renenyffenegger.ch/notes/development/databases/Oracl...
- traceroute66 3y ago
  > I was writing Oracle queries that could make your head spin Absolutely, and some of the things Tom Kyte (Oracle's resident DB performance guru) could do with Oracle were spectacular. As you say, its a shame about the Oracle baggage, and in particular the steep price tag. Otherwise I'm certain it would be far more widely deployed as database.
  - chasil 3y ago
    It's either free (for XE), $17,500 per cpu (for SE2), or $47,500 per cpu (for Enterprise). x86-64 gets a 2-for-1 core discount. You can also license it by user. https://www.oracle.com/assets/technology-price-list-070617.pdf https://www.oracle.com/assets/technology-price-list-070617.p...
- _a_a_a_ 3y ago
  Using hints is typically a sign of failure. The DB optimiser should typically not need them.
  - _a_a_a_ 3y ago
    Ok downvoter, why do you think a DB that needs hints for good performance is better than one that usually doesn't? That is, why do you think it better a person does a computer's job instead of the computer?
    - mickeyp 3y ago
      You've never run into a query planner that suddenly changes its mind once your tables grow. Try using row level security in postgres - it's awfully slow and you need to be a master architect and sql developer to make it run fast because it's too dumb on its own. With query planner hints I could at least guide it. Shame pg doesn't believe in that.
      - _a_a_a_ 3y ago
        Poster said oracle not PG
        mardifoufs 3y ago
        Total db noob here: is Oracle's query planner better? What's the difference between the two?
        _a_a_a_ 3y ago
        This is not about Oracle versus postgres, it's about whether or not to use hints. My position is that needing hints indicates a failure of the database optimiser. The optimiser theoretically should do a perfect job. In practice it doesn't, and never can so sometimes hints are necessary, but IME and speaking as an MSSQL guy, I very rarely need them.
        3y ago
auspiv 3y ago
Been a feature of SQL Server since I believe SQL Server 2008 - temporal tables. They are used (incorrectly) at my place of work quite a bit. If auditing data (who changed what and when) is important - there are two choices: 1) system versioning or 2) add a "version" column or something similar which increments with each change. There are tradeoffs for both. "Temporal tables (also known as system-versioned temporal tables) are a database feature that brings built-in support for providing information about data stored in the table at any point in time, rather than only the data that is correct at the current moment in time." https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/t...
- Foobar8568 3y ago
  It was the audit features that got introduced in 2008 C2 audit (or something like this) and change tracking. From what I remember, temporal table was a 2016 feature and more or less confirmed by your link.
refset 3y ago
Expanding on the the above, > The original version of PostgreSQL from the 1980s did not remove dead tuples. The idea was that keeping all the older versions allowed applications to execute “time-travel” queries to examine the database at a particular point in time [via https://ottertune.com/blog/the-part-of-postgresql-we-hate-the-most https://ottertune.com/blog/the-part-of-postgresql-we-hate-th...] Postgres deprecated support for time-travel in ~1997 and the more general notion of "system time" wasn't standardised until SQL:2011. This blog post is a good overview on the SQL:2011 spec + adoption in databases of (bi-)temporal versioning: https://illuminatedcomputing.com/posts/2019/08/sql2011-survey/ https://illuminatedcomputing.com/posts/2019/08/sql2011-surve... Temporal versioning is less sophisticated than git-like versioning (no branching etc.) but is usually more aligned with common end-user requirements. Kent Beck suggests this framing of "eventual business consistency": https://tidyfirst.substack.com/p/eventual-business-consistency https://tidyfirst.substack.com/p/eventual-business-consisten...
- traceroute66 3y ago
  > (bi-)temporal versioning I once spent some time trying to find a way to do bi-temporal versioning in Postgres. The only thing I found was a half-dead abandonware external project and an associated presentation PDF from some conference the author once spoke at. I was unaware that they previously had some form of temporal queries and deprecated it. That is a great shame.
  - refset 3y ago
    A shame indeed, they were only a few decades too early! The support for "Time Varying Data" gets discussed briefly in this 1995 paper by Stonebraker "The Design of Postgres" > POSTQUEL allows users to save and query historical data and versions. By default, data in a relation is never deleted or updated. Conventional retrievals always access the current tuples in the relation. Historical data can be accessed by indicating the desired time when defining a tuple variable. > [...] Finally, POSTGRES provides support for versions. A version can be created from a relation or a snapshot. Updates to a version do not modify the underlying relation and updates to the underlying relation will be visible through the version unless the value has been modified in the version. https://dsf.berkeley.edu/papers/ERL-M85-95.pdf https://dsf.berkeley.edu/papers/ERL-M85-95.pdf

by chasil 3y ago

renegade-otter 3y ago

Oracle, with all the baggage attached to the namesake company, is a remarkable database. I was working in a dual MySQL/Oracle environment 20 years ago exactly, and MySQL - still at version 3 o 4 - was a toy DB, compared. I was writing Oracle queries that could make your head spin, with optimizer hints, for example: https://renenyffenegger.ch/notes/development/databases/Oracle/SQL/hints/index https://renenyffenegger.ch/notes/development/databases/Oracl...

traceroute66 3y ago
> I was writing Oracle queries that could make your head spin Absolutely, and some of the things Tom Kyte (Oracle's resident DB performance guru) could do with Oracle were spectacular. As you say, its a shame about the Oracle baggage, and in particular the steep price tag. Otherwise I'm certain it would be far more widely deployed as database.
- chasil 3y ago
  It's either free (for XE), $17,500 per cpu (for SE2), or $47,500 per cpu (for Enterprise). x86-64 gets a 2-for-1 core discount. You can also license it by user. https://www.oracle.com/assets/technology-price-list-070617.pdf https://www.oracle.com/assets/technology-price-list-070617.p...
_a_a_a_ 3y ago
Using hints is typically a sign of failure. The DB optimiser should typically not need them.
- _a_a_a_ 3y ago
  Ok downvoter, why do you think a DB that needs hints for good performance is better than one that usually doesn't? That is, why do you think it better a person does a computer's job instead of the computer?
  - mickeyp 3y ago
    You've never run into a query planner that suddenly changes its mind once your tables grow. Try using row level security in postgres - it's awfully slow and you need to be a master architect and sql developer to make it run fast because it's too dumb on its own. With query planner hints I could at least guide it. Shame pg doesn't believe in that.
    - _a_a_a_ 3y ago
      Poster said oracle not PG
      - mardifoufs 3y ago
        Total db noob here: is Oracle's query planner better? What's the difference between the two?
        _a_a_a_ 3y ago
        This is not about Oracle versus postgres, it's about whether or not to use hints. My position is that needing hints indicates a failure of the database optimiser. The optimiser theoretically should do a perfect job. In practice it doesn't, and never can so sometimes hints are necessary, but IME and speaking as an MSSQL guy, I very rarely need them.
      - 3y ago

auspiv 3y ago

Been a feature of SQL Server since I believe SQL Server 2008 - temporal tables. They are used (incorrectly) at my place of work quite a bit. If auditing data (who changed what and when) is important - there are two choices: 1) system versioning or 2) add a "version" column or something similar which increments with each change. There are tradeoffs for both. "Temporal tables (also known as system-versioned temporal tables) are a database feature that brings built-in support for providing information about data stored in the table at any point in time, rather than only the data that is correct at the current moment in time." https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/t...

Foobar8568 3y ago
It was the audit features that got introduced in 2008 C2 audit (or something like this) and change tracking. From what I remember, temporal table was a 2016 feature and more or less confirmed by your link.

refset 3y ago

Expanding on the the above, > The original version of PostgreSQL from the 1980s did not remove dead tuples. The idea was that keeping all the older versions allowed applications to execute “time-travel” queries to examine the database at a particular point in time [via https://ottertune.com/blog/the-part-of-postgresql-we-hate-the-most https://ottertune.com/blog/the-part-of-postgresql-we-hate-th...] Postgres deprecated support for time-travel in ~1997 and the more general notion of "system time" wasn't standardised until SQL:2011. This blog post is a good overview on the SQL:2011 spec + adoption in databases of (bi-)temporal versioning: https://illuminatedcomputing.com/posts/2019/08/sql2011-survey/ https://illuminatedcomputing.com/posts/2019/08/sql2011-surve... Temporal versioning is less sophisticated than git-like versioning (no branching etc.) but is usually more aligned with common end-user requirements. Kent Beck suggests this framing of "eventual business consistency": https://tidyfirst.substack.com/p/eventual-business-consistency https://tidyfirst.substack.com/p/eventual-business-consisten...

traceroute66 3y ago
> (bi-)temporal versioning I once spent some time trying to find a way to do bi-temporal versioning in Postgres. The only thing I found was a half-dead abandonware external project and an associated presentation PDF from some conference the author once spoke at. I was unaware that they previously had some form of temporal queries and deprecated it. That is a great shame.
- refset 3y ago
  A shame indeed, they were only a few decades too early! The support for "Time Varying Data" gets discussed briefly in this 1995 paper by Stonebraker "The Design of Postgres" > POSTQUEL allows users to save and query historical data and versions. By default, data in a relation is never deleted or updated. Conventional retrievals always access the current tuples in the relation. Historical data can be accessed by indicating the desired time when defining a tuple variable. > [...] Finally, POSTGRES provides support for versions. A version can be created from a relation or a snapshot. Updates to a version do not modify the underlying relation and updates to the underlying relation will be visible through the version unless the value has been modified in the version. https://dsf.berkeley.edu/papers/ERL-M85-95.pdf https://dsf.berkeley.edu/papers/ERL-M85-95.pdf