Oracle 常用Sql 语句

　　Oracle数据库常常被用作项目开发的数据库之一；有时隔段时间没使用就会忘记一些常用的sql语法，所以我们有必要记录下常用的sql 语句，当我们需要时可以快速找到并运用。

1 创建表空间、创建用户及授权

1：创建临时表空间
create temporary tablespace user_temp
tempfile 'Q:\oracle\product\10.2.0\oradata\Test\xyrj_temp.dbf'
size 50m
autoextend on 
next 50m maxsize 20480m
extent management local;

2：创建数据表空间
create tablespace user_data
logging
datafile 'Q:\oracle\product\10.2.0\oradata\Test\xyrj_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
 
第3步：创建用户并指定表空间
create user username identified by password 
default tablespace user_data
temporary tablespace user_temp;
 
第4步：给用户授予权限
grant connect,resource,dba to username;

 

Oracle表空间之基本概念

ORACLE数据库被划分成称作为表空间的逻辑区域——形成ORACLE数据库的逻辑结构。一个ORACLE数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。表空间是ORACLE数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等

 

临时表空间作用：

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。像数据库中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临时表空间。当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。这里的释放只是标记为空闲、可以重用，其实实质占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因；

详细请参考http://www.cnblogs.com/kerrycode/p/4006840.html

 

2 导出exp 与导入imp 命令行说明

• exp导出数据

exp help=y 查看参数

1、全库方式,导出整个数据库中所有的对象，但并不包括sys用户中的对象

exp user/pwd@db_name full=y file=D:\database.dmp log=D:\database.log

 

2、用户方式：导出某一用户下所有的对象，授权了权限的用户可以导出其他用户所拥有的对象。作为全库导出的补充应用
exp user/pwd@db_name owner=(system,sys) file=D:\owner.dmp log=D:\owner.log

 

3、表方式：只导出某一用户下指定的表，而不是所有的表。
exp user/pwd@db_name tables=(tb1,tb2) file= D:\table.dmp

 

4、条件导出：将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp user/pwd@db_name tables=(table1) query=" where filed1 like '00%'" file=D:\query.dmp log=d:\query.log

只导出表前200行数据

exp user/pwd@db_name tables=(table1) query="'where rownum<=200'"

用这个语句查询你某一模式下的所有表

SELECT object_name||',' as user_object
from user_objects where object_type='TABLE';

• imp导入　　

　　同导出exp一样具有三种模式（完全、用户、表）　　　

　　1、全部导入　

　　　imp user/pwd@db_name  file=D:\database.dmp log=d:\impdatabase.log  full=y ignore=y

　　2、imp user/pwd@db_name  file=D:\database.dmp  FROMUSER=user TOUSER=user 
          这样用户user的所有对象被导入到文件中。必须指定FROMUSER、TOUSER参数，这样才能导入数据

　　3、选择表导入 将D:\table.dmp中的表table1 导入
　　　imp user/pwd@db_name file=D:\table.dmp log=d:\imptable.log tables=(table1) 

　　  如果源表已经存在，导入时报错。在后面加上 ignore=y 就可以了。ignore作用是忽略错误继续导入的意思；不少情况要先是将表彻底删除，然后导入。

 

Oracle创建directory

一般创建directory都是为了用数据泵导入/导出数据用

1、新建directory的语法
CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';

例如：
create or replace directory dump_dir as 'D:\dump\dir'
这样把目录d:\dump\dir设置成dump_dir代表的directory

2、查询有哪些directory
select * from dba_directories

oracle 删除某个用户下的所有对象

drop user user_name cascade;

加了cascade就可以把用户连带的数据全部删掉

 

创建Database links 跨库访问

create public database link SBLINK
  connect to DGTEST
  using '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )';

 

oracle存储过程访问其它用户表权限不足,为用户设置可以访问所有表的权限

GRANT SELECT ANY TABLE TO  user  WITH ADMIN OPTION

 

其他常用语句

查看某张表是否被其他对象引用

select * from user_dependencies  where referenced_name='表名'

这里表名是有区分大小写的，必须跟表名一致才能查到数据。

oracle查询某张表被其它表作为外键

select c.table_name,
       c.constraint_name,
       fc.table_name,
       fc.constraint_name,
       cc.column_name,
       fcc.column_name
  from user_cons_columns fcc,
       user_cons_columns cc,
       user_constraints  c,
       user_constraints  fc
 where fc.constraint_name = c.r_constraint_name
   and fc.constraint_name = fcc.constraint_name
   and c.constraint_name = cc.constraint_name
   and c.constraint_type = 'R'
   and  fc.table_name in ('tb_user')