Oracle数据库表的字段个数有限制吗？
1:http://topic.youkuaiyun.com/t/20060216/17/4560021.html
SQL> BEGIN
2 FOR I IN 1..999 LOOP
3 EXECUTE IMMEDIATE 'ALTER TABLE TEST.TT ADD A' || I || ' NUMBER(1)';
4 END LOOP;
5 END;
6 /
PL/SQL 过程已成功完成。
SQL> SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TT';
COUNT(*)
----------
1000
SQL> ALTER TABLE TEST.TT ADD C NUMBER(1);
ALTER TABLE TEST.TT ADD C NUMBER(1)
*
ERROR 位于第 1 行:
ORA-01792: 表或视图中的最大列数为 1000
ORA-01795 maximum number of expressions in a list is 1000
Cause: More than 254 columns or expressions were specified in a list.
Action: Remove some of the expressions from the list.
Limit and conversion very long IN list : WHERE x IN ( ,,, ...)
2:http://www.itpub.net/474454.html
oracle 对于超过255个字段的表，将把 一行放到多个block上。 一个block最多只存放255个字段。
至于表能有多少字段，你写个程序测试下就知道了
非常简单
create table t (a number);
set serverout on
begin
for i in 1..1000000 loop
execute immediate 'alter table t add( col'|| i || ' number' ;
end loop;
exception when others then
dbms_output.put_line(to_char(sqlcode)||sqlerrm);
end;
3:http://oratip.com/ORA-01792.html
ORA-01792 maximum number of columns in a table or view is 1000
Cause
An attempt was made to create a table or view with more than 1000 columns, or to add more columns to a table or view which pushes it over the maximum allowable limit of 1000. Note that unused columns in the table are counted toward the 1000 column limit.
Action
If the error is a result of a CREATE command, then reduce the number of columns in the command and resubmit. If the error is a result of an ALTER TABLE command, then there are two options:
- If the table contained unused columns, remove them by executing ALTER TABLE DROP UNUSED COLUMNS before adding new columns.
- Reduce the number of columns in the command and resubmit.
---
Related Error Messages to ORA-01792 maximum number of columns in a table or view is 1000
ORA-01787 only one clause allowed per query block
ORA-01788 CONNECT BY clause required in this query block
ORA-01789 query block has incorrect number of result columns.
ORA-01790 expression must have same datatype as corresponding expression
ORA-01791 not a SELECTed expression
ORA-01793 maximum number of index columns is 32
ORA-01794 maximum number of cluster columns is 32.
ORA-01795 maximum number of expressions in a list is 1000
ORA-01796 this operator cannot be used with lists
ORA-01797 this operator must be followed by ANY or ALL
4:http://ningoo.itpub.net/post/2149/265635
Oracle中的table最多支持多少个列？
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。
5:http://www.oracle.com.cn/viewthread.php?tid=51100&extra=page%3D28
请问各位大侠，在oracle 的表中最多可以有多少字段？
请问各位大侠，在oracle 的表中最多可以有多少字段？
我需要创建一个有1350个字段的表。
你的设计不合理, Oracle最多支持1000个字段^_^.
引用:
SQL> declare
2 query varchar2(20000) := 'create table t01(';
3 begin
4 for i in 1..1001 loop
5 query := query||'col'||i||' int,';
6 end loop;
7 query := query||'colx int)';
8 execute immediate query;
9 end;
10 /
declare
*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 1000
ORA-06512: at line 8
SQL>
6:http://bbs.51cto.com/thread-421919-1-1.html
Oracle的最大列数是1000，能不能无限制？
因为我们的数据比较特殊，表需要设置很多字段，但是oracle提示字段数不能超过1000。
不知有没有方法能让字段数量无限制？
谢谢！
mingjie1116x 2007-9-4 08:55
有没有具体报错信息提示？
mingjie1116x 2007-9-4 09:25
刚才查实，ORACLE字段不能超过1000，是不能改的！
Columns Per table 1000 columns maximum
liuyycici 2007-9-5 05:00
建表的时候，错误提示是“ORA-01792: 表或视图中的最大列数为 1000”
真遗憾，能没有限制就好了
？
7:http://blog.chinaunix.net/u1/57759/showart_548414.html
Oracle10.2限制(取自官方文档)
8:http://technet.microsoft.com/zh-cn/library/ms143432.aspx
SQL Server 2005 最大容量规范
扫一扫
1692
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
