0

Not sure if this is the right place for this question but as I could not found the answer on Oracle or through Google.

Can I back up my table (say table A) data from Oracle using external table, and is the right way to do it? The data will be older than 5 years that will be only used for investigation purposes.

And does this process save my tablespace in Oracle, if I delete/purge those copied from table A?

Does an external table use any space in the actual database?

William Robertson
  • 15,273
  • 4
  • 38
  • 44
ashim
  • 67
  • 1
  • 2
  • 12
  • You could probably have a Hive/ Hadoop datastore to preserve historical data older than certain years if your volume in the transactional database (Oracle) has increased beyond a threshold. External tables may be a good option if your OS / cloud admin can provide you sufficient disk space in the storage or cloud. But, I would say this is not a specific programming question and what you would get here is only personal opinions. – Kaushik Nayak Sep 03 '18 at 03:32
  • Will be you be accessing the table regularly? If not, you can export the dump of the table and import it when required. External table doesn't use up SGA memory until or unless it is queried. – bprasanna Sep 03 '18 at 05:52
  • @learningloop wont be accessing the table regularly. which process is faster, dumping, importing and then querying or using external table , then query when required ?? – ashim Sep 03 '18 at 06:25

1 Answers1

0

An external table only takes up space in the filesystem, not the database.

You will need to consider how your backup/restore/failover setup handles the associated OS directory when deciding on your archiving approach. If you are restoring a database backup in five years' time, will your external table's associated data file be in it? If not, will there be backups of the filesystem that you could also restore?

William Robertson
  • 15,273
  • 4
  • 38
  • 44