Oracle使用管理命令记录

没鞋的脚要努力奔跑

已于 2023-06-30 16:23:00 修改

阅读量1.9k

点赞数 2

分类专栏： sql管理文章标签： oracle 数据库 sql

于 2022-07-22 20:03:33 首次发布

本文链接：https://blog.csdn.net/qq_20377975/article/details/125939127

版权

sql管理专栏收录该内容

6 篇文章 0 订阅

订阅专栏

Oracle开启PDB模式

由于环境更新，Oracle版本换成19C，并开启了PDB模式。

使用平常启动数据库和监听方式启动数据库，显示都已成功，但是去连接数据库，报错报数据库未启动。

解决方式：

sqlplus / as sysdba;–管理员登录

select con_id,name,open_mode from V$pdbs;

–查看pdb的状态 --发现服务orclpdb,处于mounted状态

解决办法：

alter pluggable database orclpdb open; --名称orclpdb

alter session set container=orclpdb;

创建表空间

create tablespace 表空间名 logging datafile 'D:\表空间名.dbf' size 1024m autoextend on next 50m maxsize unlimited extent management local;

创建用户并指向表空间

create user 用户名 identified by 口令 default tablespace 表空间名;

用户授权

角色权限:
DBA：用户具有数据库所有的权限。

connect：用户具有连接数据库的权限，用户只能登录Oracle，不可以创建实体、不可以创建数据库结构。

resource:用户可以创建实体，但是不可以创建数据库结构。

grant connect,resource,dba to 用户名;

对象权限 : 指的是其他拥有用户的对象的权限。
包括：select、delete、update、alter、insert、index、references、flashback、query、rewrite、on
commit refresh；(注意其他用户的权限没有drop权限！)

 //授权用户chenmh用户zhang下person表的select、delete、update、insert权限（增删改查），授予具体地对象权限是对于权限严格控制的一种方案。
grant select,delete,update,inster on zhang.person to chenmh;

//授予用户chenmh用户zhang下person表的所有权限

grant all privileges on zhang.person to chenmh;

remove回收权限

1、回收角色权限
remove connect,resource from chenmh;

2、回收系统权限
remove creat from chenmh；

3、回收用户对象权限，回收zhang用户下person表的所有权限，如果是单个授予的权限需要单个的收回
remove all privileges on zhang.person from chenmh;

导入

imp 用户名/口令@数据库 file=文件路径\文件 full=y;

> 例如： 
imp root/root file=D:\2022.dmp full=y ignore=y

删除表空间，含物理文件

drop tablespace 表空间名称 including contents and datafiles cascade constraint;

Oracle 如何删除表空间

1、删除无任何数据对象的表空间：

用drop tablespace xxx ，删除需要删除的表空间。

2、删除有任何数据对象的表空间

使用drop tablespace xxx including contents and datafiles;

修改对应的表空间和临时表空间

Alter user 用户 default tablespace 表空间; 
Alter user 用户 temporary tablespace 临时表空间;

删除用户

drop user 用户 CASCADE;

常用权限

角色：

　　connect  

　　resource

　　dba

　　CONNECT角色： --是授予最终用户的典型权利，最基本的权力，能够连接到ORACLE数据库中，并在对其他用户的表有访问权限时，做SELECT、UPDATE、INSERTT等操作。
        ALTER SESSION --修改会话
        CREATE CLUSTER --建立聚簇
        CREATE DATABASE LINK --建立数据库链接
        CREATE SEQUENCE --建立序列
        CREATE SESSION --建立会话
        CREATE SYNONYM --建立同义词
        CREATE VIEW --建立视图

   RESOURCE角色： --是授予开发人员的，能在自己的方案中创建表、序列、视图等。
      CREATE CLUSTER --建立聚簇
      CREATE PROCEDURE --建立过程
      CREATE SEQUENCE --建立序列
      CREATE TABLE --建表
      CREATE TRIGGER --建立触发器
      CREATE TYPE --建立类型
      
DBA角色，是授予系统管理员的，拥有该角色的用户就能成为系统管理员了，它拥有所有的系统权限


四，授予权限

　　1、默认的普通用户scott默认未解锁，不能进行那个使用，新建的用户也没有任何权限，必须授予权限

　　

　　grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限，允许用户登录数据库

　　grant unlimited tablespace to zhangsan;//授予zhangsan用户使用表空间的权限

　　grant create table to zhangsan;//授予创建表的权限

　　grante drop table to zhangsan;//授予删除表的权限

　　grant insert table to zhangsan;//插入表的权限

　　grant update table to zhangsan;//修改表的权限

　　grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)

　　2、oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的，需要互相授权

　　

　　grant select on tablename to zhangsan;//授予zhangsan用户查看指定表的权限

　　grant drop on tablename to zhangsan;//授予删除表的权限

　　grant insert on tablename to zhangsan;//授予插入的权限

　　grant update on tablename to zhangsan;//授予修改表的权限

　　grant insert(id) on tablename to zhangsan;

　　grant update(id) on tablename to zhangsan;//授予对指定表特定字段的插入和修改权限，注意，只能是insert和update

　　grant alert all table to zhangsan;//授予zhangsan用户alert任意表的权限


　五、撤销权限

　　基本语法同grant,关键字为revoke

　　六、查看权限

　　select * from user_sys_privs;//查看当前用户所有权限

　　select * from user_tab_privs;//查看所用用户对表的权限



　　九、角色

　　角色即权限的集合，可以把一个角色授予给用户

　　create role myrole;//创建角色

　　grant create session to myrole;//将创建session的权限授予myrole

　　grant myrole to zhangsan;//授予zhangsan用户myrole的角色

　　drop role myrole;删除角色

　　sys;//系统管理员，拥有最高权限

　　system;//本地管理员，次高权限

　　scott;//普通用户，密码默认为tiger,默认未解锁

　　oracle有三个默认的用户名和密码~
　　1.用户名:sys密码:change_on_install
　　2.用户名:system密码:manager
　　3.用户名:scott密码:tiger

LINUX服务器重启Oracle数据库命令

---关闭数据库的命令：

用oracle用户名和密码登录Oracle数据库后，

su  -oracle     ---切换成oracle用户登录；

sqlplus  /nolog  ---切换至sqlplus控制台；

conn   /as  sysdba    ---以管理员的身份登录

show  parameter  db_recovery_file_dest      ---查看数据库中的文件，可以继续执行就不重启数据库，如果无法显示可以重启数据库；

shutdown  immediate   ---立即关闭数据库；

exit     ---推出sqlplus控制台；

-------关闭数据库后需要重启数据库的操作命令：

用oracle用户名和密码登录Oracle数据库后，

sqlplus  /nolog     ----切换到sqlplus控制台；

conn   / as  sysdba     ---以管理员的身份登录

startup   mount;

alter database   open;    -----启动Oracle数据库实例

show   parameter   db_recovery_file_dest      ----重启后继续查看数据库中的文件；

exit    -----使用完成退出

重启Oracle数据库

1、 以oracle身份登录数据库，命令：su - oracle
2、 进入Sqlplus控制台，命令：sqlplus /nolog
3、 以系统管理员登录，命令：connect / as sysdba
　　可以合并为：sqlplus sys/密码 as sysdba
4、 启动数据库，命令：startup
5、 如果是关闭数据库，命令：shutdown immediate（shutdown abort）
6、 退出sqlplus控制台，命令：exit
7、 进入监听器控制台，命令：lsnrctl
8、 停掉监听器       命令：stop 
9、 启动监听器       命令：start
10、退出监听器控制台，命令：exit
11、重启数据库结束
12、查看数据库监听的状态    lsnrctl status

oracle怎样查看表空间

如果要查看当前用户使用的表空间情况，使用如下命令：
select * from user_users

如果要查看oracle下面所有的表空间，使用如下命令：
select * from Dba_Tablespaces

编码问题

select userenv(‘language’) from dual;

服务器gbk编码： AMERICAN_AMERICA.ZHS16GBK
服务器utf8编码: AMERICAN_AMERICA.AL32UTF8

–修改当前会话日期语言为中文
alter session set nls_language=‘SIMPLIFIED CHINESE’;
–修改后可以成功执行
SELECT TO_DATE(‘21-5月-2021’) FROM dual;

–修改会话日期语言为英文
ALTER SESSION SET nls_date_language=‘AMERICAN’;
–修改后可以成功执行
SELECT TO_DATE(‘21-MAY-2021’) FROM dual;

查询权限分配情况

role_sys_privs : 角色拥有的系统权限
role_tab_privs : 角色拥有的对象权限
user_role_privs : 用户拥有的角色
user_tab_privs_made : 用户分配的关于表对象权限
user_tab_privs_recd : 用户拥有的关于表对象权限
user_col_privs_made : 用户分配的关于列的对象权限
user_col_privs_recd : 用户拥有的关于列的对象权限
user_sys_privs : 用户拥有的系统权限

create user : 创建用户
grant : 分配权限
create role : 创建角色
alter user : 修改用户密码
revoke : 收回权限

高级子查询

多列子查询

select name,uid,fid from user
where (uid,fid)in(select uid,fid from user where status=1);

with子句
with子句将该子句中的语句执行一次并存储到用户的临时表空间中

with number as (select iphone from user where name ='baby')
select name from user where iphone = number;

查看当前数据库连接的用户:

show user

用户的切换:

conn 用户名/密码 [as sysdba ]

单行函数:


> 把小写的字符转换成大写的字符:

select upper('ccc') from dual

> 把大写的字符转换成小写的字符:

select lower ('ccc') from dual

> 把首字母大写:

select initcap ('ccc') from dual

> 字符串的可以使用concat:

select concat('hello','world') from dual

> 字符串的截取,使用substr,第一个参数是源字符串,第二个参数是开始索引,第三个参数是结束的索引,开始的索引使用1和0效果相同:

select substr('hello',1,3) from dual

> 获取字符串的长度:

select length('ccc') from dual

> 字符串替换:第一个参数是源字符,第二个参数被替换的字符,第三个是替换字符串:

select replace('hello','l','x') from dual

修改表名

1.alter table 旧表名 rename to 新表名
2.rename 旧表名 to 新表名

复制表数据

1.表结构一样

insert into 新表名 select * from 旧表名

2.表结构不一样

insert into 新表名(column1,column2...) select (column1,column2...) from 旧表名

复制表结构和表数据

create table 新表名 as select * from 旧表名

常用命令记录

连接Oracle数据库的SQLPLUS命令

sqlplus /nolog

输入用户名和密码

connect username/password@database_name

IMPDP
IMPDP是Oracle数据库中一个非常强大的工具。它可用于将数据从一个数据库导入到另一个数据库。以下是IMPDP命令的一些示例：

-- 导入table1表的数据
impdp username/password directory=data_pump_dir dumpfile=export.dmp tables=table1

EXPDP
EXPDP是IMPDP工具的互补工具。它可用于将数据从一个数据库导出到另一个数据库。以下是一些EXPDP命令示例：

-- 导出table1表的数据
expdp username/password directory=data_pump_dir dumpfile=export.dmp tables=table1

RMAN
RMAN是Oracle数据库的备份与恢复工具。

--备份数据库
backup database;

--还原数据库：
estore database;