Oracle数据库命令操作

添加字段

-- 有默认值
ALTER TABLE PRO_PROVIDER_INFO ADD(IS_OPNE_OMS NUMBER(1) DEFAULT 0 NOT NULL);
-- 无默认值
ALTER TABLE SETTLE_BILL_PT ADD(MARK_ID VARCHAR2(50) NULL);
COMMENT ON COLUMN SETTLE_BILL_PT.MARK_ID IS '第三方申请单号';

修改字段

ALTER TABLE INVOICE MODIFY INVOICE_PRICE NUMBER(22,5);

添加索引

CREATE INDEX IDX_ORDER_MX1 ON ORDER_MX(ORDER_ID);
CREATE INDEX IDX_ORDER_ADDRESS1 ON ORDER_ADDRESS(ORDER_ID);

释放表空间

-- 释放操作日志表数据
CREATE TABLE TRACK_LOG_210714 AS SELECT * FROM TRACK_LOG;
DELETE FROM TRACK_LOG;
INSERT INTO TRACK_LOG_210714 SELECT * FROM TRACK_LOG;
DROP TABLE TRACK_LOG_210714 ;

锁表查询

SELECT L.SESSION_ID SID,
       S.SERIAL#,
       L.LOCKED_MODE,
       L.ORACLE_USERNAME,
       L.OS_USER_NAME,
       S.MACHINE,
       S.TERMINAL,
       O.OBJECT_NAME,
       S.LOGON_TIME
  FROM V$LOCKED_OBJECT L, ALL_OBJECTS O, V$SESSION S
 WHERE L.OBJECT_ID = O.OBJECT_ID
   AND L.SESSION_ID = S.SID
 ORDER BY SID, S.SERIAL#;