【数据库】Oracle常用DDL语句整理(删除表、修改表名、修改表注释、表主键等、新增列、修改列等操作)

1.1 Oracle常用DDL语句整理

1.1.1、删除表

drop table ARCH_MG.ORDER_APP_FORM;

1.1.2、修改表名

修改表名有两种方式，我们这里就只写一种常用方式

ALTER TABLE SRV_MG.SRV_MONI_FILE RENAME TO SRV_S_FILE;

1.1.3、表添加列

ALTER TABLE ARCH_MG.ARCH_USER_EXPAND ADD USER_NO1 NUMBER(16);

1.1.4、表删除列

ALTER TABLE ARCH_USER_EXPAND DROP COLUMN USER_NO;

1.1.5、修改表列类型

当不同类型修改的时候，该字段有数据情况将会修改失败，针对这种情况我们需要新建一个差不多字段，把需要修改字段的值赋值给新字段，然后删除旧字段，新字段重新修改名字和以前保持一致

ALTER TABLE BIZ_PROJECT_INFO MODIFY PRO_NAME VARCHAR2(128);

如下面情况则是从VARCHAR2转换到NUMBER类型，是需要一个中间过程，要不然无法修改成功

ALTER TABLE ARCH_MG.ARCH_USER_EXPAND ADD USER_NO1 NUMBER(16);
UPDATE ARCH_MG.ARCH_USER_EXPAND SET USER_NO1= USER_NO;
ALTER TABLE ARCH_USER_EXPAND DROP COLUMN USER_NO;
ALTER TABLE ARCH_USER_EXPAND RENAME COLUMN USER_NO1 TO USER_NO;

如果是TIMESTAMP转DATE类型则不需要中转，能直接转换成功

ALTER TABLE ARCH_MG.ARCH_LOGIN_LOG MODIFY CREATETIME  DATE;

1.1.6、修改表列名

ALTER TABLE BIZ_PROJECT_INFO RENAME COLUMN PRO_NAME TO PROJECT_NAME;

1.1.7、修改表列注释

COMMENT ON COLUMN ARCH_MG.SYS_METER.TOTAL_METER_TYPE IS '总表类型 码值：TOTAL_METER_TYPE(1010501)';

1.1.8、修改表注释

COMMENT ON TABLE BIZ_MG.BIZ_USER_SIGN_SUB IS '业务应用-用户签约';

1.1.9 表添加主键

ALTER TABLE BIZ_MG.BIZ_USER_EXEC_LOG ADD CONSTRAINT PK_BIZ_USER_EXEC_LOG PRIMARY KEY (TASK_ID);

1.1.10 删除表主键

ALTER TABLE BIZ_MG.BIZ_USER_EXEC_LOG DROP PRIMARY KEY

1.2 Oracle同义词

1.2.1 创建同义词

大部分情况下我们都是创建公有同义词，私有同义词只能在其模式内使用而且不能和当前模式的对象同名，使用场景比较少;创建同义词需要权限

CREATE OR REPLACE PUBLIC SYNONYM ANY_USER_OBJ FOR ANY_MG.ANY_USER_OBJ;

1.2.2 删除同义词

当表被删除的时候同时需要删除同义词，否则再去查询的时候就会出现同义词循环连；删除同义词同样需要权限

DROP PUBLIC SYNONYM BIZ_REAL_CONS;

1.2.3 同义词赋权

一般是MG赋权给本域下CRUD帐号增删查改权限，其他域查询权限

GRANT SELECT,INSERT,UPDATE,DELETE ON ANY_USER_OBJ TO ANY_CRUD;
GRANT SELECT ON ANY_USER_OBJ TO SRV_CRUD;
GRANT SELECT ON ANY_USER_OBJ TO ARCH_CRUD;
GRANT SELECT ON ANY_USER_OBJ TO BIZ_CRUD;

1.3 查询数据库中表无注释

SELECT TABLE_NAME,
       COMMENTS
  FROM ALL_TAB_COMMENTS 
 WHERE TABLE_TYPE='TABLE'
   AND OWNER IN ('用户1','用户2','用户3','用户4','用户5') 
   AND COMMENTS IS NULL 

1.4 查询数据库中表字段无注释

SELECT TABLE_NAME,
       COLUMN_NAME,
       COMMENTS
  FROM ALL_COL_COMMENTS 
 WHERE OWNER IN ('用户1','用户2','用户3','用户4','用户5') 
  AND COMMENTS IS NULL 
  AND TABLE_NAME NOT LIKE '%==$0'
ORDER BY OWNER,TABLE_NAME DESC

1.5 查询数据库中无主键表

select owner, table_name
from dba_tables a
where not exists(select *
                 from dba_constraints b
                 where a.table_name = b.table_name
                   and constraint_type = 'P'
                   and owner in ('用户1','用户2','用户3','用户4','用户5') )
  and owner in ('用户1','用户2','用户3','用户4','用户5') ;