Oracle基本操作

 

SQL CREATE VIEW 语法

 

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

例：

 

CREATE OR REPLACE VIEW  TO_LDPS AS
  select TO_NUMBER(CONCAT(ID,'0')) AS ID, PSJB,BZ,TO_CHAR(CHUZHIGONGZUO_ID) as TYPE_ID,'chuzhigongzuo' as TYPE from TO_CHUZHIGONGZUO_LDPS
  union 
  select TO_NUMBER(CONCAT(ID,'1')) AS ID, PSJB,BZ,TO_CHAR(DAJICHULI_ID) as TYPE_ID,'dajichuli' as TYPE  from TO_DAJICHULI_LDPS 
  

 

 

 

 

增加一个用户的SQL

 

 

 

  -- Create the user 
create user IBS2USERZH
  identified by IBS2USERZH
  default tablespace IBSZH
  temporary tablespace TEMP
  profile DEFAULT;
-- Grant/Revoke role privileges 
grant connect to IBS2USERZH;
grant dba to IBS2USERZH;
grant resource to IBS2USERZH;
-- Grant/Revoke system privileges 
grant unlimited tablespace to IBS2USERZH;

 

 

增加一个数据库的服务命名

文件D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora    

 

plsql字符串函数

http://hi.baidu.com/yzcp558/blog/item/230b0def368f2d3eadafd551.html

 

for update

 

创建表空间

/*分为四步 */
/*第1步：创建临时表空间  */
create temporary tablespace user_temp  
tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  
 
/*第2步：创建数据表空间  */
create tablespace user_data  
logging  
datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  
 
/*第3步：创建用户并指定表空间  */
create user username identified by password  
default tablespace user_data  
temporary tablespace user_temp;  
 
/*第4步：给用户授予权限  */
grant connect,resource,dba to username;

 下面是一个例子：

 

/*分为四步 */
/*第1步：创建临时表空间  tempfile要是orcl的目录才有权限*/
create temporary tablespace user_temp  
tempfile 'D:\app\Administrator\oradata\orcl\user_temp.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  
 
/*第2步：创建数据表空间  */
create tablespace EMP_TASK  
logging  
datafile 'D:\app\Administrator\oradata\orcl\EMP_TASK.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  
 
/*第3步：创建用户并指定表空间  */
create user empTask identified by 123456  
default tablespace EMP_TASK  
temporary tablespace user_temp;  
 
/*第4步：给用户授予权限  */
grant connect,resource,dba to empTask;

 

 创建J2CMS

/*第2步：创建数据表空间  */  
create tablespace J2CMS    
logging    
datafile 'D:\app\Administrator\oradata\orcl\J2CMS.dbf'   
size 50m    
autoextend on    
next 50m maxsize 20480m    
extent management local;    
   
/*第3步：创建用户并指定表空间  */  
create user j2cms identified by password    
default tablespace J2CMS    
temporary tablespace user_temp;    
   
/*第4步：给用户授予权限  */  
grant connect,resource to j2cms;  

 

删除表空间

drop tablespace tbspaceName  INCLUDING CONTENTS;

删除用户

drop user USERNAME cascade;

 修改用户密码  (SYSDBA身份登陆)

 

SQL> alter user user01 identified by password;