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