ORACLE常用语句

删除用户：drop user gdgs cascade;

 

删除表空间以及物理文件

drop tablespace tablespace_name including contents and datafiles cascade constraints;

 

 

创建表空间：

CREATE TABLESPACE "TS_PUBLICDATA" 

    LOGGING 

    DATAFILE 'C:\app\Administrator\product\11.2.0\TS_PUBLICDATA.ora' SIZE 200M 

    AUTOEXTEND 

    ON NEXT  100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL 

    SEGMENT SPACE MANAGEMENT AUTO ;

 

创建用户

CREATE USER publicdata  PROFILE "DEFAULT" 

    IDENTIFIED BY publicdata DEFAULT TABLESPACE "TS_PUBLICDATA" 

    ACCOUNT UNLOCK;

GRANT "CONNECT" TO publicdata;

GRANT "RESOURCE" TO publicdata;

grant create view to publicdata;

 

 

赋予DBA权限：

grant dba to publicdata;

 

删除用户：

drop user test cascade

 

 

导入dmp

imp 'test/test@orcl' rows=y indexes=n buffer=65536 feedback=100000 full=y file=C:\SQL\test.dmp

 

如果出现版本不统一，需要使用dba权限

grant dba to test;

 

 

建立索引 提高查询速度

 

CREATE  UNIQUE index WQQ_LS_ID  on MEMSDB.WQQ_LS(ID);

create index WQQ_LS_X on MEMSDB.WQQ_LS(POINT_X);

create index WQQ_LS_Y on MEMSDB.WQQ_LS(POINT_Y);

 

聚合索引

 create index idx_Stuname on student(name,sex,grade)

注意：一个基表不能建太多的索引；
      空值不能被索引
      只有唯一索引才真正提高速度,一般的索引只能提高30%左右。

 

注：提高查询速度的方法还有在表上建立主键，主键与唯一索引的差别
在于唯一索引可以空，主键为非空

 

例1：为EMP表的ename 列建立大写转换函数的索引idx ：

 

CREATE INDEX idx ON emp ( UPPER(ename));

 

这样就可以在查询语句来使用：

 

SELECT * FROM EMP WHERE UPPER(ename) LIKE ‘JOH%’;

 

 

 

更改字段名称

EXEC sp_rename '表名.[原列名]','目标列名','COLUMN'; 

 

 

 更改字段类型

ALTER TABLE 表名 ALTER COLUMN   F_PIPELODID VARCHAR(32)

 

 

 

表关联更新update

 

 

UPDATE DSS_INFO

SET

    DSS_INFO.X  =  (SELECT  POINT_X  FROM  WQQ_LS  WHERE   WQQ_LS.id = DSS_INFO.DSS_ID)

    ,

    DSS_INFO.Y = (SELECT  POINT_Y  FROM  WQQ_LS  WHERE   WQQ_LS.id = DSS_INFO.DSS_ID)

WHERE

  EXISTS ( SELECT 1 FROM    WQQ_LS WHERE   WQQ_LS.id = DSS_INFO.DSS_ID)

 

 

 

 

查询所有根节点

SELECT FILE_CATALOGUE_ID from BASE_FILE_CATALOGUE where connect_by_isleaf=1 

start with parent_id IS NULL 

connect BY prior file_catalogue_id = parent_id

 

 

查询指定几条路段共同拥有的文件

SELECT FILE_ENTITY_ID,FILE_ENTITY_NAME,FILE_ENTITY_USER,FILE_ENTITY_FORMAT,FILE_ENTITY_SIZE,

FILE_ENTITY_DATE

FROM BASE_FILE_ENTITY WHERE FILE_ENTITY_ID IN (

SELECT a.FILE_ENTITY_ID  

FROM BASE_REL_FILE_ROUTE_INTRVL_PHY a INNER JOIN BASE_FILE_ENTITY b   

ON a.FILE_ENTITY_ID = b.FILE_ENTITY_ID 

where a.INTRVL_ID 

in ('DD3BB70A-B3ED-447E-B17A-E92D97601BA6','53EE0501-33FA-40D9-BD67-919D490D0127')

GROUP BY a.FILE_ENTITY_ID HAVING count(DISTINCT a.INTRVL_ID) = 2

)

 

oracle11g无法导出空表解决方案

1.用sys用户登录oracle数据库

执行：alter system set deferred_segment_creation=false;  去除segment机制

然后重新启动oracle 服务。

对之前的空表不会生效，所以需要手动更改以前的segment

2. 更改之前空空表的segment机制

select table_name from user_tables where NUM_ROWS=0 OR NUM_ROWS IS NULL 

生成对应的更改语句select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null;

然后执行生成的sql语句，之前的空表就分配了segment空间

最后导出dmp文件：（需安装服务器端oracle，否则不识别exp命令）

 

1.输入cmd 

2.执行：exp platform/platform@orcl file=c:\expdata\platform.dmp owner=platform compress=y 

 

 

 

多用户创建视图，权限不足

 

 

 

 

 

GRANT CREATE ANY TABLE TO GDGS;

       GRANT SELECT ANY TABLE TO GDGS;

       GRANT COMMENT ANY TABLE TO GDGS;

       GRANT LOCK ANY TABLE TO GDGS;

       GRANT SELECT ANY DICTIONARY TO GDGS;

 

 

GRANT CREATE ANY TABLE TO PMSDB;

       GRANT SELECT ANY TABLE TO PMSDB;

       GRANT COMMENT ANY TABLE TO PMSDB;

       GRANT LOCK ANY TABLE TO PMSDB;

       GRANT SELECT ANY DICTIONARY TO PMSDB;