【Oracle实战经验二】：OracleSpatial实用资料整理

OracleSpatial实用资料整理

一.OracleSpatial授权方法

⒈用户A创建表后，将表的Select权限赋值给用户B

用户A创建的表的访问权限如何授权给用户B
– Create the user A
create user A
identified by “1”
default tablespace USERS
temporary tablespace TEMP
profile DEFAULT;
– Grant/Revoke role privileges
grant resource to A;
grant connect to A;
– Grant/Revoke system privileges
grant unlimited tablespace to A;

– Create the user B
create user B
identified by “1”
default tablespace USERS
temporary tablespace TEMP
profile DEFAULT;
– Grant/Revoke role privileges
grant connect to B;
grant resource to B;
– Grant/Revoke system privileges
grant unlimited tablespace to B;

– Create table A.table1
create table table1
(
ID long,
NAME varchar2(255)
)
;
– GRANT SELECT ON A.table1 TO B
GRANT SELECT ON A.TABLE1 TO B;

用户B如何查询自己有权限Select的所有表名信息
select U.OWNER||’.’||U.TABLE_NAME from user_tab_privs U where U.privilege = ‘SELECT’;

用户B如何查询自己创建的与自己有权限访问的表名集合
select up.OWNER||’.’||up.TABLE_NAME from user_tab_privs up,user_tables u where up.privilege = ‘SELECT’
union
select ‘B.’||u.table_name from user_tables u;

备注1：查询当前用户有权限访问的所有空间表
SELECT a.owner||’.’||a.table_name FROM ((SELECT table_name,owner FROM all_sdo_geom_metadata) INTERSECT (SELECT table_name,owner FROM all_tab_columns WHERE data_type=‘SDO_GEOMETRY’)) a order by a.owner
备注2：查询当前用户有权限访问的所有属性表
逻辑：其它用户创建且授权给我的非空间的表 + 我自己创建的非空间的表
select up.OWNER||’.’||up.TABLE_NAME from user_tab_privs up
where up.privilege = ‘SELECT’
AND up.OWNER||’.’||up.TABLE_NAME not in (select g.OWNER||’.’||g.TABLE_NAME from all_sdo_geom_metadata g)
AND up.OWNER||’.’||up.TABLE_NAME not in (select a.OWNER||’.’||a.object_name from all_objects a where a.object_type != ‘TABLE’)
union
select ‘DATA.’||u.table_name from user_tables u where u.TABLE_NAME not in (select g.TABLE_NAME from user_sdo_geom_metadata g);

二.OracleSpatial字符集

1. OracleSpatial创建数据库实例时，默认的字符集为：ZHS16GBK
2. 字符集查询方法
   –查询数据库服务端字符集
   select * from nls_database_parameters
   –查询数据库客户端字符集
   select * from nls_instance_parameters
   –修改数据库服务端字符集
   ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
3. ZHS16GBK与AL32UTF8字符集的差异
   如果是ZHS16GBK 表示一个中文占2个字节
   如果是AL32UTF8 表示一个中文占3个字节

三.OracleSpatial表空间

1、查询表空间使用情况
–查询表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) “表空间名”,
D.TOT_GROOTTE_MB “表空间大小(M)”,
D.TOT_GROOTTE_MB - F.TOTAL_BYTES “已使用空间(M)”,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),‘990.99’) || ‘%’ “使用比”,
F.TOTAL_BYTES “空闲空间(M)”,
F.MAX_BYTES “最大块(M)”
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;

–查询表空间的free space
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;

–查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;

–查询表空间使用率
select total.tablespace_name,
round(total.MB, 2) as Total_MB,考试大论坛
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || ‘%’ as Used_Pct
from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_free_space
group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name;

1、表空间创建、修改
–创建大文件表空间
create bigfile tablespace test_spa datafile ‘F:\app\Administrator\oradata\orcl\test_spa.dbf’ size 2g Autoextend on;
–修改大文件表空间大小
alter tablespace test_spa resize 1g;

–创建小文件表空间
create tablespace test_spa datafile ‘F:\app\Administrator\oradata\orcl\test_spa .dbf’ size 100m;
–修改小文件表空间大小
alter database datafile ‘F:\app\Administrator\oradata\orcl\test_spa .dbf’ resize 50m;

–修改表空间为只读状态
alter tablespace test_spa read only;
–修改表空间为读写状态
alter tablespace test_spa read write;

–重命名表空间名称
alter tablespace test_spa rename to test_spa_new;

四.SDO_GEOMETRY字段类型的空间索引（创建、查询、删除）

1、 创建空间索引
create index I2017111794156796 on BOU2_4M_S2(geometry) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS(‘SDO_COMMIT_INTERVAL=1000’);

2、 查询表对应的空间索引信息
select * from all_indexes t where t.table_name = ‘BOU2_4M_S2’ and t.ityp_name = ‘SPATIAL_INDEX’;

3、 空间索引状态查询
select t.index_name,t.ityp_name,t.status,t.table_owner,t.table_name from user_indexes t where t.ityp_name = ‘SPATIAL_INDEX’ and t.table_name = ‘表名’;
其中status为VALID表示有效，UNUSABLE表示无效，INPROGRS 表示创建过程中

4、空间索引删除

对于索引状态为INPROGRS 的空间索引删除，需要添加force关键字

对于VALID或UNUSABLE状态的索引，删除：DROP INDEX xxx;

对于INPROGRS状态的索引，删除：DROP INDEX XXX force;