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 = ))
(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')
上一篇:JS,删除数据时候,多次确认后才删除。


下一篇:Too Much Money