oracle的长度限制

最新推荐文章于 2024-05-10 08:20:00 发布

大圣向西行

最新推荐文章于 2024-05-10 08:20:00 发布

阅读量1.7w

点赞数 1

分类专栏： oracle

oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一. varchar2长度限制问题

1) 表字段创建最大长度：4000
SQL> CREATE TABLE t_test1(a VARCHAR2( 4000 )); --正确
Table created.
SQL> CREATE TABLE t_test2(a VARCHAR2( 4001 )); --错误
CREATE TABLE t_test2(a VARCHAR2(4001))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype

2) plsql变量定义最大长度：32767
SQL> CREATE OR REPLACE PROCEDURE sp_test1 (p IN VARCHAR2 DEFAULT NULL ) --正确
IS
x_var VARCHAR2 ( 32767 );
BEGIN
DBMS_OUTPUT.put_line (p);
END;
/
Procedure created.
SQL> CREATE OR REPLACE PROCEDURE sp_test2 (p IN VARCHAR2 DEFAULT NULL ) --错误
IS
x_var2 VARCHAR2 ( 32768 );
BEGIN
NULL;
END;
/
Warning: Procedure created with compilation errors.
SQL> show error;
Errors for PROCEDURE SP_TEST2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/23 PLS-00215: String length constraints must be in range (1 ..
32767)

3) plsql调用参数最大长度：65535
SQL> DECLARE
lv_test VARCHAR2 (32767);
BEGIN
FOR i IN 1 .. 32767
LOOP
lv_test := lv_test || '-';
END LOOP;
sp_test1 (LENGTHB (lv_test || lv_test || ' 1 '));
END;
/
65535
SQL> DECLARE
lv_test VARCHAR2 (32767);
BEGIN
FOR i IN 1 .. 32767
LOOP
lv_test := lv_test || '-';
END LOOP;
sp_test1 (LENGTHB (lv_test || lv_test || ' 12 '));
END;
/
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 9

二. dbms_output.put_line长度限制问题

对于10g以上版本(包括10g), dbms_output.put_line的最大长度限制是 32767 . 如果报错buffer overflow, 执行如下语句即可:
set serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

对于10g以下版本dbms_output.put_line最大长度限制是 255 .

oracle表名、字段名等对象的命名长度限制

今天在为某系统数据库结构整理升级脚本时，遇到了“命名字节过长的错误”，类似于下面的截图语句：

由于升级的结构中对于字段名的命名根据业务进行了修改，出现了命名过长的情况。

这里想说一下，对于oracle，表名、字段名等对象命名字节个数限制在了30个字节！是无法更改的，这是oracle的固化设置。

我们可以通过查看数据库字典进一步验证一下。先思考一下，哪里可以获得关于这类命名的信息呢？有没有想到USER_TAB_COLUMNS,USER_CONSTRAINTS这一类的视图。我们来做一个验证。

实验:

在sys用户下，查看user_tables这个视图，可以发现在table_name这个字段，类型为varchar，长度为30，这里记录的表的名称，如下：

再进一步查看视图的结构，如下：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

 
        create 
        or 
        replace 
        view 
        user_tables 
       
 
        (table_name, tablespace_name, cluster_name, iot_name, status, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent, min_extents, max_extents, pct_increase, freelists, freelist_groups, logging, backed_up, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, avg_space_freelist_blocks, num_freelist_blocks, degree, instances, cache, table_lock, sample_size, last_analyzed, partitioned, iot_type,  
        temporary 
        , secondary, nested, buffer_pool, flash_cache, cell_flash_cache, row_movement, global_stats, user_stats, duration, skip_corrupt, monitoring, cluster_owner, dependencies, compression, compress_for, dropped, read_only, segment_created, result_cache) 
       
 
        as 
       
 
        select 
        o. 
        name 
        , 
       
 
                
        decode(bitand(t.property,2151678048), 0, ts. 
        name 
        , 
       
 
                       
        decode(t.ts#, 0,  
        null 
        , ts. 
        name 
        )), 
       
 
                
        decode(bitand(t.property, 1024), 0,  
        null 
        , co. 
        name 
        ), 
       
 
                
        decode((bitand(t.property, 512)+bitand(t.flags, 536870912)), 
       
 
                       
        0,  
        null 
        , co. 
        name 
        ), 
       
 
                
        decode(bitand(t.trigflag, 1073741824), 1073741824,  
        'UNUSABLE' 
        ,  
        'VALID' 
        ), 
       
 
                
        decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0,  
        null 
        ), 
       
 
                
        decode(bitand(ts.flags, 32), 32, to_number( 
        NULL 
        ), 
       
 
                   
        decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0,  
        null 
        )), 
       
 
                
        decode(bitand(t.property, 32), 0, t.initrans,  
        null 
        ), 
       
 
                
        decode(bitand(t.property, 32), 0, t.maxtrans,  
        null 
        ), 
       
 
                
        decode(bitand(t.property, 17179869184), 17179869184, 
       
 
                              
        ds.initial_stg * ts.blocksize, 
       
 
                              
        s.iniexts * ts.blocksize), 
       
 
                
        decode(bitand(t.property, 17179869184), 17179869184, 
       
 
                       
        ds.next_stg * ts.blocksize, 
       
 
                       
        s.extsize * ts.blocksize), 
       
 
                
        decode(bitand(t.property, 17179869184), 17179869184, 
       
 
                       
        ds.minext_stg, s.minexts), 
       
 
                
        decode(bitand(t.property, 17179869184), 17179869184, 
       
 
                       
        ds.maxext_stg, s.maxexts), 
       
 
                
        decode(bitand(ts.flags, 3), 1, to_number( 
        NULL 
        ), 
       
 
                       
        decode(bitand(t.property, 17179869184), 17179869184, 
       
 
                                     
        ds.pctinc_stg, s.extpct)), 
       
 
                
        decode(bitand(ts.flags, 32), 32, to_number( 
        NULL 
        ), 
       
 
                  
        decode(bitand(o.flags, 2), 2, 1, 
       
 
                         
        decode(bitand(t.property, 17179869184), 17179869184, 
       
 
                                
        ds.frlins_stg, decode(s.lists, 0, 1, s.lists)))), 
       
 
                
        decode(bitand(ts.flags, 32), 32, to_number( 
        NULL 
        ), 
       
 
                  
        decode(bitand(o.flags, 2), 2, 1, 
       
 
                         
        decode(bitand(t.property, 17179869184), 17179869184, 
       
 
                                
        ds.maxins_stg, decode(s.groups, 0, 1, s.groups)))), 
       
 
                
        decode(bitand(t.property, 32+64), 0, 
       
 
                         
        decode(bitand(t.flags, 32), 0,  
        'YES' 
        ,  
        'NO' 
        ),  
        null 
        ), 
       
 
                
        decode(bitand(t.flags,1), 0,  
        'Y' 
        , 1,  
        'N' 
        ,  
        '?' 
        ), 
       
 
                
        t.rowcnt, 
       
 
                
        decode(bitand(t.property, 64), 0, t.blkcnt,  
        null 
        ), 
       
 
                
        decode(bitand(t.property, 64), 0, t.empcnt,  
        null 
        ), 
       
 
                
        decode(bitand(t.property, 64), 0, t.avgspc,  
        null 
        ), 
       
 
                
        t.chncnt, t.avgrln, t.avgspc_flb, 
       
 
                
        decode(bitand(t.property, 64), 0, t.flbcnt,  
        null 
        ), 
       
 
                
        lpad(decode(t.degree, 32767,  
        'DEFAULT' 
        , nvl(t.degree,1)),10), 
       
 
                
        lpad(decode(t.instances, 32767,  
        'DEFAULT' 
        , nvl(t.instances,1)),10), 
       
 
                
        lpad(decode(bitand(t.flags, 8), 8,  
        'Y' 
        ,  
        'N' 
        ),5), 
       
 
                
        decode(bitand(t.flags, 6), 0,  
        'ENABLED' 
        ,  
        'DISABLED' 
        ), 
       
 
                
        t.samplesize, t.analyzetime, 
       
 
                
        decode(bitand(t.property, 32), 32,  
        'YES' 
        ,  
        'NO' 
        ), 
       
 
                
        decode(bitand(t.property, 64), 64,  
        'IOT' 
        , 
       
 
                        
        decode(bitand(t.property, 512), 512,  
        'IOT_OVERFLOW' 
        , 
       
 
                        
        decode(bitand(t.flags, 536870912), 536870912,  
        'IOT_MAPPING' 
        ,  
        null 
        ))), 
       
 
                
        decode(bitand(o.flags, 2), 0,  
        'N' 
        , 2,  
        'Y' 
        ,  
        'N' 
        ), 
       
 
                
        decode(bitand(o.flags, 16), 0,  
        'N' 
        , 16,  
        'Y' 
        ,  
        'N' 
        ), 
       
 
                
        decode(bitand(t.property, 8192), 8192,  
        'YES' 
        , 
       
 
                       
        decode(bitand(t.property, 1), 0,  
        'NO' 
        ,  
        'YES' 
        )), 
       
 
                
        decode(bitand(o.flags, 2), 2,  
        'DEFAULT' 
        , 
       
 
                       
        decode(bitand(decode(bitand(t.property, 17179869184), 17179869184, 
       
 
                                     
        ds.bfp_stg, s.cachehint), 3), 
       
 
                                     
        1,  
        'KEEP' 
        , 2,  
        'RECYCLE' 
        ,  
        'DEFAULT' 
        )), 
       
 
                
        decode(bitand(o.flags, 2), 2,  
        'DEFAULT' 
        , 
       
 
                       
        decode(bitand(decode(bitand(t.property, 17179869184), 17179869184, 
       
 
                                     
        ds.bfp_stg, s.cachehint), 12)/4, 
       
 
                                     
        1,  
        'KEEP' 
        , 2,  
        'NONE' 
        ,  
        'DEFAULT' 
        )), 
       
 
                
        decode(bitand(o.flags, 2), 2,  
        'DEFAULT' 
        , 
       
 
                       
        decode(bitand(decode(bitand(t.property, 17179869184), 17179869184, 
       
 
                                     
        ds.bfp_stg, s.cachehint), 48)/16, 
       
 
                                     
        1,  
        'KEEP' 
        , 2,  
        'NONE' 
        ,  
        'DEFAULT' 
        )), 
       
 
                
        decode(bitand(t.flags, 131072), 131072,  
        'ENABLED' 
        ,  
        'DISABLED' 
        ), 
       
 
                
        decode(bitand(t.flags, 512), 0,  
        'NO' 
        ,  
        'YES' 
        ), 
       
 
                
        decode(bitand(t.flags, 256), 0,  
        'NO' 
        ,  
        'YES' 
        ), 
       
 
                
        decode(bitand(o.flags, 2), 0,  
        NULL 
        , 
       
 
                    
        decode(bitand(t.property, 8388608), 8388608, 
       
 
                           
        'SYS$SESSION' 
        ,  
        'SYS$TRANSACTION' 
        )), 
       
 
                
        decode(bitand(t.flags, 1024), 1024,  
        'ENABLED' 
        ,  
        'DISABLED' 
        ), 
       
 
                
        decode(bitand(o.flags, 2), 2,  
        'NO' 
        , 
       
 
                    
        decode(bitand(t.property, 2147483648), 2147483648,  
        'NO' 
        , 
       
 
                       
        decode(ksppcv.ksppstvl,  
        'TRUE' 
        ,  
        'YES' 
        ,  
        'NO' 
        ))), 
       
 
                
        decode(bitand(t.property, 1024), 0,  
        null 
        , cu. 
        name 
        ), 
       
 
                
        decode(bitand(t.flags, 8388608), 8388608,  
        'ENABLED' 
        ,  
        'DISABLED' 
        ), 
       
 
                
        case 
        when 
        (bitand(t.property, 32) = 32)  
        then 
       
 
                  
        null 
       
 
                
        when 
        (bitand(t.property, 17179869184) = 17179869184)  
        then 
       
 
                  
        decode(bitand(ds.flags_stg, 4), 4,  
        'ENABLED' 
        ,  
        'DISABLED' 
        ) 
       
 
                
        else 
       
 
                  
        decode(bitand(s.spare1, 2048), 2048,  
        'ENABLED' 
        ,  
        'DISABLED' 
        ) 
       
 
                
        end 
        , 
       
 
                
        case 
        when 
        (bitand(t.property, 32) = 32)  
        then 
       
 
                  
        null 
       
 
                
        when 
        (bitand(t.property, 17179869184) = 17179869184)  
        then 
       
 
                   
        decode(bitand(ds.flags_stg, 4), 4, 
       
 
                   
        case 
        when 
        bitand(ds.cmpflag_stg, 3) = 1  
        then 
        'BASIC' 
       
 
                        
        when 
        bitand(ds.cmpflag_stg, 3) = 2  
        then 
        'OLTP' 
       
 
                        
        else 
        decode(ds.cmplvl_stg, 1,  
        'QUERY LOW' 
        , 
       
 
                                                   
        2,  
        'QUERY HIGH' 
        , 
       
 
                                                   
        3,  
        'ARCHIVE LOW' 
        , 
       
 
                                                      
        'ARCHIVE HIGH' 
        )  
        end 
        , 
       
 
                        
        null 
        ) 
       
 
                
        else 
       
 
                  
        decode(bitand(s.spare1, 2048), 0,  
        null 
        , 
       
 
                  
        case 
        when 
        bitand(s.spare1, 16777216) = 16777216    
        -- 0x1000000 
       
 
                            
        then 
        'OLTP' 
       
 
                       
        when 
        bitand(s.spare1, 100663296) = 33554432   
        -- 0x2000000 
       
 
                            
        then 
        'QUERY LOW' 
       
 
                       
        when 
        bitand(s.spare1, 100663296) = 67108864   
        -- 0x4000000 
       
 
                            
        then 
        'QUERY HIGH' 
       
 
                       
        when 
        bitand(s.spare1, 100663296) = 100663296  
        -- 0x2000000+0x4000000 
       
 
                            
        then 
        'ARCHIVE LOW' 
       
 
                       
        when 
        bitand(s.spare1, 134217728) = 134217728  
        -- 0x8000000 
       
 
                            
        then 
        'ARCHIVE HIGH' 
       
 
                       
        else 
        'BASIC' 
        end 
        ) 
       
 
                
        end 
        , 
       
 
                
        decode(bitand(o.flags, 128), 128,  
        'YES' 
        ,  
        'NO' 
        ), 
       
 
                
        decode(bitand(t.trigflag, 2097152), 2097152,  
        'YES' 
        ,  
        'NO' 
        ), 
       
 
                
        decode(bitand(t.property, 17179869184), 17179869184,  
        'NO' 
        , 
       
 
                       
        decode(bitand(t.property, 32), 32,  
        'N/A' 
        ,  
        'YES' 
        )), 
       
 
                
        decode(bitand(t.property,16492674416640),2199023255552, 
        'FORCE' 
        , 
       
 
                              
        4398046511104, 
        'MANUAL' 
        , 
        'DEFAULT' 
        ) 
       
 
        from 
        sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o, 
       
 
              
        sys.deferred_stg$ ds, sys.obj$ cx, sys. 
        user 
        $ cu, x$ksppcv ksppcv, 
       
 
              
        x$ksppi ksppi 
       
 
        where 
        o.owner# = userenv( 
        'SCHEMAID' 
        ) 
       
 
           
        and 
        o.obj# = t.obj# 
       
 
           
        and 
        bitand(t.property, 1) = 0 
       
 
           
        and 
        bitand(o.flags, 128) = 0 
       
 
           
        and 
        t.bobj# = co.obj# (+) 
       
 
           
        and 
        t.ts# = ts.ts# 
       
 
           
        and 
        t.file# = s.file# (+) 
       
 
           
        and 
        t.block# = s.block# (+) 
       
 
           
        and 
        t.ts# = s.ts# (+) 
       
 
           
        and 
        t.obj# = ds.obj# (+) 
       
 
           
        and 
        t.dataobj# = cx.obj# (+) 
       
 
           
        and 
        cx.owner# = cu. 
        user 
        # (+) 
       
 
           
        and 
        ksppi.indx = ksppcv.indx 
       
 
           
        and 
        ksppi.ksppinm =  
        '_dml_monitoring_enabled' 
       
 
        ; 
       
 
        comment  
        on 
        table 
        USER_TABLES  
        is 
        'Description of the user' 
        's own relational tables' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.TABLE_NAME  
        is 
        'Name of the table' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.TABLESPACE_NAME  
        is 
        'Name of the tablespace containing the table' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.CLUSTER_NAME  
        is 
        'Name of the cluster, if any, to which the table belongs' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.IOT_NAME  
        is 
        'Name of the index-only table, if any, to which the overflow or mapping table entry belongs' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.STATUS  
        is 
        'Status of the table will be UNUSABLE if a previous DROP TABLE operation failed, 
       
 
        VALID otherwise' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.PCT_FREE  
        is 
        'Minimum percentage of free space in a block' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.PCT_USED  
        is 
        'Minimum percentage of used space in a block' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.INI_TRANS  
        is 
        'Initial number of transactions' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.MAX_TRANS  
        is 
        'Maximum number of transactions' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.INITIAL_EXTENT  
        is 
        'Size of the initial extent in bytes' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.NEXT_EXTENT  
        is 
        'Size of secondary extents in bytes' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.MIN_EXTENTS  
        is 
        'Minimum number of extents allowed in the segment' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.MAX_EXTENTS  
        is 
        'Maximum number of extents allowed in the segment' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.PCT_INCREASE  
        is 
        'Percentage increase in extent size' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.FREELISTS  
        is 
        'Number of process freelists allocated in this segment' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.FREELIST_GROUPS  
        is 
        'Number of freelist groups allocated in this segment' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.LOGGING  
        is 
        'Logging attribute' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.BACKED_UP  
        is 
        'Has table been backed up since last modification?' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.NUM_ROWS  
        is 
        'The number of rows in the table' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.BLOCKS  
        is 
        'The number of used blocks in the table' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.EMPTY_BLOCKS  
        is 
        'The number of empty (never used) blocks in the table' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.AVG_SPACE  
        is 
        'The average available free space in the table' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.CHAIN_CNT  
        is 
        'The number of chained rows in the table' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.AVG_ROW_LEN  
        is 
        'The average row length, including row overhead' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.AVG_SPACE_FREELIST_BLOCKS  
        is 
        'The average freespace of all blocks on a freelist' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.NUM_FREELIST_BLOCKS  
        is 
        'The number of blocks on the freelist' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.DEGREE  
        is 
        'The number of threads per instance for scanning the table' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.INSTANCES  
        is 
        'The number of instances across which the table is to be scanned' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.CACHE  
        is 
        'Whether the table is to be cached in the buffer cache' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.TABLE_LOCK  
        is 
        'Whether table locking is enabled or disabled' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.SAMPLE_SIZE  
        is 
        'The sample size used in analyzing this table' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.LAST_ANALYZED  
        is 
        'The date of the most recent time this table was analyzed' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.PARTITIONED  
        is 
        'Is this table partitioned? YES or NO' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.IOT_TYPE  
        is 
        'If index-only table, then IOT_TYPE is IOT or IOT_OVERFLOW or IOT_MAPPING else NULL' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES. 
        TEMPORARY 
        is 
        'Can the current session only see data that it place in this object itself?' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.SECONDARY  
        is 
        'Is this table object created as part of icreate for domain indexes?' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.NESTED  
        is 
        'Is the table a nested table?' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.BUFFER_POOL  
        is 
        'The default buffer pool to be used for table blocks' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.FLASH_CACHE  
        is 
        'The default flash cache hint to be used for table blocks' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.CELL_FLASH_CACHE  
        is 
        'The default cell flash cache hint to be used for table blocks' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.ROW_MOVEMENT  
        is 
        'Whether partitioned row movement is enabled or disabled' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.GLOBAL_STATS  
        is 
        'Are the statistics calculated without merging underlying partitions?' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.USER_STATS  
        is 
        'Were the statistics entered directly by the user?' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.DURATION  
        is 
        'If temporary table, then duration is sys$session or sys$transaction else NULL' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.SKIP_CORRUPT  
        is 
        'Whether skip corrupt blocks is enabled or disabled' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.MONITORING  
        is 
        'Should we keep track of the amount of modification?' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.CLUSTER_OWNER  
        is 
        'Owner of the cluster, if any, to which the table belongs' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.DEPENDENCIES  
        is 
        'Should we keep track of row level dependencies?' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.COMPRESSION  
        is 
        'Whether table compression is enabled or not' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.COMPRESS_FOR  
        is 
        'Compress what kind of operations' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.DROPPED  
        is 
        'Whether table is dropped and is in Recycle Bin' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.READ_ONLY  
        is 
        'Whether table is read only or not' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.SEGMENT_CREATED  
        is 
        'Whether the table segment is created or not' 
        ; 
       
 
        comment  
        on 
        column 
        USER_TABLES.RESULT_CACHE  
        is 
        'The result cache mode annotation for the table' 
        ; 
       

我们可以由视图结构中锁定到三个地方，定位到来源，如下：

table_name

o.name

sys.obj$ o

可以发现，table_name字段来源于sys.obj$中的name字段，如下：

我们尝试修改一下sys.obj$中name字段的长度，如下：

可以看到，我们是无法修改数据库系统的字典字段的定义。用同样的方法，我们还可以查看USER_TAB_COLUMNS,USER_CONSTRAINTS等视图，会发现对于oracle，限制了命名的长度，都限制在了30个字节。

之后在网上搜了一下，发现对于不同的数据库原来命名长度限制是不一样的，摘录如下：

数据库	表名长度限制	字段名长度限制
oracle	30	30
mysql	64	64
db2	128	128
access	64	64
sqlserver	128	128

大圣向西行

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
oracle的长度限制

一. varchar2长度限制问题1) 表字段创建最大长度：4000SQL&gt; CREATE TABLE t_test1(a VARCHAR2(4000)); --正确Table created.SQL&gt; CREATE TABLE t_test2(a VARCHAR2(4001)); --错误CREATE TABLE t_test2(a VARCHAR2(4001))*ERROR at ...
复制链接

扫一扫

专栏目录

oracle的长度限制

“相关推荐”对你有帮助么？