oracle创建表空间,用户,表等常用操作

创建表空间:

--创建表空间：
create tablespace test_tablespace
   datafile 'E:\oracleDB\test.dbf' 
   size 500m;

--创建用户：
create user test
    identified by test
    default tablespace test_tablespace;

--给用户赋权限：
grant dba to test;

如果建立好用户之后.  不想给用户太多权限.  就可以这样写.

--创建用户
create user test
identified by test
default tablespace test_tablespace;

--让用户可以差看表等数据.
ALTER USER test QUOTA UNLIMITED ON test_tablespace;

--给用户赋两个基本权限.
GRANT CONNECT, RESOURCE TO test;

建立好了用户, 肯定就可能要删除用户:

--删除用户
drop user test cascade; 

修改密码:

alter user system identified by manager;

修改用户名:

    修改用户名时必须, 使用sysdba的连接登录才能修改用户名.  如果使用JDBC连接, 就必须要这样写才能使用sysdba连接.

Class.forName("驱动名");
Properties conProps = new Properties();
conProps.put("user", "用户名");
conProps.put("password", "密码");
conProps.put("defaultRowPrefetch", "15");
conProps.put("internal_logon", "sysdba");
conn = DriverManager.getConnection("连接URL", conProps);

  SQL修改语句.

--老用户名一定是大写的. 因为oracle的数据库里保存的都是大写的. 所以新用户名也因该大写.
update user$ set name = '新用户名' where name = '老用户名';
--刷新数据库.
alter system checkpoint;	
alter system flush shared_pool;
--修改了用户名之后, 密码也失效了. 所以必须修改一下密码. 此处是没有引号的.
alter user 用户名 identified by 密码; 

创建表:

create table TEST(
       TEST_ID number(10) PRIMARY KEY,   --建立主键.
       TEST_NAME varchar2(20) default 'XXXX' not null,   --建立默认值. 字段不能为空.
);

comment on table TEST is '测试表';  --对表的描述.
comment on column TEST.TEST_NAME is '用户名';    --对字段的描述.

创建序列:

create sequence SEQ_TEST_ID      --序列ID
minvalue 1                       --最小序列
maxvalue 9999999999999999        --最大序列
start with 1                     --开始
increment by 1                   --增长大小
cache 8;			 --高速缓存

新增语句:

INSERT INTO 表名称 VALUES (值1, 值2,....)

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

删除语句:

DELETE FROM 表名称 WHERE 列名称 = 值

DELETE FROM Person WHERE LastName = 'Wilson' 

查询语句:

SELECT 列名称 FROM 表名称

select userName, userPwd from test
         where test.id = 0;

修改语句:

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson' 

查询MD5

--语句查询.
select Utl_Raw.Cast_To_Raw(sys.dbms_obfuscation_toolkit.md5(input_string => 'userPwd'))
from dual

GUID:

select sys_guid() from dual

查询数据库用户:

select * from dba_users; --查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,system
select * from all_users;  --查看你能管理的所有用户！
select * from user_users; --查看当前用户信息 ！

查询数据库表:

select table_name from user_tables;

查看表空间大小

SELECT
  tablespace_name as 表空间,
  round((sum_alloc - nvl(sum_free,0))/1024/1024,1) as 已用空间M,
  round(sum_max/1024/1024,1) as 总大小M,
  round(100*(sum_alloc - nvl(sum_free,0))/sum_max,1) As 使用百分比
FROM 
  ( SELECT 
      tablespace_name, 
      sum(bytes) AS sum_alloc,
      sum(decode(maxbytes,0,bytes,maxbytes)) AS sum_max
    FROM dba_data_files
    GROUP BY 
      tablespace_name
  ),
  ( SELECT 
      tablespace_name AS fs_ts_name, 
      sum(bytes) AS sum_free
    FROM dba_free_space
    GROUP BY tablespace_name 
  )
WHERE 
  tablespace_name = fs_ts_name(+);

多条件判断的sql：

select
        oper.opid,  
        oper.user_name,  
        oper.user_host,  
        case   
        when oper.oper_type = 1  then 'System Manager'   
        else oper.oper_type = 2  then 'USER Manager'  
        end opertype,                 --别名  
        case  
        when oper.oper_object_type = 1 then 'User'  
        when oper.oper_object_type = 2 then 'Role'  
        when oper.oper_object_type = 3 then 'Broker'  
        when oper.oper_object_type = 4 then 'QM Manager'  
        when oper.oper_object_type = 5 then 'User Group'  
        when oper.oper_object_type = 6 then 'Msg Flow'  
        when oper.oper_object_type = 7 then 'Queue'  
        end objecttype             --别名.  
 from esb_log_user_oper oper;  

    ORACLE数据字典视图的种类分别为：USER,ALL 和 DBA.
    
    USER_*:有关用户所拥有的对象信息，即用户自己创建的对象信息
    
    ALL_*:有关用户可以访问的对象的信息，即用户自己创建的对象的信息加上
    
    其他用户创建的对象但该用户有权访问的信息
    
    DBA_*:有关整个数据库中对象的信息
    
    （这里的*可以为TABLES,INDEXES,OBJECTS,USERS等。）
    
    1、查看所有用户
    
    select * from dba_user;
    
    select * from all_users;
    
    select * from user_users;
    
    2、查看用户系统权限
    
    select * from dba_sys_privs;
    
    select * from all_sys_privs;
    
    select * from user_sys_privs;
    
    3、查看用户对象权限
    
    select * from dba_tab_privs;
    
    select * from all_tab_privs;
    
    select * from user_tab_privs;
    
    4、查看所有角色
    
    select * from dba_roles;
    
    5、查看用户所拥有的角色
    
    select * from dba_role_privs;
    
    select * from user_role_privs;
    
    6、查看当前用户的缺省表空间
    
    select username,default_tablespace from user_users;
    
    7、查看某个角色的具体权限
    
    如grant connect,resource,create session,create view to TEST;
    
    8、查看RESOURCE具有那些权限
    
    用SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE';

获取用户有的表的列名: 

SELECT * from USER_TAB_COLUMNS t;

查看用户对哪些表的列有操作权限:

SELECT * FROM user_col_privs t;

用户对某些表列进行添加权限. 注意, select权限是没有列权限的. select是通过视图来控制. 而没有使用权限. 

既: grant select(列名) on 表明 to 用户   会报错.

grant update(列名) on 表明 to 用户 而使用Update则不会报错.

获取当前登录用户:

select user from dual;

将表的权限授予全体用户:

--public表示是所有的用户，这里的all权限不包括drop
grant all on product to public;

union/union all运算：将查询的返回组合成一个结果， union all不过滤重复。

[sql] view plaincopy

1. SELECT product_id FROM order_items  
2. UNION  
3. SELECT product_id FROM inventories;  
4.   
5. SELECT location_id  FROM locations   
6. UNION ALL   
7. SELECT location_id  FROM departments;  

intersect运算：返回查询结果中相同的部分。

[sql] view plaincopy

1. SELECT product_id FROM inventories  
2. INTERSECT  
3. SELECT product_id FROM order_items;  

minus运算：返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。

[sql] view plaincopy

1. SELECT product_id FROM inventories  
2. MINUS  

oracle回收站, 设置开关:

alter session set recyclebin=off;
alter system set recyclebin=off;

Session查询与杀死:

--查询
SELECT OBJECT_ID,SESSION_ID,SERIAL#, ORACLE_USERNAME,OS_USER_NAME,S.PROCESS 
FROM V$LOCKED_OBJECT A, V$SESSION S 
WHERE A.SESSION_ID=S.SID;

--杀死
alter system kill session '131,275';   
--参数 131 为 SESSION_ID
--参数 275 为 SERIAL#