[20171113]修改表结构删除列相关问题3.txt

[20171113]修改表结构删除列相关问题3.txt

--//维护表结构删除字段一般都是先
ALTER TABLE <table_name> SET UNUSED (<column_name>);

--//然后等空闲时候删除列.
ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <n>;

--//参考文档:
https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11662

Removing Unused Columns

The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes
unused columns from the table and reclaims disk space.

In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint
to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of
undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;
 
--//从文档上可以看出加入CHECKPOINT关键字可以一定程度减少undo空间的消耗.

--//测试看看使用CHECKPOINT <n>的情况.如果执行中断会出现什么情况呢?会回滚吗,oracle如何处理这些细节问题.而且这个时候sys.col$并不能修改.
--//因为这些修改没有完成.通过测试理解这些问题.

1.环境:

SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t (id number,v1 varchar2(5),v2 varchar2(10));
insert into t select rownum,lpad('a',5,'a'),lpad('b',10,'b') from xmltable('1 to 1000000');
commit;

SCOTT@book> SELECT obj#,col#, segcol#, name, intcol#, type#,PROPERTY FROM sys.col$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE object_name = 'T' AND owner = user);
        OBJ#         COL#      SEGCOL# NAME                      INTCOL#        TYPE#     PROPERTY
------------ ------------ ------------ -------------------- ------------ ------------ ------------
       90622            1            1 ID                              1            2            0
       90622            2            2 V1                              2            1            0
       90622            3            3 V2                              3            1            0

SCOTT@book> ALTER TABLE t SET UNUSED (v1);
Table altered.

SCOTT@book> SELECT obj#,col#, segcol#, name, intcol#, type#,PROPERTY FROM sys.col$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE object_name = 'T' AND owner = user);
        OBJ#         COL#      SEGCOL# NAME                                INTCOL#        TYPE#     PROPERTY
------------ ------------ ------------ ------------------------------ ------------ ------------ ------------
       90622            1            1 ID                                        1            2            0
       90622            0            2 SYS_C00002_17111311:56:47$                2            1        32800
       90622            2            3 V2                                        3            1            0

COTT@book> @ &r/spid
         SID      SERIAL# PROCESS                  SERVER    SPID       PID    P_SERIAL# C50
------------ ------------ ------------------------ --------- ------ ------- ------------ --------------------------------------------------
          67           93 48102                    DEDICATED 48103       29           35 alter system kill session '67,93' immediate;

--// 记下进程号spid=48103

2.执行DROP UNUSED COLUMNS:
--//session 1:
ALTER TABLE t DROP UNUSED COLUMNS CHECKPOINT 10;

--//session 2, 在命令行执行:
$ kill -l
1) SIGHUP       2) SIGINT       3) SIGQUIT      4) SIGILL
5) SIGTRAP      6) SIGABRT      7) SIGBUS       8) SIGFPE
9) SIGKILL     10) SIGUSR1     11) SIGSEGV     12) SIGUSR2
13) SIGPIPE     14) SIGALRM     15) SIGTERM     16) SIGSTKFLT
17) SIGCHLD     18) SIGCONT     19) SIGSTOP     20) SIGTSTP
21) SIGTTIN     22) SIGTTOU     23) SIGURG      24) SIGXCPU
25) SIGXFSZ     26) SIGVTALRM   27) SIGPROF     28) SIGWINCH
29) SIGIO       30) SIGPWR      31) SIGSYS      34) SIGRTMIN
35) SIGRTMIN+1  36) SIGRTMIN+2  37) SIGRTMIN+3  38) SIGRTMIN+4
39) SIGRTMIN+5  40) SIGRTMIN+6  41) SIGRTMIN+7  42) SIGRTMIN+8
43) SIGRTMIN+9  44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12
47) SIGRTMIN+13 48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14
51) SIGRTMAX-13 52) SIGRTMAX-12 53) SIGRTMAX-11 54) SIGRTMAX-10
55) SIGRTMAX-9  56) SIGRTMAX-8  57) SIGRTMAX-7  58) SIGRTMAX-6
59) SIGRTMAX-5  60) SIGRTMAX-4  61) SIGRTMAX-3  62) SIGRTMAX-2
63) SIGRTMAX-1  64) SIGRTMAX

--//19 是 stop,18是cout,9 kill.我直接使用-9 kill进程.
--//先写下命令,避免手忙脚乱.先在session 1发出ALTER TABLE t DROP UNUSED COLUMNS CHECKPOINT 10;,然后切换到session 2执行如下命令.
$ kill -9 48103

--//session 1:
SCOTT@book> ALTER TABLE t DROP UNUSED COLUMNS CHECKPOINT 10;
ALTER TABLE t DROP UNUSED COLUMNS CHECKPOINT 10
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 48103
Session ID: 67 Serial number: 93

SCOTT@book> select * from t where rownum<=10;
select * from t where rownum<=10
              *
ERROR at line 1:
ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE

--  //可以发现无法select,也就是要Submit ALTER TABLE DROP COLUMNS CONTINUE.

SCOTT@book>  ALTER TABLE t DROP UNUSED COLUMNS CHECKPOINT 1000;
ALTER TABLE t DROP UNUSED COLUMNS CHECKPOINT 1000
             *
ERROR at line 1:
ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE

$ oerr ora 12986
12986, 00000, "columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE"
// *Cause:  An attempt was made to access a table with columns in partially
//          dropped state (i.e., drop column operation was interrupted).
// *Action: Submit ALTER TABLE DROP COLUMNS CONTINUE to complete the drop
//          column operation before accessing the table.

--//一旦出现这样的情况,就不能在使用CHECKPOINT参数.而是执行:

SCOTT@book> ALTER TABLE t DROP COLUMNS CONTINUE;
Table altered.

SCOTT@book> select * from t where rownum<=1;
        ID V2
---------- ----------
       563 bbbbbbbbbb

--ok. 问题在于oracle如何知道发生了中断,重新产生问题跟踪看看(步骤略).

SCOTT@book> @ &r/10046on 12
old   1: alter session set events '10046 trace name context forever, level &1'
new   1: alter session set events '10046 trace name context forever, level 12'
Session altered.

SCOTT@book> select * from t where rownum<=1;
select * from t where rownum<=1
              *
ERROR at line 1:
ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE

SCOTT@book> @ &r/10046off
Session altered.

--//看不出问题.没有任何线索.

SYS@book> select * from sys.tab$  where obj#=90622;
...省略...
SP2-0784: Invalid or incomplete character beginning 0xEF returned

--//报SP2-0784错误.

SYS@book> host oerr  SP2 0784
00784,0, "Invalid or incomplete character beginning 0x%02X returned\n"
// *Cause:  Attempted to return a string from the database that contained
//          an invalid or incomplete character.
// *Action: Replace the invalid or incomplete string in the database with
//          a valid or complete string.

--//一个一个字段查询确定,问题在spare4字段上.

SYS@book> select SPARE4 from sys.tab$  where obj#=90622;
SPARE4
-----------------------------------------------------------------
SP2-0784: Invalid or incomplete character beginning 0xEF returned

SCOTT@book> select dump(SPARE4,16) c30 ,spare6 from sys.tab$  where obj#=90622;
C30                            SPARE6
------------------------------ --------------------
Typ=1 Len=6: 1,0,3,e1,0,ef     2017-11-13 03:56:47

--//注意后面有一个ef表示.

--//再建立一个表T1 对比看看.

SCOTT@book> create table t1 (id number,v1 varchar2(5),v2 varchar2(10));
Table created.

SCOTT@book> insert into t1 select rownum,lpad('a',5,'a'),lpad('b',10,'b') from xmltable('1 to 100000');
100000 rows created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> SELECT obj#,col#, segcol#, name, intcol#, type#,PROPERTY FROM sys.col$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE object_name = 'T1' AND owner = user);
      OBJ#       COL#    SEGCOL# NAME                    INTCOL#      TYPE#   PROPERTY
---------- ---------- ---------- -------------------- ---------- ---------- ----------
     90624          1          1 ID                            1          2          0
     90624          2          2 V1                            2          1          0
     90624          3          3 V2                            3          1          0

SCOTT@book> column spare6 format a20
SCOTT@book> select  OBJ#,DATAOBJ#,spare1,spare2,spare3,spare4,spare5,spare6 from sys.tab$  where obj#=90624;
      OBJ#   DATAOBJ#       SPARE1     SPARE2     SPARE3 SPARE4     SPARE5     SPARE6
---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------------
     90624      90624          736                                             2017-11-13 06:59:26

SCOTT@book> ALTER TABLE t1 SET UNUSED (v1);
Table altered.

SCOTT@book> select  OBJ#,DATAOBJ#,spare1,spare2,spare3,spare4,spare5,spare6 from sys.tab$  where obj#=90624;
      OBJ#   DATAOBJ#       SPARE1     SPARE2     SPARE3 SPARE4     SPARE5     SPARE6
---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------------
     90624      90624          736                                             2017-11-13 07:01:07

SCOTT@book>  SELECT obj#,col#, segcol#, name, intcol#, type#,PROPERTY FROM sys.col$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE object_name = 'T1' AND owner = user);
      OBJ#       COL#    SEGCOL# NAME                              INTCOL#      TYPE#   PROPERTY
---------- ---------- ---------- ------------------------------ ---------- ---------- ----------
     90624          1          1 ID                                      1          2          0
     90624          0          2 SYS_C00002_17111315:01:08$              2          1      32800
     90624          2          3 V2                                      3          1          0

--//你可以发现在ALTER TABLE t1 DROP UNUSED COLUMNS CHECKPOINT 10;前,sys.tab$的spare4为null.
--//session 1:

SCOTT@book> @ &r/spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        54        203 49380                    DEDICATED 49381       28         74 alter system kill session '54,203' immediate;

--//确定转储文件: /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_49381.trc

$ cat x1.sql
@ &r/10046on 12
select current_scn from v$database ;
ALTER TABLE t1 DROP UNUSED COLUMNS CHECKPOINT 10;
select current_scn from v$database ;
@ &r/10046off

SCOTT@book> select  OBJ#,DATAOBJ#,spare1,spare2,spare3,spare4,dump(spare4,16) c30,spare5,spare6 from sys.tab$  where obj#=90624;
      OBJ#   DATAOBJ#     SPARE1     SPARE2     SPARE3 SPARE4     C30                            SPARE5     SPARE6
---------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ---------- --------------------
     90624      90624        736                        寠 k     Typ=1 Len=6: 1,0,8c,8a,0,6b               2017-11-13 07:01:07

--// 检查跟踪文件,发现如下:(注sql语句我做了格式化处理.)
PARSING IN CURSOR #140630994050912 len=532 dep=1 uid=0 oct=6 lid=0 tim=1510556663952810 hv=685354830 ad='7bc57128' sqlid='b5cr4hhndmbuf'
UPDATE tab$
   SET ts#  = :2,
       file# = :3,
       block# = :4,
       bobj# = decode(:5,
       0,
       null,
       :5),
       tab# = decode(:6,
       0,
       null,
       :6),
       intcols = :7,
       kernelcols = :8,
       clucols = decode(:9,
       0,
       null,
       :9),
       audit$ = :10,
       flags = :11,
       pctfree$ = :12,
       pctused$ = :13,
       initrans = :14,
       maxtrans = :15,
       rowcnt = :16,
       blkcnt = :17,
       empcnt = :18,
       avgspc = :19,
       chncnt = :20,
       avgrln = :21,
       analyzetime = :22,
       samplesize = :23,
       cols = :24,
       property = :25,
       degree = decode(:26,
       1,
       null,
       :26),
       instances = decode(:27,
       1,
       null,
       :27),
       dataobj# = :28,
       avgspc_flb = :29,
       flbcnt = :30,
       trigflag = :31,
       spare1 = :32,
       spare2 = decode(:33,
       0,
       null,
       :33),
       spare4 = :34,
       spare6 = :35
WHERE obj# = :1
END OF STMT

*** 2017-11-13 15:04:23.953
BINDS #140630995900744:
Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7fe73485db08  bln=22  avl=02  flg=05
  value=4
Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7fe73485db20  bln=22  avl=02  flg=01
  value=3
...

Bind#38
  oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=7c1eef40  bln=32  avl=06  flg=09
  value="^A"
~~~~~~~~~~~~~~~~~~ 
Bind#39
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=7c1eef5e  bln=07  avl=07  flg=09
  value="11/13/2017 7:1:7"
Bind#40
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fe734950188  bln=22  avl=04  flg=05
  value=90624

--//注意看下划线就是插入sprae4的值.

SCOTT@book> select  OBJ#,DATAOBJ#,spare1,spare2,spare3,spare4,dump(spare4,16) c30,spare5,spare6 from sys.tab$  where obj#=90624;
      OBJ#   DATAOBJ#     SPARE1     SPARE2     SPARE3 SPARE4     C30                            SPARE5     SPARE6
---------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ---------- --------------------
     90624      90624        736                        寠 k     Typ=1 Len=6: 1,0,8c,8a,0,6b                2017-11-13 07:01:07

--//不知道spare4的插入值的具体含义.可以知道仅仅与drop 字段有关.转储日志分析看看:

alter system dump logfile '/mnt/ramdisk/book/redo02.log' scn min 13277923577 scn max 13277943577;

$ egrep "col 33: \[ 6\]  01 00|col  0: \[ 4\]  c3 0a 07 19" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_49641.trc > /tmp/aa.txt
--//注 c3 0a 07 18 对应的是obj#字段
SCOTT@book> select dump(90624,16) from dual ;
DUMP(90624,16)
----------------------
Typ=2 Len=4: c3,a,7,19

--//检查/tmp/aa.txt文本,可以发现如下信息.
col  0: [ 4]  c3 0a 07 19
col 33: [ 6]  01 00 7a cb 00 0a
col  0: [ 4]  c3 0a 07 19
col 33: [ 6]  01 00 7a cb 00 0a
col  0: [ 4]  c3 0a 07 19
col 33: [ 6]  01 00 7a cb 00 14
col  0: [ 4]  c3 0a 07 19
col 33: [ 6]  01 00 7a cb 00 14
col  0: [ 4]  c3 0a 07 19
col 33: [ 6]  01 00 7a cb 00 1e
col  0: [ 4]  c3 0a 07 19
col 33: [ 6]  01 00 7a cb 00 1e
col  0: [ 4]  c3 0a 07 19
col 33: [ 6]  01 00 7a cb 00 28
col  0: [ 4]  c3 0a 07 19
col 33: [ 6]  01 00 7a cb 00 28
col  0: [ 4]  c3 0a 07 19
...

--//0x0a=10,0x14=20,1e=30,0x28=40.哈哈看出来了吗?这个就是每次提交的记录行号. 我是每10条一个提交,这样就很好猜测rowid之类的信息.
--//spare4记录下一条操作的记录rowid,我估计.
--//如果继续往下看

col  0: [ 4]  c3 0a 07 19
col 33: [ 6]  01 00 7a cb 01 0e
col  0: [ 4]  c3 0a 07 19
col 33: [ 6]  01 00 7a cc 00 01
col  0: [ 4]  c3 0a 07 19

--//这里跨块了,0x10e=270.
--//好了修复看看该表的rowid一切就清楚了.

SCOTT@book> ALTER TABLE t1 DROP COLUMNS CONTINUE;
Table altered.

SCOTT@book> select  OBJ#,DATAOBJ#,spare1,spare2,spare3,spare4,dump(spare4,16) c30,spare5,spare6 from sys.tab$  where obj#=90624;
        OBJ#     DATAOBJ#     SPARE1     SPARE2     SPARE3 SPARE4     C30                            SPARE5     SPARE6
------------ ------------ ---------- ---------- ---------- ---------- ------------------------------ ---------- --------------------
       90624        90624        736                                  NULL                                      2017-11-13 07:54:14
--//完成后spare4内容清空.

SCOTT@book> select rowid,t1.* from t1 where rownum<=2;
ROWID                        ID V2
------------------ ------------ ----------
AAAWIAAAEAAAHrLAAA          563 bbbbbbbbbb
AAAWIAAAEAAAHrLAAB          564 bbbbbbbbbb

SCOTT@book> @ &r/rowid AAAWIAAAEAAAHrLAAA
      OBJECT         FILE        BLOCK          ROW ROWID_DBA            DBA                  TEXT
------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
       90624            4        31435            0  0x1007ACB           4,31435              alter system dump datafile 4 block 31435

--//注意看ROWID_DBA,就是块地址.与前面的转储内容一直.可以猜测这块279条记录.因为下一个记录是"01 00 7a cc 00 01".
--//块dba=0x01007acc有1条已经提交了+加上剩下9条作为一个事务提交.验证看看.

SCOTT@book> select count(*) from t1 where rowid between 'AAAWIAAAEAAAHrLAAA' and 'AAAWIAAAEAAAHrLBBB';
    COUNT(*)
------------
         279

--//继续看下面也验证了猜测.
col  0: [ 4]  c3 0a 07 19
col 33: [ 6]  01 00 7a cc 01 0f
col  0: [ 4]  c3 0a 07 19
col 33: [ 6]  01 00 7a cd 00 02
col  0: [ 4]  c3 0a 07 19

总结:
1.ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <n>;
In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint
to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of
undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.

--//虽然减少undo的使用,但是如果中断或者中途退出,表无法查询与使用,必须执行ALTER TABLE <table_name> DROP COLUMNS CONTINUE;修复.
--//并且再使用checkpoint 参数.

--//oracle是通过修改相应sys.tab$表的spare4的值来确定这个工作是否完成,并且这里记录的是下一次要操作记录的rowid.

2.测试很幸运正好sys.tab$显示pare4线索异常, 最佳的方式应该是直接跟踪ALTER TABLE t1 DROP UNUSED COLUMNS CHECKPOINT 10;操作.走了一个弯路.^_^.

时间: 2024-11-06 09:29:17

[20171113]修改表结构删除列相关问题3.txt的相关文章

[20171113]修改表结构删除列相关问题2.txt

[20171113]修改表结构删除列相关问题2.txt --//测试看看修改表结构删除列产生的redo向量,对这些操作细节不了解,分析redo看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ----------------------------------------------

[20171113]修改表结构删除列相关问题4.txt

[20171113]修改表结构删除列相关问题4.txt --//连续写了3篇修改表结构删除列的相关问题,链接如下: http://blog.itpub.net/267265/viewspace-2147158/ http://blog.itpub.net/267265/viewspace-2147163/ http://blog.itpub.net/267265/viewspace-2147196/ --//从redo记录日志内容看,日志仅仅记录偏移位置 (piece relative colu

[20171113]修改表结构删除列相关问题.txt

[20171113]修改表结构删除列相关问题.txt --//维护表结构删除字段一般都是先 ALTER TABLE <table_name> SET UNUSED (<column_name>); --//然后等空闲时候删除列. ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <n>; --//参考文档: https://docs.oracle.com/cd/E11882_01/server.1

mysql修改表结构方法实例详解_Mysql

本文实例讲述了mysql修改表结构方法.分享给大家供大家参考.具体如下: mysql修改表结构使用ALTER TABLE语句,下面就为您详细介绍mysql修改表结构的语句写法,希望对您学习mysql修改表结构方面能有所帮助. ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...] alter_specification: ADD [COLUMN] create_definition [FIRST | AFTER column_nam

必须会的SQL语句(二) 创建表、修改表结构、删除表_MsSql

1.创建数据库表 --使用哪个数据库,如果不写这一句是默认的数据库,也可以用鼠标选当前数据库 use testDB --创建表 Create Table tablename ( --id表示字段名 --int 数据类型 --primary key 主键 --not null 非空 --identity(1,1)初始值是1 每次自增长1 id int primary key not null identity(1,1), --unique 唯一 name varchar(20) not null

看实例学VFP:用sql命令修改表结构

在看实例学VFP:用sql语句修改数据表记录一文中介绍过用sql语句修改表记录的值,实际vfp中也可以用sql语言中的Alter语句动态的修改表结构.虽然可以实现这样的操作,但是我觉得尽可能还是不要在程序中动态修改表结构,因为这样有可能会造成程序的不稳定以及其它的意外情况. vfp中对表结构的操作主要是增加字段.修改字段.重命名字段和删除字段这四项,sql中修改表结构的语句是Alter语句,那么和这四项操作相对应的Alter语句格式可以总结如下: 增加字段:alter talbe 表名 add

mysql alter table命令修改表结构实例详解_php实例

mysql alter table语句可以修改表的基本结构,例如添加字段.删除字段.添加主键.添加索引.修改字段数据类型.对表重命名等等操作,本文章通过两个简单的实例向大家介绍mysql alter table的使用方法.  实例一:使用ALTER TABLE命令向表中添加字段.修改字段类型以及设置主键. 首先创建一个表,SQL语句如下: mysql> CREATE TABLE myTable( -> ID SMALLINT -> ); 使用desc命令查看表结构: mysql>

mysql alter table命令修改表结构实例_Mysql

mysql实例之使用alter table命令修改表结构 mysql alter table语句可以修改表的基本结构,例如添加字段.删除字段.添加主键.添加索引.修改字段数据类型.对表重命名等等操作,本文章通过两个简单的实例向大家介绍mysql alter table的使用方法  实例一:使用ALTER TABLE命令向表中添加字段.修改字段类型以及设置主键. 首先创建一个表,SQL语句如下: mysql> CREATE TABLE myTable( -> ID SMALLINT ->

sqlserver 2008手工修改表结构,表不能保存的问题与解决方法_mssql2005

如果点击"保存文本文件"将会弹出保存文本文件的对话框口, 保存的文本文件中的内容是如下: /*    2010年4月5日0:34:53    用户:    服务器: LONGGEL    数据库: longgel    应用程序: */ ChildCaiClass 保存的对于我来说简直是没用的信息,只是记录了事务的发生时间和一些相关信息,结果这样操作了数据库的结构还是没能修改并保存,而是继续弹出上面的那个窗口,这下我就郁闷了. 点击"取消"却弹出 同样也是没有完成表