风一程

导航

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:\APP\ADMINISTRATOR\ORADATA\ORCL\TJSD_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'

 

posted on 2020-12-27 21:11  风一程  阅读(72)  评论(0编辑  收藏  举报