1.创建表空间
create tablespace SF1
datafile 'D:\oracle\product\10.2.0\oradata\gdsf2\SF101.dbf' size 10000M
autoextend on next 100M maxsize unlimited logging
extent management local autoallocate
segment space management auto;
2.为表空间追加文件
Alter tablespace SF1
Add datafile 'D:\oracle\product\10.2.0\oradata\gdsf2\SF102.dbf' size 5000m
3.为用户赋权
grant resource,connect to seu
4.导出数据
exp username/password@gdsf file=d:\output.dmp owner=(username)
5.导入数据
imp username/password@gdsf file=d:\imput.dmp full=y ingore=y
6.改变用户的默认表空间
alter user pju default tablespace sf1;
7.查看表空间大小
select a.tablespace_name,
used/1024/1024||'M' used,
round(100-b.free/used*100,2)||'%' used_pct,
round(free/1024/1024,2)||'M' free,
round(max_size/1024/1024)||'M' max_size,
round(max_size/1024/1024)-used/1024/1024||'M' "MAX_SIZE-TOTAL"
from
(select tablespace_name,sum(bytes) used,
sum(case when maxbytes=0 then bytes else maxbytes end) max_size
from dba_data_files
group by tablespace_name
)a,
(select tablespace_name,sum(bytes) free
from dba_free_space
group by tablespace_name
)b
where a.tablespace_name=b.tablespace_name;
8. SDE创建用户
-- Create the user
create user sf2domuser
identified by sf2domuser
default tablespace SDE_DOM1
temporary tablespace TEMP;
-- Grant/Revoke role privileges
grant connect to sf2domuser;
-- Grant/Revoke system privileges
grant administer database trigger to sf2domuser with admin option;
grant alter any index to sf2domuser with admin option;
grant alter any table to sf2domuser with admin option;
grant analyze any to sf2domuser with admin option;
grant create any index to sf2domuser with admin option;
grant create any table to sf2domuser with admin option;
grant create any procedure to sf2domuser with admin option;
grant create any sequence to sf2domuser with admin option;
grant create any trigger to sf2domuser with admin option;
grant create any view to sf2domuser with admin option;
grant create any indextype to sf2domuser with admin option;
grant create library to sf2domuser with admin option;
grant create operator to sf2domuser with admin option;
grant create public synonym to sf2domuser with admin option;
grant create session to sf2domuser with admin option;
grant create type to sf2domuser with admin option;
grant create view to sf2domuser with admin option;
grant drop any index to sf2domuser with admin option;
grant debug any procedure to sf2domuser with admin option;
grant drop any sequence to sf2domuser with admin option;
grant delete any table to sf2domuser with admin option;
grant drop any view to sf2domuser with admin option;
grant drop public synonym to sf2domuser with admin option;
grant select any table to sf2domuser with admin option;
grant select any sequence to sf2domuser with admin option;
grant unlimited tablespace to sf2domuser with admin option;
9.关联更新
UPDATE weatherforecast a
SET a.x = (SELECT b.centerx
FROM cityarea b
WHERE b.code = a.dscd)
WHERE EXISTS (SELECT 1 FROM cityarea c WHERE c.code = a.dscd)
select * from cityarea b,weatherforecast a where a.dscd = b.code
update weatherforecast a -- 使用别名
set x=(select b.centerx from cityarea b where b.code=a.dscd)
where exists (select 1
from cityarea b
where b.code=a.dscd
)
10.查找表中多余的重复记录
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
11.获取最新时间的记录集
select d.*
from (select b.*, a.stnm
from STU.ST_STBPRP_B a, stu.ST_RIVER_R1 b
where a.stcd = b.stcd
order by b.tm desc, a.stcd desc) d
where d.tm in (select max(c.tm)
from (select b.*, a.stnm
from STU.ST_STBPRP_B a, stu.ST_RIVER_R1 b
where a.stcd = b.stcd
order by b.tm desc, a.stcd desc) c
where c.stcd = d.stcd)
分享到:
相关推荐
Oracle Sql语句转换成Mysql Sql语句java 源码,非常简单,只要给定源oracle sql语句地址,和生成目标文件地址运行即可。
ORACLE常用SQL语句大全.pdf
Oracle常用sql语句整理 Oracle常用sql语句整理 Oracle常用sql语句整理 Oracle常用sql语句整理 Oracle常用sql语句整理 Oracle常用sql语句整理
oracle的SQL语句的一些经验总结,里边有很多大家和自己的东西。
oracle常用SQL语句下载 oracle常用SQL语句下载 oracle常用SQL语句下载
oracle常用sql语句,数据库常用的sql语句!!!!
Oracle学习,常用sql语句汇总,适合新手使用。
此LR脚本是应用于的Oracle的请求报文,此资源为直连Oracle执行SQL进行压测,为方便小伙伴快速开发此类的测试脚本,先已经整理成模板供小伙伴参考。
对ORACLE-SQL进行一些布局优化,更新它的格式
oracle常用sql语句大全 注释完整 放心使用 包含所有常用语句
Oracle SQL Profiler,自己设计算法写的一款非常好用的抓取Oracle数据库SQL语句的工具,可以再没有源码的情况下监控ORACLE数据库服务器的v$sqlarea视图抓取出从点击开始按钮到点击结束按钮期间执行过的SQL语句。...
Oracle中SQL语句执行效率的查找与解决..
Oracle——sql语句优化
ORACLE经典语句汇总 -- 字符串左填充和右填充,默认填充空格 -- 产生1~99行数据,少于一位则补0 -- 刪除相同行 -- 随机数 -- 产生业务流水号 -- 查询某张表中有哪些字段 -- 自循环表中 由叶子节点查父节点 -- 查子...
oracle、sql语句基础
Oracle中用sql语句创建数据库,比较长,一般做了解
ORACLE数据库SQL语句编写优化总结文档
oracle的sql语句和语法,初学者用用还不错,了解oracle的一些用法
oracle常用sql语句 oracle常用sql语句 oracle常用sql语句 oracle常用sql语句 oracle常用sql语句
Oracle数据库Sql语句详解大全,提供给大家快速查询复习哦!