oracle常用操作

数据库

1. 12C之后有了CDB(container database)和PDB(pluggable database)的概念，一个CDB可以包含多个PDB。
   
2. 创建容器和非容器化的数据库
   
3. 查询当前cdb下的pdbs，使用pl/sql developer执行没结果，sql* plus可以，不知为何

-- 先使用dba登录
show pdbs;

4. 通过pdbseed创建pdb

-- create pluggable database 数据库名 admin user 管理员账
-- identified by 管理员密码
-- file_name_convert=('pdbseed目录','新pdb目录')
create pluggable database salepdb admin user salepdb identified by 123456
storage (maxsize 2G)
default tablespace sales
file_name_convert=('D:\Users\zhoushiya\oradata\ORCL\pdbseed','D:\Users\zhoushiya\oradata\ORCL\salepdb')
-- 打开pdb数据库
alter pluggable database salepdb open;

5. 创建好了之后就可以用刚才的账号密码以dba身份登录了
6. 切换pdb

alter session set container=salepdb;

7. 也可以直接在登录时指定pdb，与指定服务名一样的
   https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9531165800346188172

sale_operator/welcome@salepdb as sysoper;

账户

1. CDB上只能分配公共账户，账户名必须以c##开头，例如

create user c##zhoushiya
identified by "123456"
container=all
default tablespace USERS;

2. PDB上只能分配本地账户

create user saler
identified by "welcome"
container=current
default tablespace saler;

3. 给账户添加connect和resource权限，以及saler表空间的无限制配额

grant connect,resource to "saler";
alter user saler quota unlimited on saler;

4. 注意：在oracle windows服务器上默认任意账号是无法使用sysoper身份登录的，会提示账号或密码错误。但是任意账号都可以使用sysdba登录，即使不是管理员。这是oracle的默认设定。将当前用户添加到ORA_OPER组之后，就可以使用sysoper身份登录了。

-- 添加用户到组（以用户administrator到组ORA_OPER为例）：
net localgroup ORA_OPER administrator /a
-- 查看当前用户信息
net user %username%

5. 使用普通身份登录

表空间

1. 创建表空间

create tablespace saler
datafile 'd:\saler.dbf'
size 16m
autoextend on
next 32m
maxsize 2048m
extent management local;

2. 查询表空间

select tablespace_name from user_tablespaces;

3. 设定用户默认表空间

alter user salepdb default tablespace saler;

4. 查询某个表空间下所有表

select table_name from all_tables where tablespace_name='SALER';

表

1. 创建表

create table saler_account(
id number not null primary key,
account varchar2(128) not null unique 
)
tablespace saler

其他命令

1. 查询oracle当前版本

SELECT * from v$version;

2. oracle可以使用||来连接字符串

insert into saler.saler_account values(SEQ_SALER_ID.nextval,'张三'||SEQ_SALER_ID.nextval);

3. 循环

SET SERVEROUTPUT ON;
DECLARE
  X number; --声明变量
BEGIN
  x := 1; --给初值
  WHILE x<10 LOOP
    --reverse由大到小
    DBMS_OUTPUT.PUT_LINE(x);
    x :=x+1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('end loop:x=' || X); --x=1
END;

备份和还原

1. cmd下使用exp命令进行备份，注意必须填写完整ip和端口，否则可能连不上

exp saler/welcome@localhost:1521/salepdb buffer=64000 file=d:\1.dmp full=y

2. cmd下使用imp命令进行还原，将会还原可以还原的对象以及数据，出错的环节不影响其他环节

imp saler/welcome@localhost:1521/salepdb file=d:\1.dmp full=y log=d:\1.log

表空间、用户、表的关系

1. 表空间相当于仓库
2. 用户相当于商家
3. 表相当于产品

a用户访问b用户的表c

1. 首先要给与a访问b.c表的权限

grant select on b.c to a;

2. 然后进行查询

select * from b.c;

3. 还可以创建同义词，让b更简单访问b.c

create public SYNONYM d for b.c;

# a用户直接
select * from d;