oracle常用命令

--删除表中一列
alter table TJBG1.f_njbg drop column c13_test;

-- 给表增加一列
ALTER TABLE TJBG1.f_njbg ADD (c13_test int default -100 not null);
COMMENT ON COLUMN f_njbg.c13_test IS '13C尿素呼气试验,HP检查的一种';

-- oracle占连接数查询
select b.MACHINE, b.PROGRAM, b.USERNAME, count() from v$process a, v$session b
where a.ADDR = b.PADDR and b.USERNAME is not null
group by b.MACHINE, b.PROGRAM, b.USERNAME
order by count() desc

--修改密码
alter user tjbg1 identified by aaaabbbccc

--检查某个表的注释
select * from dba_col_comments where TABLE_NAME='F_NJBG'

-- 查某个表属于哪个用户
SELECT * FROM DBA_TABLES WHERE TABLE_NAME='YL_YJYCXXFK';

-- 查询视图属于哪个用户
select * from all_objects where object_type='VIEW' and object_name='VI_ZYBR_ALL'

--查询视图脚本
SELECT * FROM DBA_VIEWS WHERE VIEW_NAME = 'VI_ZYBR_ALL'

-- 查询建表脚本,需要有SELECT_CATALOG_ROLE权限

SELECT DBMS_METADATA.GET_DDL('TABLE','表名','用户名') FROM DUAL;

--建表时添加注释

CREATE TABLE developer.tcd_report
(
    exam_no           varchar2(100)              not null,
    ultrasound_no     varchar2(100)              not null,
    exam_time         int                        not null,
    exam_type         varchar2(100)              not null,
    chief_complaint   varchar2(500)  default ' ' not null,
    family_history    varchar2(500)  default ' ' not null,
    doctor_exam       varchar2(100)              not null,
    doctor_report     varchar2(100)              not null,
    report_time       int                        not null,
    imaging_finding   varchar2(4000)             not null,
    imaging_diagnosis varchar2(4000)             not null,
    pdf_url           varchar2(1000)             not null,
    primary key (exam_no)
);
COMMENT ON TABLE  developer.tcd_report                   IS 'TCD报告回传表';
COMMENT ON COLUMN developer.tcd_report.exam_no           IS '检查号';
COMMENT ON COLUMN developer.tcd_report.ultrasound_no     IS '超声号';
COMMENT ON COLUMN developer.tcd_report.exam_time         IS '检查时间,unix时间戳,单位为秒';
COMMENT ON COLUMN developer.tcd_report.exam_type         IS '检查类型';
COMMENT ON COLUMN developer.tcd_report.chief_complaint   IS '主诉';
COMMENT ON COLUMN developer.tcd_report.family_history    IS '家族史';
COMMENT ON COLUMN developer.tcd_report.doctor_exam       IS '检查医生';
COMMENT ON COLUMN developer.tcd_report.report_time       IS '检查时间';
COMMENT ON COLUMN developer.tcd_report.doctor_report     IS '报告医生(记录医生)';
COMMENT ON COLUMN developer.tcd_report.imaging_finding   IS '影像表现';
COMMENT ON COLUMN developer.tcd_report.imaging_diagnosis IS '影像表现诊断(影像学意见)';
COMMENT ON COLUMN developer.tcd_report.pdf_url           IS '报告的url';

查看表相关的触发器

SELECT trigger_name
FROM all_triggers
WHERE table_name = 'HSZ_BRSMTZ';

查看触发器内容

select text from all_source where type='TRIGGER' AND name='TR_HSZ_BRSMTZ_UPDATE'

时间戳转时期时间

SELECT TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(insert_time /1000, 'SECOND') AS datetime
FROM developer.report_base;