Oracle数据库创建用户，表空间，授权

时间：2025-04-03 19:41:43

Oracle数据库创建用户，表空间，授权

1.首先可用用户scott登录以sysdba的身份登录oracle

conn scott/tiger as sysdba;

2.创建用户 eg：cyz

create user cyz identified by "123456";

3.如果要修改用户密码：

alter user cyz identified by cyz21；

4.用户解锁

alter user cyz account unlock;

5.查询或删除当前用户/系统的表空间

select username,default_tablespace from user_users;//查询当前表空间
drop tablespace USERS including contents and datafiles cascade constraint;/删除表空间
select username,default_tablespace from dba_users;//查询系统表空间

6.创建表空间

create tablespace db_space datafile 'E:\db_space\db_data.dbf' size 200M;

7.将表空间分配给用户

alter user cyz default tablespace db_space ;

8.判断该用户是否存在

select username from all_users where username='cyz';

9.授权（单个授权）

grant create session to cyz；登录权限
grant create table to cyz；建表权限
grant create view to cyz；创建视图权限
grant create connect to cyz；
grant resource to cyz；
grant all to public；所有权限给所有用户

10.对用户进行整体授权

grant connect ,resource,dba,exp_full_database,imp_full_database to cyz;

详细介绍：

connect 角色拥有的权限有：alter session，create cluster，create database
link，create sequence，create session，create stnonym，create table，create
view； resource角色拥有的权限有：create cluster，create indextype，create
operator，create procedure，creawte sequence， create table，create
trigger，create type； dba拥有的所有管理权限 exp_full_database 主要是用户oracle 数据的导出
imp_full_database 主要用于oracle数据的导入

11.撤销权限

revoke connect   from cyz;

12.查询用户具有的权限

select * from session_privs;

13.删除用户及相关对象

drop user cyz cascade;

14.对表，字段，类型的相关操作。

create table test
(
id  number not null,
name varchar2(20)
)

alter table test rename to test1；修改表名
alter table test rename column name to name1；修改表列名
alter table test modify (name1 number(20));修改字段类型
alter table test add  status varchar2(50);添加字段status
alter table test drop column status;删除字段status

15.删除用户下的所有表

select ‘drop table ‘||table_name||’;’ from cat where table_type='TABLE' order by TABLE_NAME;

16.数据导出和导入
exp 用户名/密码@数据库实例 owner=用户名 file=文件存储路径

exp cyz/123456@127.0.0.1:1521/orcl owner=cyz file=F:\cyzdb.dmp

导入
imp用户名/密码@数据库fromuser=用户名touser=用户名file=d:\ ignore=y

imp cyz/123456@127.0.0.1:1521/orcl fromuser=cyz touser=data file=F:\test.dmp ignore=y

基本语法和实例：

1、exp: 有三种主要的方式（完全、用户、表） 1、完全： EXP SYSTEM/MANAGER BUFFER=64000
FILE=C:\ FULL=Y 如果要执行完全导出，必须具有特殊的权限 2、用户模式： EXP SONIC/SONIC
BUFFER=64000 FILE=C:\ OWNER=SONIC 这样用户SONIC的所有对象被输出到文件中。
3、表模式： EXP SONIC/SONIC BUFFER=64000 FILE=C:\ OWNER=SONIC
TABLES=(SONIC) 这样用户SONIC的表SONIC就被导出

17.判断用户下表是否存在，存在则删除，不存在则创建

declare
	num number;
	Begin
		select count(1) into num from all_tables where table_name='DS_TABLE' and owner='data';
	if num=1
	then 
		execute immediate 'drop table DS_TABLE';
	else
		execute immediate 'create table XXXX(id number not null,name varchar(100))';
	end if;
end;

18.扩展内容：
18.1查询oracle版本

select version from product_component_version where substr(product, 1, 6) = 'Oracle';

18.2查询表空间大小

select sum(bytes) / (1024 * 1024) as free_space, tablespace_name from dba_free_space group by tablespace_name;

18.3查询表空间大小，空闲大小，使用大小。

select a.tablespace_name tablespace,//查询表空间
        total/(1024*1024) allsize,//查询总大小,单位为Mb
        free/(1024*1024) freesize,//空闲空间大小
        (total-free)/(1024*1024) usedsize,//已使用大小
        total/(1024*1024*1024) Gsize,//查询总大小， 单位为G
        round((total-free)/total,4)*100 usedlv //使用率
        from (select tablespace_name,sum(bytes)free
        from dba_free_space group by tablespace_name) a,
        (select tablespace_name,sum(bytes) total 
        from dba_data_files group by tablespace_name)b
        where a.tablespace_name=b.tablespace_name;

18.4 修改连接池
//查询连接池连接数

show parameter processes;

//修改连接池

alter system set processes=1000 scope=spfile;

//查询连接会话

show parameter sessions;

//修改连接会话大小

alter system set sessions=1000 scope=spfile;

19.开启关闭数据库
19.1关闭数据库（SHUTDOWN IMMEDIATE）

（1）打开cmd，输入sqlplus/nolog，按回车键；
（2）输入conn/ as sysdba（最高权限）
（3）shutdown immediate；

19.2、启动数据库

（1）打开cmd，输入sqlplus/nolog，按回车键；
（2）输入conn/ as sysdba
（3）startup

20.查询oracle用户是否连接

select sid,serial#,username from v$session where username=upper('用户名');

  /*
     删除当前连接用户
    */
-- 1、首先切换到 SYSTEM 用户
-- 2、查询用户各进程相对应的 sid、serial#。
      SELECT sid,serial#,username FROM v$session WHERE username = UPPER('TEST1');
-- 3、根据上面的查询结果，将用户占用的所有进程杀掉。
      --注意： sid 与 serial# 是一一对应的
      ALTER SYSTEM KILL SESSION 'xxx, xxx';
-- 4、删除用户
      DROP USER test1 CASCADE;

21.查询job进程，查杀该进程查询job序号

select * from user_jobs;

查杀进程

exec DBMS_JOB.BROKEN(JOB,true);

启动进程

exec DBMS_JOB.RUN(JOB)

=进入最高权限sysdba用户操作用户锁定，修改用户密码的问题====

cmd>sqlplus，进入oracle控制台
用户名: sqlplus/as sysdba,口令：空(回车即可)
 #查看用户列表
SQL>select username from dba_users;
#修改某一用户密码
SQL>alter user 用户名 identified by 新密码；
#oracle用户解锁
SQL>alter user 用户名 account unlock；

相关文章