oracle常用操作语句:
select * from all_tab_columns where table_name='jkfilerec'
执行后提示:未选定行
错误原因:区分大小写,将'jkfilerec'改为'JKFILEREC' ,问题解决
查询表空间信息
SELECT * FROM DBA_DATA_FILES;
oracle如何区分 64-bit/32bit
SQL> select * from v$version;
oracle清屏命令:
SQL>host cls
cmd清屏命令:cls
如何分辨某个用户是从哪台机器登陆ORACLE的
SELECT machine ,terminal FROM V$SESSION;
desc table_name 可以查询表的结构
select * from all_tab_columns where table_name='??' (表名区分大小写)
如何查询每个用户的权限
SELECT * FROM DBA_SYS_PRIVS;
怎么可以快速做一个和原表一样的备份表?
create table new_table as (select * from old_table);
怎样得到触发器、过程、函数的创建脚本
desc user_source
user_triggers
如何查看当前字符集
select nls_charset_name(to_number('0354','xxxx')) from dual;
如何查看oracle支持哪些字符集
select * from v$nls_valid_values where parameter='CHARACTERSET'
如何快速清空一个大表(将表里的数据清空,效率比delete高)
SQL>truncate table table_name;
创建表空间:
CREATE SMALLFILE TABLESPACE "JI110" DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLE\ji110.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
扩展表空间:
ALTER TABLESPACE "HANLIN" ADD DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLE\add20111101'
SIZE 100M AUTOEXTEND ON NEXT 10K MAXSIZE UNLIMITED
查看相关参数
sqlplus/nolog
conn sys/manager as sysdba
show parameter name
cmd远程连接数据库
conn sys/manager @192.168.1.105:1521/oracle as sysdba
创建用户并设置默认表空间、赋权
create user test identified by "test" default tablespace hanlin
grant connent,dba to test
查看网络服务名:
F:\oracle\product\10.2.0\db_1\NETWORK\ADMIN下的tnsnames.ora
EXP命令:
exp hlwl20110902/0 file=c:\backup
IMP命令:
create user testimp identifed by "0"
grant dba to testimp
imp testimp2/0 file=c:\backup\hlwl20110902.dmp full=y -------导入所有对象
imp testimp2/0 file=c:\backup\hlwl20110902.dmp tables=(jknoss) -------导入jknoss表