oracle数据库常用命令大全(精华版).docVIP

通用 字符集 select userenv(language) from dual; 服务端字符集： select nls_charset_name(1) from dual; select nls_charset_id(‘WE8MSWIN1252) from dual;? 查看版本 select * from v$version 查询约束 select table_name from user_constraints where constraint_name = FK236DF0421758F244; 表空间 //创建临时表空间 create temporary tablespace test_temp tempfile E:\oracle\product\10.2.0\oradata\testserver\test_temp01.dbf size 32m autoextend on next 32m maxsize 2048m extent management local; //创建数据表空间 create tablespace test_data logging datafile E:\oracle\product\10.2.0\oradata\testserver\test_data01.dbf size 32m autoextend on next 32m maxsize 2048m extent management local; //创建用户并指定表空间 create user username identified by password default tablespace test_data temporary tablespace test_temp; //给用户授予权限 grant connect,resource to username; /*更改表空间*/ alter table table_name move tablespace tablespace_name /*重建表索引*/ alter index index_name rebuild 批量删除 declare cursor cur1 is select table_name from dba_tables where owner=TEST; begin for cur2 in cur1 loop execute immediate drop table test.||cur2.table_name; end loop; end; --所有有表 select drop table || table_name ||;||chr(13)||chr(10) from user_tables; --所有序列 select drop sequence || sequence_name||;||chr(13)||chr(10) from user_sequences; 断开活动的用户 select * from v$session where schemaname=REPOSITORY alter system kill session 138,6526 Windows 启动 Linux Web控制台 启动：emctl start dbconsole 停止：emctl stop dbconsole 备份 export NLS_LANG=Simplified Chinese_China.ZHS16GBK export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 exp repository/ekingstar file=/home/oracle/repository.dmp owner=repository 增量备份：inctype=incremental 恢复 export NLS_LANG=Simplified Chinese_China.ZHS16GBK export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 imp repository/ekingstar file=/home/oracle/repositorydmp full=y 查看dmp字符集 cat repository.dmp |od -x|head -1|awk {print $2 $3}|cut -c 3-6? select nls_charset_name(to_number(0354,xxxx)) from dual; 如：0345查询结果ZHS16GBK? 删除在线用户 select * from v$session wher