ORACLE常用SQL语句

查找表空间：

select * from dba_data_files;

创建表空间：

create tablespace test
logging 
datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\test.dbf' 
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local;

删除表空间：

DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES;

创建用户：

create user test identified by test
default tablespace test
TEMPORARY TABLESPACE TEMP;

分配权限：

grant connect,resource,dba to test;

删除用户：

drop user test cascade;

ORACLE分页：

SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM table_name) A WHERE ROWNUM <=10) WHERE RN >= 0

以下为数据导入导出命令：

imp test/test@localhost:1521/orcl file=c:\dmp\test.dmp log=c:\dmp\test.log full=y ignore=y

exp test/test@localhost:1521/orcl file=c:\dmp\test.dmp log=c:\dmp\test.log owner=test

导出部分表

exp test/test@localhost:1521/orcl tables=(table1,table2,table3) file=c:\dmp\test.dmp log=c:\dmp\test.log