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里需要用到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')