oracle创建用户，修改用户，删除用户等关于用户的

--直接修改底层表 USER$ 更换用户名

1、windows 平台下运行 cmd

2、sqlplus /nolog

3、SQL> conn SYSTEM/123@ORCL as sysdba 连接到数据库。通常可以用sys用户登陆。

4、SQL> select * from user$; 找到 name为需要修改的用户的user#。

select user#, name from user$ where name = 'SA';     --先瞧下SA的信息,其中user#就是一个序列号,相当于身份证号吧,假如这里是250

update user$ set name = 'SA3' where user# = 250;     --于是用户名就改好了啊.当然是update语句就记得再commit提交下啊.

5、SQL> UPDATE USER$ SET NAME='新用户名' WHERE USER#=38;
   已更新 1 行。

6、SQL> COMMIT;提交完成。

7、SQL> ALTER SYSTEM CHECKPOINT;
   系统已更改。

8、SQL> ALTER USER 新用户名 IDENTIFIED BY 新密码;
   此时会提示新用户不存在。

SQL> ALTER USER 新用户名 IDENTIFIED BY 新密码       

*ERROR 位于第 1 行:ORA-01918: 用户'新用户'不存在

9、SQL> ALTER SYSTEM FLUSH SHARED_POOL;
   系统已更改。

10、SQL> ALTER USER 新用户 IDENTIFIED BY 新密码;
    用户已更改。

11、测试连接
    SQL> CONN 新用户/新密码@lmisdx_local;
    已连接。

12、SQL> SELECT * FROM TAB;
    查看新用户下是否有原用户的对象，包括表等。

13、SQL> CONN /    AS SYSDBA已连接。

14、SQL> SHOW USER     --查看当前连接用户
    为"SYS"

15、SQL> SHUTDOWN IMMEDIATE   －－关闭数据库(这句话不能乱用)
    数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。(13,14可以拯救这段)

16、SQL> STARTUP              －－启动数据库
        ORACLE 例程已经启动。......数据库装载完毕。数据库已经打开。

17、可以看出没有再恢复为TEST
    SQL> SELECT USER#,NAME,PASSWORD FROM USER$ WHERE USER#=38;
                   
18、查看数据对象
    SQL> SELECT * FROM TAB;
                              
19、SQL >connsys/lmis@lmisdx_localas sysdba
已连接。
SQL> SHOW USERUSE
为"SYS"
--DROP USER 也没有问题
SQL> DROP USER TEST1 CASCADE;   --删除以前的用户

SYSDBA身份登陆时修改其用户密码比：
SQL> alter user user01 identified by user10;
用户已更改
把USER01用户密码修改USER10

 

查询当前数据名

方法一:select name from v$database;

方法二：show parameter db

方法三：查看参数文件。

Oracle函数的写法（tdc002表名，返回值v_Value）

create or replace

FUNCTION Getivalue

(p_Id VARCHAR2)

RETURN VARCHAR2 AS

v_Value VARCHAR2(50);

BEGIN

  SELECT en_name INTO v_Value FROM tdc002

  WHERE en_code = p_Id;

  RETURN v_Value;

END Getivalue;s

函数的用法：

select Getivalue(en_code) from TDC002s

返回一个表的超级麻烦

从oracle 9i 开始，提供了一个叫做“管道化表函数”的概念，来解决这个问题
这种类型的函数，必须返回一个集合类型，且标明 pipelined
这个oracle函数不能返回具体变量，必须以一个空 return 返回
这个oracle函数中，通过 pipe row () 语句来送出要返回的表中的每一行

调用这个oracle函数的时候，通过 table() 关键字把管道流仿真为一个数据集

具体写法如下：

create type row_type2 as object(username1 varchar2(100),dw1 varchar2(100),ts number);  //创建行类型，同要返回的表的类型一致。
create type table_type2 as table of row_type2;  
create or replace function fun1(dateSJ varchar2) return table_type2
pipelined as  v row_type2; 
begin 
for myrow in 
(
    with a as
     (
         select (select username from users where userid= t.fromid) as username1,to_char((select dwbmmc from ks_xqpm_dwbm where dwbmid=(select deptid from users where userid=t.fromid) )) as dw1,count(*) as ts from mobile t where to_char(sendtime,'yyyy-mm')=dateSJ group by fromid
     )
     ,
     b as
     (
         select '合计' as  username1,'' as dw1,(select count(*)  from mobile  where to_char(sendtime,'yyyy-mm')=dateSJ) as ts from dual
     )
     select username1,dw1,ts  from a union all select username1,dw1,ts from b
)
loop  v := row_type2(myrow.username1, myrow.dw1,myrow.ts); 
pipe row (v); 
end loop; 
return; 
end;  

调用方法： select * from table(fun1('2012-01'));

 

查询当前数据库实例名

方法一：select instance_name from v$instance;

方法二：show parameter instance

方法三：在参数文件中查询。

 

查询数据库域名

方法一：select value from v$parameter where name = 'db_domain';

方法二：show parameter domain

方法三：在参数文件中查询。

 

查询数据库服务名

方法一：select value from v$parameter where name = 'service_name';

方法二：show parameter service_name

方法三：在参数文件中查询。

 

简易连接，不用进行网络配置（2种）

1、c:/>sqlplus /nolog

2、sqlplus sys/pwd@ip:1521/service_name  as sysdba 

 

SERVICE_NAME：在服务器端，用system用户登陆后，sqlplus> show parameter service_name命令察看。 

 

1：创建用户aaa，给其connect和resource角色，但回收unlimited tablespace权限：

SQL> create user SAidentified by sa default tablespace users;

2、分配权限

SQL> grant connect,resource to SA;

grant dba to aaa;

Grant succeeded.

///回收权限

SQL> revoke unlimited tablespace from aaa;

Revoke succeeded.

//分配空间

create tablespace  CMPDATA datafile 'D:a.dbf' size 1000m autoextend on next 50m

导入sql语句

：@D: est.sql

SQl>grant insert any table to SA

.查看所有用户：

  select * from dba_users;

  select * from all_users;

  select * from user_users;

 

2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：

  select * from dba_sys_privs;

  select * from user_sys_privs;

3.查看角色(只能查看登陆用户拥有的角色)所包含的权限

sql>select * from role_sys_privs;

 

4.查看用户对象权限：

  select * from dba_tab_privs;

  select * from all_tab_privs;

  select * from user_tab_privs;

 

5.查看所有角色：

  select * from dba_roles;

 

6.查看用户或角色所拥有的角色：

  select * from dba_role_privs;

  select * from user_role_privs;

7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

select * from V$PWFILE_USERS

 

2、查看用户默认表空间

SQL> select username,default_tablespace from dba_users where username ='SA';

 

3、查看用户使用的表空间

SQL> select DISTINCT owner ,tablespace_name from dba_extents where owner like 'SAMS';

 

4、查看表空间大小

sql> select TABLESPACE_NAME, BYTES/1024/1024 M from dba_data_files where TABLESPACE_NAME='EMALL_DAT';

 

7、检查表空间是否自动扩展

  select tablespace_name,file_name,autoextensible,increment_by from dba_data_files;

 

8、修改表空间自动扩展

   alter database datafile '/oradata/dzsw/UNDOTBS01.dbf' autoextend on  next 10M maxsize 30000M;

其中next 参数为每次扩展的空间的大小，MAXSIZE为扩展空间的最大限制，UNLIMITED为无限制。

 

9、修改表空间数据文件大小

  alter database datafile 'E:APPADMINISTRATORORADATASUSDATASYSTEM01.DBF' resize 5000M ;

 

10、增加表空间数据文件

  alter tablespace ICD_SERVICE ADD datafile  '+DATA1/ctidb/datafile/icd_service1' SIZE 4000M autoextend on  next 50M maxsize UNLIMITED;

 

  alter tablespace ICD_SERVICE ADD datafile  '+DATA1/ctidb/datafile/icd_service1' SIZE 1000M ;

 

  alter tablespace ICD_SERVICE ADD datafile  '+DATA1'  SIZE 4000M;

 

11、检查临时文件使用情况

  select file_name, sum(e.bytes)/1024/1024 as MB   from dba_extents e join dba_temp_files f on e.file_id=f.file_id   group by file_name;

  select file_name,bytes/1024/1024 mb ,tablespace_name from dba_temp_files;

 

12、检查可以回收的文件空间

  SELECT 'alter database datafile '''||a.name||''' resize '||

       CEIL(b.HWM * a.block_size/1024/1024)||'M;' ResizeCmd

FROM v$datafile a,

     (SELECT file_id, MAX(block_id + blocks - 1) HWM

      FROM dba_extents

      GROUP BY file_id) b

WHERE a.file# = b.file_id (+)

  AND (a.bytes/1024/1024 - CEIL(b.HWM * a.block_size/1024/1024)) > 0 ;

alter database datafile '/oradata/dzsw/tbs_jiankong.dbf' resize 1000M;

alter database datafile '/oradata2/dzsw/fapdbls5.dbf' resize 2000M;

 

13、检查系统表空间内是否有用户表

select table_name,tablespace_name from dba_tables where tablespace_name='SYSTEM' and owner='SA';

select table_name,tablespace_name from dba_tables where tablespace_name='USERS' and owner='SA';

 

14、查数据库整体大小

SQL> select sum(mb) from ( select sum(bytes)/1024/1024 as mb from  Dba_Segments Group By Tablespace_Name);

 

15、修改用户默认表空间

alter user SA default tablespace cmpdata quota unlimited on cmpdata ;

 

3，、创建用户命令

 A、连接到用户SQL> conn system/root

B、创建用户: SQL> create user scott identified by tiger;

C、切换用户:SQL> conn scott/tiger    //用户切换失败

grant 权限名 to 用户名;

SQL> conn system/root;//第一步切换到管理员

SQL> grant create session to scott; //为用户受权

Grant succeeded//受权成功

,5，、角色(一个角色包含多个权限)

connect

   create session

   create table

   ……

 resource

    对表空间使用权限

    ……

   grant connect,resource to scott;

1、Create user 用户名 identified by 密码;(不能是全数字)

 2、grant create session,create table,resource to 用户名;//给用户分配权限