select * from tt as of timestamp to_date('2016-2-15 10:07:00','yyyy-MM-dd HH24:mi:ss') --查询某个时间点的数据
alter table tt enable row movement;--更改行级移动
flashback table tt to timestamp to_date('2016-2-15 10:07:00','yyyy-MM-dd HH24:mi:ss') --闪回某个时间的数据
flashback table tt to before drop;--回复删除的表
select dbms_flashback.get_system_change_number from dual; --查看SCN
select timestamp_to_scn(to_date('2015-02-25 08:00:00','yyyy-MM-dd hh24:mi:ss')) from dual; --时间转SCN
select to_char(scn_to_timestamp(1646776582),'yyyy-MM-dd hh24:mi:ss') from dual; --SCN转时间
select * from tt as of scn 1656766582; --查SCN时间的数据
select split('1,2,3',',') from dual --split返回Collection
select * from table(split('1,2,3',',')) ---查询Collection
create table user_info(userid number,username varchar2(50)); --建表
alter table user_info rename column user_id to user_code --修改表字段名
rename table_user to table_customer --修改表名
alter table table_user modify user_name varchar(50) --修改表字段的类型
insert into user_info (userid,username) select t.cusid,t.cusname from cus_info t --插入一堆数据
insert into user_info (userid,username) values (1,'张三') --插入一条数据
update user_info set userid=1,username='李四' where userid=2 --修改一条数据
update user_info set (userid,username) = (select cusid,cusname from cus_info) where status=0 --修改一堆数据
select * from testT t connect by prior t.id=t.parent start with t.id=1 --树形查询
drop index tt_index; --删除索引
create index tt_index on tt(tid,tname); --新建索引
drop sequence tt_seq; --删除序列
create sequence tt_seq --创建序列
minvalue 1
maxvalue 100
start with 1
increment by 1
nocache
drop view tt_view; --删除视图
create view tt_view as select t.tname from tt t --创建视图
select * from user_tables --查询当前用户的表
select * from user_sequences --查询当前用户的序列
select * from user_indexes --查询当前用户的索引
select * from user_views --查询当前用户的视图
select * from user_tablespaces --查询当前用户的表空间
select * from user_types --查询当前用户的type
select * from user_users --查询当前用户中的用户
select * from user_synonyms --查询当前用户的同义词
select * from user_jobs --查询当前用户的job
select * from user_workspaces --查询当前用户的工作空间
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id; ---查询表被锁住
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time; ---查询锁表的sid和serial#
alter system kill session'632,53'; ---删除锁表的进程,sid,seria#
select * from v$datafile; --数据文件
select * from v$controlfile; --控制文件
select * from v$logfile; --日志文件
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES table_name2(column_name) ON DELETE CASCADE; --级联删除
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES table_name2(column_name) ON DELETE SET NULL; --如果被引用的表数据删除,该字段设置为空
ALTER TABLE table_name DROP CONSTRAINT constraint_name; --删除表约束
简单oracle常用sql操作
最新推荐文章于 2021-04-14 22:05:20 发布