1、备份数据表
--新建要备份表将需要备份的数据存储在备份表中
create table sys_user_20200314 as select * from sys_user
--已有备份表将需要备份的数据存储在备份表中
insert into sys_user_20200314 select * from sys_user
2、解锁/锁死
--解锁
SELECT object_name, machine, s.sid, s.serial# FROM gv$locked_object l, dba_objects o, gv$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid;
alter system kill session 'sid, serial#';
--锁死
LOCK TABLE tablename IN EXCLUSIVE MODE;
3、表操作
--创建表
create table tabelname(
ID number primary key,
name varchar2(500),
address varchar2(500),
creater varchar2(200),
create_time date,
updater varchar2(200),
update_time date,
del number
)
--添加注释
comment on column tablename.columnname is '注释信息';
-- 添加序列
create sequence SEQ_tabelname_ID
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;
--删除表
drop table tablename
truncate table tablename(不支持回滚,要注意)
--修改列名
alter table tablename rename column oldcolumn to newcilumn
--修改列类型
alter table tablename modify( column columndatatype)
--删除列:
alter table tablename drop column columnname
--增加列:
alter table tablename add columnname datatype
4、表数据操作(增删该查)
insert into tablename (col,col) values (val,val)
delete from tablename where 要删除的数据条件
update tablename set tablecolumn=xx where 要修改的数据条件
select * from tablename(*代表查询所有字段,可以单独写字段名查询单个字段,多个字段查询以“,”分隔)