Oracle常用操作

 

创建表空间
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;

 

创建用户
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;

 

数据库导入导出
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

 

冷备份
select name from v$datafile;
select name from v$controlfile;
select member from v$logfile;
备份$ORACLE_BASE/oradata/SID/目录下所有文件
备份$ORACLE_HOME/dbs/init.ora参数配置文件

 

数据库错误日志
$oracle_home\admin\SID\bdump\alert.log

 

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%';

 

环境变量
1）导入导出数据时
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
2）后台程序中文
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

 

oracle的rownum与sybase的top n的区别，oracle没有top n的语法
SELECT * FROM (SELECT * FROM ... ORDER BY ...) WHERE ROWNUM <= n ORDER BY ROWNUM ASC