Oracle 常用命令 | mgzu's blog

Oracle 常用命令及错误解决

表空间

创建表空间及用户

# win
create tablespace HG datafile 'd:\oracle\data\hg.dbf' size 2000M;
# linux
create tablespace HG datafile '/home/oracle/data/hg.dbf' size 2000M;

空间名重命名

alter tablespace HG rename to HG_DATA;

修改用户默认表空间、默认临时表

ALTER USER test_user DEFAULT TABLESPACE HG_DATA;
ALTER USER test_user TEMPORARY TABLESPACE TEMP;

删除表空间

# 先修改用户默认表空间
ALTER USER test_user DEFAULT TABLESPACE HG_OTHER;
# 再删除表空间
DROP TABLESPACE HG_DATA INCLUDING CONTENTS AND DATAFILES;

用户

创建用户

# 需先创建 pdb1 表空间
create user test_user identified by "abcdef.123" default tablespace pdb1 temporary tablespace TEMP;

授权

# 一般
grant connect, resource to test_user;
# dba
grant connect, resource, dba to test_user;

PDB

创建 PDB

先查询出 PDBSEED 位置

select name from v$datafile;

win

CREATE PLUGGABLE DATABASE pdb1 ADMIN USER test_user IDENTIFIED BY "abcdef.123"
STORAGE (MAXSIZE 2G)
DEFAULT TABLESPACE pdb1
DATAFILE 'D:\Oracle\data\pdb\pdb01.dbf' SIZE 2000M AUTOEXTEND ON
PATH_PREFIX = 'D:\Oracle\data\pdb'
FILE_NAME_CONVERT = ('D:\ORACLE\ORADATA\ROCL\PDBSEED', 
'D:\Oracle\data\pdb');

linux

CREATE PLUGGABLE DATABASE pdb1 ADMIN USER test_user IDENTIFIED BY "abcdef.123"
STORAGE (MAXSIZE 2G)
DEFAULT TABLESPACE pdb1
DATAFILE '/home/oracle/data/pdb/pdb01.dbf' SIZE 2000M AUTOEXTEND ON
PATH_PREFIX = '/home/oracle/data/pdb'
FILE_NAME_CONVERT = ('/opt/oracle/oradata/ORCLCDB/pdbseed',
'/home/oracle/data/pdb');

windows 文件分隔符为 \，linux 为 /

切换 pdb

ORCLPDB 需要替换成对应的 PDB

alter session set container=ORCLPDB;

查询 service_name

show pdbs;
# 或者
select name, pdb from v$services;

开启 PDB

# 开启单个 pdb
alter pluggable database pdb1 open;
# 开启所有 pdb
alter pluggable database all open;

数据库启动自动开启 PDB

CREATE TRIGGER open_all_pdbs
   AFTER STARTUP ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;

数据库导入、导出

imp 导入

imp 导入，需要创建同样的表空间

imp test_user/abcdef.123@ORAL file=d:\db\test_user.dmp log=d:\db\test_user.log full=y

常见错误

ORA-01034

startup

ORA-01035

test_user 需要替换成对应的用户

如果 PDB 不对应，需要先切换 PDB

grant restricted session to test_user;

ORA-01109

ORCLPDB 需要替换成对应的 PDB

alter pluggable database ORCLPDB open;

ORA-12514

1. 可能是数据库未启动

   # 检查 oracle 是否启动
   select status from v$instance;
   # 启动数据库
   startup;
   # 启动所有 PDB
   alter pluggable database all open;

ORA-65114

alter session set container=oral;
alter pluggable database storage unlimited;

This blog is under a CC BY-NC-SA 4.0 International License
本文链接：http://mgzu.github.io/2021/01/27/Oracle-command/