Oracle 命令

#建立表空间
create tablespace sde_tbs
logging
datafile 'D:\app\Admin\oradata\eighth\sde_tbs_01.dbf' 
size 50m  
autoextend on  
next 50m maxsize 30480m  
extent management local; 


#建立缓存表空间
create temporary tablespace sde_tbs_temp  
tempfile 'D:\app\Admin\oradata\eighth\sde_tbs_temp_01.dbf' 
size 50m  
autoextend on  
next 50m maxsize 30480m  
extent management local; 




#创建用户并指定默认表空间
create user sde identified by root default tablespace sde_tbs temporary tablespace sde_tbs_temp;




#赋予用户DBA权限
grant dba to sde;


#创建逻辑文件夹（磁盘中必须有该文件夹，备份和恢复使用）
create directory bf as 'D:\Oracle\dmp\sdesixth';




#查询表空间大小
SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS 
FROM DBA_TABLESPACES T,DBA_DATA_FILES D 
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME 
ORDER BY TABLESPACE_NAME,FILE_NAME;








#删除用户
drop user bd cascade;




#删除表空间
drop tablespace seventh including contents and datafiles;




#删除缓存表空间
drop tablespace sde_tbs_temp including contents and datafiles;






#查询表空间
select tablespace_name from dba_tablespaces;






#查看表空间的使用情况 
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name 
FROM dba_free_space 
GROUP BY tablespace_name; 
SELECT a.tablespace_name, 
a.bytes total, 
b.bytes used, 
c.bytes free, 
(b.bytes * 100) / a.bytes "% USED ", 
(c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name;




#设置oracle中ST_SHAPELIB路径
create or replace library ST_SHAPELIB as 'C:\Program Files (x86)\ArcGIS\Desktop10.2\DatabaseSupport\Oracle\Windows64\st_shapelib.dll';


#查询ST_SHAPELLB的路径
select * from user_libraries;


#电脑名字
DESKTOP-6JH16C1


#项目使用数据库表名
beidou_poi_sot, beidou_line, beidou_surface, beidou_new_geosot, beidou_geosot(旧)




#导出数据
exp sde/root@eighth file=F:\sde\1eighth.dmp tables=(beidou_poi_sot, beidou_line, beidou_surface, beidou_new_geosot) 




#为表空间指定数据文件
alter tablespace tablespace_name
add datafile 'E:\oracle\product\10.2.0\oradata\orcl\user_data3.dbf'
size 50M;






#删除数据文件（最后删除数据文件）
alter database datafile 'D:\app\Admin\oradata\eighth\01_sde_tbs.dbf' offline drop;






#为某个表空间增加数据文件
alter tablespace sde_tbs add datafile 'D:\app\Admin\oradata\eighth\sde_tbs_03.dbf' size 50m
autoextend ON next 50m maxsize 34359721984;






#登录数据库
sqlplus sys/root@sixth as sysdba；


#导入数据
imp sde/root@eighth file="D:\数据\eighth.dmp"  ignore=y statistics=none tables=(beidou_poi_sot, beidou_line, beidou_surface, beidou_new_geosot)




#导出数据
exp sde/root@eighth file=F:\sde\1eighth.dmp tables=(beidou_poi_sot, beidou_line, beidou_surface, beidou_new_geosot) 






#sql sde函数查询语句
select count(*) resnum
FROM BEIDOU_SURFACE s
where SDE.st_contains(
(
SELECT
SDE.st_geomfromtext(
'POLYGON((116 39,116 40,117 40,117 39,116 39))',4326)
FROM DUAL
),s.GEOMET
) = 1