oracle常见语句
----本用户所拥有的系统权限:
select * from user_sys_privs;
---本用户读取其他用户对象的权限:
select * from user_tab_privs;
-----添加权限
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO 用户名(accor)
-----创建临时表空间
create temporary tablespace accor_ts
tempfile 'd:\accorDB' ----‘数据文件存放路径’
size 32M ----文件大小
autoextend on
next 32M maxsize 2048M
extent management local;
----创建表空间
create tablespace accor_test
datafile 'd:\accorDataBase' ----‘数据文件存放路径’
size 32M ----文件大小
autoextend on
next 32M maxsize 2048M
extent management local;
-----创建用户
create user accor identified by accor(用户密码)
default tablespace accor_test -----默认表空间
temporary tablespace accor_ts -----临时表空间
----删除用户
---- 用户下存在对象,表示要连带对象一起删除
drop user accor cascade;
---- 最快的办法是:
conn /as sysdba
delete from user$ where name='dzysc';
commit;
alter system flush shared_pool;
----给用户授权
grant connect,resource,create session,dba to accor
--更换用户登陆 disconnect
conn username/password@实例ID
select * from user_sys_privs;
导出命令:
exp uat_acc/uat_acc@ACCOR file=./uat_accor_0429.dmp owner=uat_acc;
导入命令:
imp uat_acc2/uat_acc2@ACCOR file=./test_acc_0413.dmp fromuser=uat_acc2 touser=uat_acc2;
相关文档:
author:skate
time:2010-05-25
数据文件管理
1. datafile add
2. datafile delete
3. datafile resize
4. datafile rename
1. datafile add
添加表空间,从而添加datafile
SQL> create tablespace tbs_skate
2 datafile '+datagroup' size 20m;
Tablespace created
SQL> sel ......
要创建两个文件
1: runBatch.bat
2: sql.txt
runBatch.bat 内容如下:
sqlplus username/password @sql.txt
pause
sql.txt内容如下:
spool sql.log
create table t1(cname char(20));
insert into t1(cname) values('test');
select * from t1;
spool off
exit
双击runBatch.bat就可以批量执行sql.txt中 ......
Yes, sure ! You must be the local admin of this computer, and not a domain user.
Moreover, I'm afraid you cannot install it in a domain, maybe you should (just for testing purposes) try to install a standalone Windows system and try installing Oracle 10g on it.
You know, a RDBMS database server mu ......
字符函数
下面是Oracle支持的字符函数和它们的Microsoft SQL Server等价函数。
函数 Oracle Microsoft SQL Server
把字符转换为ASCII :ASCII ASCII
字串连接: CONCAT --------------(expression + expression)
把ASCII转换为字符 CHR, CHAR
返回字符串中的开始字符(左起) INSTR ,---------------CHARINDEX
......
今天碰到一个问题,通过一个SQL语句查询时,出如下问题:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 30
问题出现在通过WMSYS. ......