Oracle sql中允许多少个SAVEPOINTS？

Question

我开始了解SQL中的保存点，以便回滚到特定的事务点。但是我们不知道我们可以创建多少个保存点？保存点会给数据库带来额外的负担吗！

Answer 1

一个简单的测试证明，保存点数的限制足够大，足以覆盖任何实际用途。很可能是因为资源有限，撤消表空间。

HUSQVIK@HQ_PDB_TCP> CREATE TABLE test1 (val NUMBER);

Table created.

HUSQVIK@HQ_PDB_TCP> BEGIN
  2    FOR i IN 1..100000 LOOP
  3  INSERT INTO test1 VALUES (dbms_random.value);
  4  EXECUTE IMMEDIATE 'SAVEPOINT s' || i;
  5    END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

HUSQVIK@HQ_PDB_TCP> SELECT count(*) FROM test1;

  COUNT(*)
----------
    100000

1 row selected.

HUSQVIK@HQ_PDB_TCP> ROLLBACK TO SAVEPOINT s99999;

Rollback complete.

HUSQVIK@HQ_PDB_TCP> SELECT count(*) FROM test1;

  COUNT(*)
----------
     99999

1 row selected.

HUSQVIK@HQ_PDB_TCP> ROLLBACK TO SAVEPOINT s1;

Rollback complete.

HUSQVIK@HQ_PDB_TCP> SELECT count(*) FROM test1;

  COUNT(*)
----------
         1

1 row selected.

HUSQVIK@HQ_PDB_TCP>

Answer 2

我知道在Oracle7上，默认值是5，可以通过设置Oracle SAVEPOINTS初始化参数将其提高到255。但从那以后，我就再也没有遇到过问题。