Oracle常用操作

1、创建表空间

1）临时表空间

create temporary tablespace user_temp tempfile 'D:\oracle\oradata\user_temp.dbf'

size 50m autoextend on next 50m maxsize 20480m;

2）数据表空间

create tablespace test_data datafile 'D:\oracle\oradata\user_data_01.dbf'

size 50m autoextend on next 50m maxsize 20480m;

alter tablespace test_data add datafile 'D:\oracle\oradata\user_data_02.dbf' 

size 50m autoextend on next 50m maxsize 20480m;

3）删除表空间

drop tablespace test_data including contents and datafiles;

2、创建用户

1）默认表空间

create user username identified by password;

2）指定表空间

create user username identified by password

default tablespace user_data

temporary tablespace user_temp;

3）分配权限

grant connect,resource,dba to username;

4）收回权限

revoke dba from username;

5）删除用户及用户对象

drop user username cascade;

3、数据库导入导出

1）导出

exp test/test owner=test file=test.dmp

exp test/test owner=test file=test.dmp rows=N

exp test/test file=test.dmp tables=test.table1,test.table2

2）导入

imp test/test fromuser=test touser=test file=test.dmp

imp test/test fromuser=test touser=test file=test.dmp rows=N

imp test/test fromuser=test touser=test file=test.dmp ignore=y tables=test.table1,test.table2

4、冷备份

select name from v$datafile;

select name from v$controlfile;

select member from v$logfile;

备份$ORACLE_BASE/oradata/SID/目录下所有文件

备份$ORACLE_HOME/dbs/init.ora参数配置文件

5、数据库错误日志

$oracle_home\admin\SID\bdump\alert.log

6、SQL语句

select table_name from user_tables;

drop table tablename cascade constraints;

select * from nls_instance_parameters;

select * from nls_database_parameters;

select * from nls_session_parameters;

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff3';

select round(to_number(end_date - start_date) * 24 * 60 * 60) from dual;

select * from rtdmodule order by dbms_random.value;

set define off;（插入&转义符）

select file_id,tablespace_name,file_name,bytes from dba_data_files;

select tablespace_name,sum(bytes) from dba_free_space group by tablespace_name;

select segment_name, bytes from user_segments where segment_type = 'TABLE';

select 'drop table ' || table_name || ' cascade constraints;' from user_tables where table_name like '%2011%'; 

select 'drop sequence ' || sequence_name || ';' from user_sequences where sequence_name like '%2011%'; 

7、环境变量

1）导入导出数据时

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

2）后台程序中文

export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

8、oracle的rownum与sybase的top n的区别，oracle没有top n的语法

SELECT * FROM (SELECT * FROM ... ORDER BY ...) 

WHERE ROWNUM <= n 

ORDER BY ROWNUM ASC