删除用户: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命令)