ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle中的table最多支持多少个列?

Oracle中的table最多支持多少个列?

原创 Linux操作系统 作者:NinGoo 时间:2019-07-16 18:06:01 0 删除 编辑

Oracle中的table的列数有没有限制?如果有,这个限制是多少?


版本信息:

NING@ning>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

创建以下过程用于测试:

create or replace procedure test_cols(cols number)
as
l_sql dbms_sql.varchar2s;
l_cursor number;
l_rows number;

begin
l_cursor := dbms_sql.open_cursor;

l_sql(1) :='create table cols'||cols||'(';

for i in 2..cols+1 loop
l_sql(i) := 'a' || to_char(i-1) || ' int,';
end loop;

l_sql(cols+1) := 'a' || to_char(cols) || ' int)';

dbms_sql.parse(c=>l_cursor,
statement=>l_sql,
lb=>l_sql.first,
ub=>l_sql.last,
lfflg=>TRUE,
language_flag=>dbms_sql.native);

dbms_sql.close_cursor(l_cursor);
end;
/

过程的参数为欲创建的table的列数,创建的table名则为cols+列数,例如cols10,cols100等。

NING@ning>exec test_cols(10);

PL/SQL procedure successfully completed.

font size="2">NING@ning>exec test_cols(100);

PL/SQL procedure successfully completed.

NING@ning>exec test_cols(1000);

PL/SQL procedure successfully completed.

NING@ning>exec test_cols(1001);
BEGIN test_cols(1001); END;

*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 1000
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1608
ORA-06512: at "SYS.DBMS_SQL", line 33
ORA-06512: at "NING.TEST_COLS", line 18
ORA-06512: at line 1

NING@ning>select count(*) from all_tab_columns where owner=user and table_name='COLS10';

COUNT(*)
----------
10

NING@ning>select count(*) from all_tab_columns where owner=user and table_name='COLS100';

COUNT(*)
----------
100

NING@ning>select count(*) from all_tab_columns where owner=user and table_name='COLS1000';

COUNT(*)
----------
1000

根据测试结果可知,oracle中table的列数最多只能为1000。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/193161/viewspace-50288/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    200
  • 访问量
    131834