Named length limits for objects such as Oracle table name, field name, and so on

Original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the source, otherwise hold the copyright legal responsibility.

Deep Blue Blog:http://blog.csdn.net/huangyanlong/article/details/45854385

Today, when the upgrade script is organized for a system database structure, a "name byte too long error" is encountered, similar to the following statement:

Because the naming of field names in the upgraded structure was modified according to the business, there was a case of a long name.

To say here, for Oracle, the number of object-named bytes, such as table name, field name, and so on, is limited to five bytes! Cannot be changed, this is the Cure setting for Oracle .

We can further verify this by looking at the database dictionary.

Experiment :

Under sys user, view user_tables This view, you can find in table_name This field, the type is varchar, the length is 30 , as follows:

Further review the structure of the view as follows:

Create or Replace view user_tables (table_name, Tablespace_name, Cluster_Name, Iot_name, status, Pct_free, pct_used, ini_t Rans, 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) Asselect O.name, Decode (Bitand (t.property,2151678048), 0, Ts.name, decode (t.ts#, 0, NULL,  Ts.name)), Decode (Bitand (T.property, 1024x768), 0, NULL, co.name), Decode (Bitand (t.property) +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.pc tfree$, Max), 0, NULL), Decode (Bitand (ts.flags, +), to_number (null), Decode (Bitand (T.property, 32+ , 0, t.pctused$, 0, NULL), Decode (Bitand (t.property, 0, T.initrans, NULL), Decode (Bitand (t.proper Ty, 0, T.maxtrans, NULL), Decode (Bitand (T.property, 17179869184), 17179869184, ds.initial_s TG * ts.blocksize, s.iniexts * ts.blocksize), decode (Bitand (T.property, 17179869184), 1717986918 4, Ds.next_stg * ts.blocksize, S.extsize * ts.blocksize), decode (Bitand (T.property, 171798              69184), 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.P Roperty, 17179869184), 17179869184, DS.PCTINC_STG, s.extpct)), Decode (Bitand (ts.flags, +), +, 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, +), +, 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 (bit and (T.flags, +), 0, ' YES ', ' NO '), null), Decode (Bitand (t.flags,1), 0, ' Y ', 1, ' N ', '? '), t.rowcnt, deco De (Bitand (T.property), 0, t.blkcnt, NULL), Decode (Bitand (T.property), 0, t.empcnt, NULL), Decode (Bitan D (T.property, +), 0, T.AVGSPC, null), t.chncnt, T.avgrln, T.AVGSPC_FLB, Decode (Bitand (t.property, 0), T.F LBCNT, null), Lpad(Decode (T.degree, 32767, ' Default ', NVL (t.degree,1)), Lpad (Decode (t.instances, 32767, ' Default ', NVL (t.instances , 1)), 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, +), +, ' YES ', ' NO '), Decode (Bitand (T.propert  Y, +), +, ' IOT ', Decode (Bitand (t.property, MB), "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, +), 0, ' n ', +, ' Y ', ' n '), Decode (Bitand (T.property, 8192), 8192, ' YES ', Decode (Bitand (T.pro Perty, 1), 0, ' NO ', ' YES '), Decode (Bitand (O.flags, 2), 2, ' DEFAULT ', decode (Bitand (Decode (Bitand (T.pro Perty, 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), 4, 1, ' KEEP ', 2, ' NONE ' , ' Default '), Decode (Bitand (O.flags, 2), 2, ' DEFAULT ', decode (Bitand (Decode (Bitand (t.property), 1717986 9184), 17179869184, Ds.bfp_stg, S.cachehint),/16, 1, ' KEEP ', 2, ' NONE ', ' DEFAULT '), Decode (Bitand (T.flags, 131072), 131072, ' ENABLED ', ' DISABLED '), Decode (Bitand (T.flags, 51           2), 0, ' NO ', ' YES '), Decode (Bitand (t.flags, 0), 0, ' NO ', ' yes '), decode (Bitand (O.flags, 2),, NULL, Decode (Bitand (T.property, 8388608), 8388608, ' sys$session ', ' sys$transaction '), decode (Bitand (t  . Flags, 1024x768), 1024x768, ' 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, 1024x768), 0, NULL, cu.na  Me), Decode (Bitand (T.flags, 8388608), 8388608, ' ENABLED ', ' DISABLED '), Case when (Bitand (T.property, 32) = 32)  Then NULL if (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, +) = +) then NULL if (Bitand (T.property, 17179869184) = 17179869184) Then Decode (Bitand (DS.FLAGS_STG, 4), 4, Case if Bitand (DS.CMPFLAG_STG, 3) = 1 Then ' BASIC ' W                                          Hen 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--0x 4000000 then "QUERY High" when Bitand (S.spare1, 100663296) = 100663296--0x2000000+0x40000                   ARCHIVE ' When Bitand (S.spare1, 134217728) = 134217728--0x8000000        Then ' ARCHIVE ' Else ' BASIC ' End, decode (Bitand (o.flags, +), +, ' YES ', ' NO '), Decode (Bitand (T.trigflag, 2097152), 2097152, ' YES ', ' NO '), Decode (Bitand (T.property, 17179869184), 1717986918 4, ' NO ', Decode (Bitand (T.property, +), +, ' n/A ', ' YES '), Decode (Bitand (t.property,16492674416640), 21    99023255552, ' Force ',                 4398046511104, ' MANUAL ', ' DEFAULT ') from sys.ts$ ts, sys.seg$ S, sys.obj$ Co, sys.tab$ T, sys.obj$ O, sy  s.deferred_stg$ DS, sys.obj$ cx, sys.user$ cu, X$ksppcv ksppcv, x$ksppi ksppiwhere o.owner# = Userenv (' SchemaID ') and o.obj# = t.obj# and Bitand (t.property, 1) = 0 and Bitand (o.flags,) = 0 and t.bobj# = co.obj# (+) and t.ts# = TS.T s# 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 would be unusable if a previous DROP table operation Failed,valid otherwise '; Comment on Colu MN 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 ' have 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 and 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 was to being scanned '; Comment on column user_tables. The cache is ' Whether ', the table is-cached in the buffer cache ', comment on column user_tables. Table_lock is ' Whether table locking are 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 were ANALYZED '; Comment on column user_tables. Partitioned is the This table partitioned? YES or NO '; Comment on column user_tables. Iot_type is ' If index-only table, then Iot_type was IOT or Iot_overflow or iot_mapping else NULL '; Comment on column User_ta Bles. Temporary is ' Can ', the current session, only see data, it place in this objECT itself? '; Comment on column user_tables. Secondary is ' the This Table object created as part of the icreate for domain indexes? '; Comment on column user_tables. NESTED is ' The Table a NESTED table? '; Comment on column user_tables. Buffer_pool is ' The default buffer POOL to being used for table blocks '; Comment on column user_tables. Flash_cache is ' The default flash CACHE hint to being used for table blocks '; Comment on column user_tables. Cell_flash_cache is ' The default cell FLASH CACHE hint to being used for table blocks '; Comment on column user_tables. Row_movement is ' Whether partitioned row movement are enabled or disabled '; Comment on column user_tables. Global_stats is ' 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 was enabled or not '; Comment on column user_tables. Compress_for is ' COMPRESS what kind of the operations '; Comment on column user_tables. DROPPED is ' Whether table was DROPPED and is in Recycle Bin '; Comment on column user_tables. READ_ONLY is ' Whether table was read only or not '; Comment on column user_tables. Segment_created is ' Whether the table SEGMENT was CREATED or not '; Comment on column user_tables. Result_cache is ' The result of CACHE mode annotation for the table ';

We can lock into three places from the view structure and navigate to the source as follows:

table_name

O.name

sys.obj$ o

can be found that table_name field from sys.obj$ in the name fields, as follows :

We tried to modify the length of the name field in sys.obj$ as follows:

As you can see, we are unable to modify the definition of the dictionary field of the database system. In the same way, we can also view views such as user_tab_columns,user_constraints , and find that for Oracle, the length of the named is limited to a limit of four bytes.

After searching the internet, we found that the original name length limit for different databases is not the same, excerpt as follows:

Database	Table name length limit	Field name length limit
Oracle	30	30
Mysql	64	64
Db2	128	128
Access	64	64
Sql server	128	128

Original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the source, otherwise hold the copyright legal responsibility.

Deep Blue Blog:http://blog.csdn.net/huangyanlong/article/details/45854385

Named length limits for objects such as Oracle table name, field name, and so on