当前位置：文江博客话题详情

如何查找与表名关联的表的序列名

发布于 2025-01-10 06:35:29 字数 84 浏览 0 评论 0原文

如何在Oracle sql中查找与mytable关联的序列名称 Select * user_sequwnces where table_name = 表名

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

远昼 2025-01-17 06:35:29

如果您使用IDENTITY子句创建了一个表，那么序列名称将与表的对象ID对齐，例如

    SQL> create table t ( x int generated as identity);
    
    Table created.
    
    SQL> select object_id from user_objects
      2  where object_name = 'T';
    
     OBJECT_ID
    ----------
       4282429
    
    SQL> select sequence_name
      2  from   user_sequences
      3  where sequence_name like '%4282429%';
    
    SEQUENCE_NAME
    -----------------------------------------------------
    ISEQ$_4282429

但是如果您只是使用自己的序列作为默认值，则不存在这种关系，例如

SQL> create sequence blah;

Sequence created.

SQL> create table t1 ( x int default blah.nextval, y int );

Table created.

SQL> create table t2 ( x int default blah.nextval, y int );

Table created.

SQL> create table t3 ( x int default blah.nextval, y int );

Table created.

您甚至可以删除序列......并且只有当您稍后尝试使用它时才会成为问题，例如

SQL> drop sequence blah;

Sequence dropped.

SQL> insert into t1 (x,y) values (0,0);

1 row created.

SQL> insert into t1 (y) values (0);
insert into t1 (y) values (0)
          *
ERROR at line 1:
ORA-02289: sequence does not exist

If you created a table using the IDENTITY clause, then the sequence name will aligned with the object ID of the table, eg

    SQL> create table t ( x int generated as identity);
    
    Table created.
    
    SQL> select object_id from user_objects
      2  where object_name = 'T';
    
     OBJECT_ID
    ----------
       4282429
    
    SQL> select sequence_name
      2  from   user_sequences
      3  where sequence_name like '%4282429%';
    
    SEQUENCE_NAME
    -----------------------------------------------------
    ISEQ$_4282429

But if you just use a sequence of your own as a default, there is no such relationship, eg

SQL> create sequence blah;

Sequence created.

SQL> create table t1 ( x int default blah.nextval, y int );

Table created.

SQL> create table t2 ( x int default blah.nextval, y int );

Table created.

SQL> create table t3 ( x int default blah.nextval, y int );

Table created.

You can even drop the sequence ... and that only would become an issue later when you try to use it, eg

SQL> drop sequence blah;

Sequence dropped.

SQL> insert into t1 (x,y) values (0,0);

1 row created.

SQL> insert into t1 (y) values (0);
insert into t1 (y) values (0)
          *
ERROR at line 1:
ORA-02289: sequence does not exist

回复收藏 0 原文

~没有更多了~