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'