oracle笔记

1.查看表空间大小

SELECT a.tablespace_name "bm", 
total / (1024 * 1024) "bkjdx(M)", 
free / (1024 * 1024) "表空间剩余大小(M)", 
(total - free) / (1024 * 1024 ) "表空间使用大小(M)", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name

2.查询表存放路径

select t1.name,t2.name 
from v$tablespace t1,v$datafile t2
where t1.ts# = t2.ts#;

3.扩展表空间大小（TJSD_DATA为表名bm）

ALTER TABLESPACE TJSD_DATA ADD DATAFILE
'D:APPADMINISTRATORORADATAORCLTJSD_DATA4.DBF' 
size 20260m autoextend on ;

4.表授权

grant select,insert,delete,update on ditu_jzw_layer to dgzykc;

5.创建MDSYS.SDO_GEOMETRY空间索引

新建geom字段，类型MDSYS.SDO_GEOMETRY

插入元数据

insert into user_sdo_geom_metadata(table_name,COLUMN_NAME, DIMINFO, SRID)
values(
'SDE.DITU_OBD_INFO_1',
'GEOM',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X',-180,180,0.005), 
MDSYS.SDO_DIM_ELEMENT('Y',-90,90,0.005)
),
4326 
);

创建空间索引

create index dgobd_01 on sde.ditu_obd_info_1(GEOM) indextype is mdsys.spatial_index;

插入点数据

update sde.ditu_obd_info_1  set geom= MDSYS.SDO_GEOMETRY(2001,4326,MDSYS.SDO_POINT_TYPE(lng, lat, 0),NULL,NULL);

插入面数据

insert into ditu_map_polygon_log(id,geom,create_time)
select sys_guid(),MDSYS.SDO_GEOMETRY
(2003,4326,NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1),
SDO_ORDINATE_ARRAY(面的几何数据)
),sysdate from dual;

查询两个表的相交数据--点与面相交

select r.address,r.zl_address from
yq_dp_dg_address_part partition(DGGOVPARTGC) r,ditu_map_polygon_log g
where g.id='C35490181AFF4F859FB6EDD80A65C831'
and sdo_relate(r.geom,g.geom,'MASK=INSIDE')='TRUE'5.创建MDSYS.SDO_GEOMETRY空间索引

6.oracle 撤回已提交的事务

查询视图v$sqlarea，找到你操作那条SQL的时间。SQL_TEXT就是执行的SQL语句

select r.FIRST_LOAD_TIME,r.* from v$sqlarea r order by r.FIRST_LOAD_TIME desc //查询视图

查找到你执行的那条SQL语句并找到对应的时间，然后执行以下语句

alter table 表名 enable row movement;
flashback table 表名 to timestamp to_timestamp('2021-01-08 16:57:17', 'yyyy-mm-dd hh24:mi:ss');

7.查看锁表SQL

select t2.username oracle用户名,
       t2.sid sid进程号,
       t2.serial# serial#序列号,
       t3.object_name 表名,
       t2.OSUSER 操作系统用户名,
       t2.MACHINE 机器名,
       t2.PROGRAM 操作工具,
       t2.LOGON_TIME 登陆时间,
       t1.LOCKED_MODE 锁表模式
  from v$locked_object t1, v$session t2, dba_objects t3
 where t1.session_id = t2.sid
   and t1.object_id = t3.object_id;
   
     alter system kill session '1546,2980'