In this Document
APPLIES TO:
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
PURPOSE
This document addresses the frequently asked questions related to Oracle Multitenant achitecture which includes container database (CDB) and pluggable database (PDB).
QUESTIONS AND ANSWERS
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner
General CDB / PDB Concept in 12c Multitenant Architecture
What is a pluggable database (PDB) in Multitenant Architecture?
Pluggable Databases (PDBs) is new in Oracle Database 12c Release 1 (12.1). You can have many pluggable databases inside a single Oracle Database occurrence. Pluggable Databases are fully backwards compatible with an ordinary pre-12.1 database.
Why would I consider using the Multitenant option ?
You should consider following Database Consolidation Goals to achieve:
- Reduce Total Cost of Operation
- Administration costs
- Operations costs
- Data center costs
- Storage costs
- Contingency costs
- Resource utilization
- Manageability
- Integration
- Service management
- Must not need to change applications
- Must not harm performance
- Must provide resource management and isolation
between applications
- Must simplify patching and upgrade
of Oracle Database
What other benefits will I get from Multitenant option ?
The benefits of mulitenant Pluggable Databases are:
- Fast provisioning of a new database or of a copy of an existing database.
- Fast redeployment, by unplug and plug, of an existing database to a new platform.
- Quickly patch or upgrade the Oracle Database version for many databases and for the cost of doing it once.
- Patch or upgrade by unplugging a PDB and plugging it into a different container database (CDB) in a later version.
- A machine can run more database instances in the form of PDBs than as individual, monolithic databases.
- Separate the duties of the application administrator from the duties of the administrator of the Oracle-supplied system.
How easy to migrate an existing pre 12.1 database to 12c Multitenant database ?
Migrate to 12c Pluggable database is very simple and easy. You can evaluate and adopt which one best suit for you.
Plan A.
- Upgrade an existing pre 12.1 database to 12.1
- Plug-in the database post upgrade into a CDB
Plan B.
- Provision empty PDBs per database that need to consolidated
- Use datapump or golden gate replication to migrate a Database into a PDB
You can take a look at Doc ID 2051130.1 - Database upgrade to 12c Pluggable database (Multitenant) using TTS method. It talks about upgrading 11.2.0.4.0 database directly to 12.1.0.2.0 pluggable database through TTS method. This method won't require intermediate 12c upgrade.
Which Oracle Database features are currently not supported in a Multitenant architecture ?
The following Oracle Database features are currently not supported in a CDB:
If you must use one or more of these features, then create a non-CDB.
Does each non-CDB need its own CDB if I do not have a license for Multitenant?
Yes
Can multiple CDBs run on the same server?
Yes
Can multiple CDBs run out of the same ORACLE_HOME installation?
Yes
Basic Multitenant CDB / PDB Operations
How do I know if my database is Multitenant or not ?
Establish a SQL Session and run this Query:
SQL> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE;
NAME Multitenant Option ? OPEN_MODE CON_ID
--------- ------------------------------ -------------------- ----------
CDB2 Multitenant Option enabled MOUNTED 0
What Pluggable databases do we have in this container database ?
SQL> Show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ --------------- ---------------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
5 PDB3 READ WRITE YES
How do I connect to a Pluggable Database , say, PDB6 ?
You can switch to PDB6 from other PDB or Root Container using following command.
SQL> alter session set container = pdb6;
Connecting to a PDB directly Using the SQL*Plus CONNECT Command:
You can use the following techniques to connect to a PDB with the SQL*Plus CONNECT command:
A) Database connection using easy connect
Ex: CONNECT username/password@host[:port][/service_name][:server][/instance_name]
Examples of SQLPLUS from Os prompt:
$ sqlplus username/password@//<HOST>:1521/pdb2
OR
$ sqlplus username/password@//localhost:1521/pdb2
OR
$ sqlplus username/password@//localhost/pdb2
SQL> show con_name
CON_NAME
------------------------------
PDB2
B) Database connection using a net service name
Example TNSNAMES.ora:
=======
LISTENER_CDB1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
CDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1)
)
)
PDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = www.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb2)
)
)
=======
Example of SQLPLUS from Os prompt:
$ sqlplus username/password@pdb2
How do I switch to main container Database ?
SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
How do I determine which PDB or CDB I am currently connected to ?
SQL> show con_name
CON_NAME
------------------------------
PDB6
OR
SQL> select sys_context ( 'Userenv', 'Con_Name') "Container DB" from dual;
Container DB
--------------------------------------------------------------------------------
PDB6
How do I start up a Pluggable database ?
When connect to current PDB:
SQL> alter pluggable database open;
When connect to root:
SQL> alter pluggable database pdb6 open;
How do I shutdown / close a Pluggable database ?
When connect to current PDB:
SQL> alter pluggable database close;
When connect to root:
SQL> alter pluggable database pdb6 close;
How do I shut down / Startup the Container Database ?
Use startup / Shutdown command similar to startup / shutdown of Non CDB.
When the container database is shutdown , no PDB is accessible.
In a CDB, the root and all of the PDBs share a single instance, or, when using Oracle RAC, multiple concurrent database instances. You start up and shut down an entire CDB, not individual PDBs. However, when the CDB is open, you can change the open mode of an individual PDB by using the ALTER PLUGGABLE DATABASE statement.
Which parameters are modifiable at PDB level ?
select NAME, ISPDB_MODIFIABLE from V$PARAMETER;
What common users do I have in my cdb ?
SQL> select distinct USERNAME from CDB_USERS where common = 'YES';
How do I create a common user ?
SQL> create user c##xxxidentified by <password> container=all;
How do I create a local user ?
SQL> create user <username> identified by <password> container=current;
Multitenant Architecture
What is the difference between Container ID Zero and One ?
CON_ID "0" means data does not pertain to any particular Container but to the CDB as a whole. For example, a row returned by fetching from V$DATABASE pertains to the CDB and not to any particular Container, so CON_ID is set to "0". A CONTAINER_DATA object can conceivably return data pertaining to various Containers (including the Root which has CON_ID==1) as well as to the CDB as a whole, and CON_ID in the row for the CDB will be set to 0.
Following table describes various values of CON_ID Column in Container Data Objects.
0 = The data pertains to the entire CDB
1= The data pertains to the root
2= The data pertains to the seed
3 - 254 = The data pertains to a PDB, Each PDB has its own container ID.
Are there any background processes ex, PMON, SMON etc associated with PDBs ?
No. There is one set of background processes shared by the root and all PDBs.
Are there separate control file required for each PDB ?
No. There is a single redo log and a single control file for an entire CDB.
Are there separate Redo log file required for each PDB ?
No. There is a single redo log and a single control file for an entire CDB.
Can I monitor SGA usage on a PDB by PDB basis?
There are single SGA shared by all pluggable databases. However, you can determine SGA consumptions by all containers i.e, root and PDB.
SQL> alter session set container=CDB$ROOT;
SQL> select POOL, NAME, BYTES from V$SGASTAT where CON_ID = '&con_id';
SQL> select CON_ID, POOL, sum(bytes) from v$sgastat
group by CON_ID, POOL order by CON_ID, POOL;
Can I monitor PGA usage on a PDB by PDB basis?
select CON_ID, sum(PGA_USED_MEM), sum(PGA_ALLOC_MEM), sum(PGA_MAX_MEM)
from v$process
group by CON_ID order by CON_ID;
alter session set container =CDB$ROOT;
select NAME , value from v$sysstat where NAME like 'workarea%';
alter session set container = <targetPDB>;
select NAME , value from v$sysstat where NAME like 'workarea%';
Do I need separate UNDO tablespaces for for each of my PDB ?
There is one active undo tablespace for a single-instance CDB. For an Oracle RAC CDB, there is one active undo tablespace for each instance. Only a common user who has the appropriate privileges and whose current container is the root can create an undo tablespace.
Please refer the below note for various modes of Undo available from 12.2 onwards
Undo Modes in 12.2 Multitenant Databases - Local and Shared Modes (Doc ID 2169828.1)
Do I need separate SYSTEM tablespaces for each of my PDB ?
There is a separate SYSTEM tablespace for the root and for each PDB.
Do I need separate SYSAUX tablespaces for for each of my PDB ?
There is a separate SYSAUX tablespace for the root and for each PDB.
Do I need Temporary tablespaces for for each of my PDB ?
There is one default temporary tablespace for the entire CDB. However, you can create additional temporary tablespaces in individual PDBs. One active temporary tablespace is needed for a single-instance CDB, or one active temporary tablespace is needed for each instance of an Oracle RAC CDB.
Can I specify a separate default tablespace for the root and for each PDB ?
Yes. You can specify a separate default tablespace for the root and for each PDB
Are all physical datafiles separate for root and PDB?
There are separate datafiles for the root, the seed, and each PDB.
Where is user data stored in CDB ?
In a CDB, most user data is in the PDBs. The root contains no user data or minimal user data.
Does Pluggable database support separate database characterset ?
A CDB uses a single character set. All of the PDBs in the CDB use this character set.
Oracle recommends the following:
- For all new deployments and if all PDBs are created empty, Oracle strongly recommends AL32UTF8 for the CDB database character set and AL16UTF16 for the CDB national character set.
- If you can migrate your existing databases to AL32UTF8 before consolidation, Oracle recommends that you do so and consolidate into one or more AL32UTF8 CDBs, depending on your needs. You can use Oracle Database Migration Assistant for Unicode to migrate a non-CDB to AL32UTF8. You cannot migrate the CDB using Oracle Database Migration Assistant for Unicode, after creation.
- If you cannot migrate your existing databases prior to consolidation, then you have to partition them into sets with plug-in compatible database character sets and plug each set into a separate CDB with the appropriate superset character set.
Reference: Oracle Database Globalization Support Guide, 12c Release 1 (12.1)
How do I configure Net Files in a Pluggable database environment ?
There is a single listener.ora, tnsnames.ora, and sqlnet.ora file for an entire CDB. All of the PDBs in the CDB use these files.
Advance CDB / PDB Operations
How can I install and setup Pluggable Database ?
Use runInstaller to install the Oracle Database software
Use dbca to create databases. You can create many pluggable databases in a single operation.
DBCA enables you to specify the number of PDBs in the CDB when it is created.After a CDB is created, you can use DBCA to plug PDBs into it and unplug PDBs from it.
What Operations act on PDBs as entities ?
These operations act on PDBs as entities:
• create PDB (brand-new, as a clone of an existing PDB, by plugging in an unplugged PDB)
• unplug PDB
• drop PDB
• set the Open_Mode for a PDB
How can I create a pluggable database ?
create pluggable database x admin user a identified by p;
create pluggable database y admin user a identified by p file_name_convert = ('pdbseed', 'y');
How to drop a PDB irrevocably ?
drop pluggable database x
including datafiles;
How easy is it to manage the provisioning of PDBs using PL/SQL ?
Following an Example of PL/SQL Code to show this.
-- Using Oracle-Managed Files
declare
t0 integer not null := -1;
procedure Show_Time(What in varchar2) is
t varchar2(10);
begin
t := Lpad((DBMS_Utility.Get_Time() - t0), 5);
DBMS_Output.Put_Line('create PDB:'||t||' centiseconds');
end Show_Time;
begin
t0 := DBMS_Utility.Get_Time();
execute immediate '
create pluggable database x
admin user a identified by p
';
Show_Time('create PDB:');
t0 := DBMS_Utility.Get_Time();
execute immediate '
drop pluggable database x
including datafiles
';
Show_Time('drop PDB: ');
end;
How to clone a PDB from an existing PDB ?
The clonee must be open in read only mode.
-- Using Oracle-Managed Files
create pluggable database x2
from x;
How to unplug a PDB ?
alter pluggable database x unplug into '/some_directory/x_description.xml' ;
The into keyword must be followed by the full path for a
description of the PDB, in XML, generated by the operation.
Scalability & RAC
How to add or modify a user-managed service ?
srvctl add service … –pdb <pdb_name>
Starting a user-managed service using
srvtcl will open the PDB automatically in all the instances in which the service is started.
Specifying the empty string ("") as the <pdb_name> will cause the pluggable database
attribute of a service to be set to null. The service can then be used only to connect to the root.
How can I view which service is attached to my Pluggable database ?
SQL> column NAME format a30
SQL> select PDB, INST_ID, NAME from gv$services order by 1;
PDB INST_ID NAME
-------------------------------- ---------- --------------------------------
CDB$ROOT 1 cdb1XDB
CDB$ROOT 1 SYS$BACKGROUND
CDB$ROOT 1 SYS$USERS
CDB$ROOT 1 cdb1
PDB1 1 pdb1
PDB2 1 pdb2
Diagnosibility
Where can I find Alert log for my pluggable Database ?
A Single copy of Alert log is generated which contains warnings and alert information for all PDBs.
XML version of alert can be found in "Diag Alert" and text formatted Aler log can be found in "Diag Trace" of the container database.
You can find details by selecting from v$diag_info dynamic view.
Where can I find trace files related for my pluggable Database ?
All traces generated from all PDBs are currently found in "Diag Trace" of the container database.
You can find details by selecting from v$diag_info dynamic view.
Miscellaneous
If a user-defined, common user creates schema objects in a PDB, and if later that PDB is unplugged
and plugged into a different CDB in which that common user does not exist,
then what happens to the schema objects?
By which user will they be owned?
Will other users within the PDB, which had been granted privileges on those schema objects, still retain those privileges?
If you plug a PDB that contains a common user into a CDB, then the following actions take place:
The common user accounts in this PDB lose commonly granted privileges that they may have had,
including the SET CONTAINER privilege.
If the target CDB has a common user with the same name as a common user in a newly plugged-in PDB,
then the new common user is merged with the target CDB common user. The password of the target CDB
common user takes precedence. Otherwise, a common user in a newly plugged in PDB becomes a locked account. In this case, you can do one of the following:
Leave the user account locked and use the objects of its schema.
Use Oracle Data Pump to copy these objects to another schema,
and then drop the locked user account.
Close the PDB, connect to the root, and then create a common user with the same name
as the locked account. When you re-open the PDB, Oracle Database resolves the differences
in the roles and privileges that were commonly granted to the locked user. Afterward,
you can unlock this user account in the PDB. Privileges and roles that were
locally granted to the user will remain unchanged.
Is the multitenant option available in Standard Edition?
Yes, but you may only create one PDB, per CDB
Can a transaction span across PDBS ?
No, though "alter session set container" is allowed after starting a transaction in a PDB,
only select is allowed in the second PDB.Transaction is preserved and you can do
commit or rollback after switch back to original PDB
What data can be seen in CDB_ and V$ views from each container?
CDB_* views are container data objects. When a user connected to the root queries a CDB_* view,
the query results will depend on the CONTAINER_DATA attribute for users for the view.
The CONTAINER_DATA clause of the SQL ALTER USER statement is used to set and modify
users' CONTAINER_DATA attribute.
In the root of a multitenant container database (CDB), CDB_* views can be used to
obtain information about tables, tablespaces, users, privileges, parameters, and so on
contained in the root and in pluggable databases (PDBs).
The CDB_* views are owned by SYS, regardless of who owns the underlying DBA_* view.
By default, a user connected to the root will only see data pertaining to the root.
May the database timezone be set on a per-PDB basis?
Yes.
May NLS currency settings (NLS_CURRENCY et al) be set on a per-PDB basis?
Yes.
How to monitor the undo usage of each container /database in CDB/PDB ?
select NAME,MAX(TUNED_UNDORETENTION), MAX(MAXQUERYLEN), MAX(NOSPACEERRCNT), MAX(EXPSTEALCNT)
from V$CONTAINERS c , V$UNDOSTAT u
where c.CON_ID=u.CON_ID
group by NAME;
select NAME,SNAP_ID,UNDOTSN,UNDOBLKS,TXNCOUNT,MAXQUERYLEN,MAXQUERYSQLID
from V$CONTAINERS c , DBA_HIST_UNDOSTAT u
where c.CON_ID=u.CON_ID
and u.CON_DBID=c.DBID
order by NAME;
What is the difference between schema-based consolidation and the multitenant architecture?
1. Name collision might prevent schema-based consolidation.
2. Schema-based consolidation brings weak security.
3. Per-application, back-end, point-in-time recovery is prohibitively difficult.
4. Resource management between application back-ends is difficult.
5. Patching the Oracle version for a single application back-end is not possible.
6. Cloning a single application back-end is difficult.
############sample 1 怎样在本地和异地克隆一个pdb
参考 文档 https://blog.csdn.net/su377486/article/details/103058842
通过克隆pdb 方式，可以快速的搭建一个测试环境。uat 或者dev 环境
1.1 本地 本机 克隆一个pdb
create pluggable database testmove1 from MIQS file_name_convert=('miqs','testmove1');
alter pluggable database TESTMOVE1 open;
ALTER pluggable DATABASE ALL save state;
alter session set container=TESTMOVE4;
1.2 异地 异机 克隆一个pdb 使用db link 方式克隆一个pdb
19c dest: (tns names SERVICE_NAME point to cdb level ,not pdb level)
pdbname_dev=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.241.117.117)(PORT=1528))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=icndev)))
19c source:
sqlplus dbmgr/Qwer1234@10.241.117.49:1521/pdbname
19c target
sqlplus dbmgr/Qwer1234@10.241.117.16:1521/pdbname
sqlplus dbmgr/Qwer1234@pdbname_dev
19c source: (cdb level)
sqlplus / as sysdba
grant create pluggable database to dbmgr container=all;
alter pluggable database pdbname close immediate;
alter pluggable database pdbname open read only;
19c: target: (cdb level)
##创建新pdb数据目录和 tempfile 目录
mkdir -p /data/oracle/data/spdbnamedb01/cpdbname/oradata/pdbname_test/tempfile
mkdir -p /data/oracle/data/spdbnamedb01/cpdbname/oradata/pdbname_test
sqlplus / as sysdba
create database link clone_icn connect to dbmgr identified by Qwer1234 using 'pdbname_dev';
select * from dual@clone_icn;
#pdbname@clone_icn is source pdb name and connet info
create pluggable database pdbname_test from pdbname@clone_icn file_name_convert=('/data/oracle/data/spdbnamedb01/cpdbname/oradata','/data/oracle/data/spdbnamedb01/cpdbname/oradata/pdbname_test');
select * from pdb_plug_in_violations;
alter pluggable database pdbname_test open;
19c source: (cdb level)
source:
alter pluggable database pdbname close immediate;
alter pluggable database pdbname open;
##########sample 2 pdb 可以通过sqlpluspdb 连接pdb
通过设置这个环境变量，就可以登陆了
export ORACLE_PDB_SID="$READ_TEMP2"
sqlplus / as sysdba
##sample 5 如果要重命名 pdb ，请参考
https://blog.csdn.net/weixin_34381666/article/details/94759086
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
YB MOUNTED
YB1 MOUNTED
CLONEDB READ WRITE
SQL> alter session set container=clonedb;
Session altered.
SQL> shutdown immediate;
Pluggable Database closed.
#以restrict模式打开
SQL> startup open restrict
Pluggable Database opened.
SQL> alter pluggable database clonedb rename global_name to rdb;
Pluggable database altered.
SQL> show con_name;
CON_NAME
------------------------------
RDB
SQL>
复制代码
转载于:https://www.cnblogs.com/wangchaoyuana/p/7531993.html
######sample 6 删除pdb ，
2. 删除PDB执行步骤
关闭所有节点下的指定PDB，PDB处于关闭状态才能删除
SQL>show pdbs；
首先在所有节点上停止实例（PDB）
SQL> alter pluggable database testrac close immediate;
Pluggable database altered.
2. 单节点执行删除命令
SQL> drop pluggable database testrac including datafiles;
Pluggable database dropped.
3.所以节点查看是否删除
SQL>show pdbs;
######sample 2
记一次Oracle19c pdb 修改端口号无法动态注册监听的问题
https://blog.csdn.net/vip5194/article/details/111880495
问题：一个19c的库修改listener.ora，tnsnames.ora 的端口号，从1521 修改到1523，然后重启监听，但是各种方式都尝试就是无法动态注册监听，
尽管local_listener 'LISTENER_ORCL19C' 配置是正确的。
解决办法：
重新注册local_listener
alter system set local_listener='LISTENER_ORCL19C'
然后重新注册
alter system register
或者
重启数据库
##########sample 3 sql语法新功能 FETCH
在Oracle 12c中为了方便数据的分页显示操作，专门提供了FETCH语句，使用此语句可以方便地取得指定范围内的操作数据。
语法：
SELECT [DISTINCT] 分组字段1 [AS] [列别名]，分组字段2 [AS] [列别名]
FROM 表名称 [表别名]
[WHERE 条件(S)]
[GROUP BY 分组字段1，分组字段2]
[HAVING 过滤条件(S)]
[ORDER BY 排序字段 ASC|DESC]
[FETCH FIRST 行数] | [OFFSET 开始位置 ROWS FETCH NEXT 个数] | [FETCH NEXT 百分比 PERCENT] ROW ONLY在本语法中，FETCH语句放在整体查询语句的最后位置，该语句有3种使用方法：
FETCH FIRST 行数 ROW ONLY：取得前N行记录；
OFFSET 开始位置 ROWS FETCH NEXT 个数 ROWS ONLY：取得指定范围的记录；
FETCH NEXT 百分比 PERCENT ROWS ONLY：按照百分比取得记录；
通过实验案例来进行验证：
案例1：先查询scott.emp表的所有数据，然后取其前5行。
SYS@PRODCDB> select empno, ename, job, sal from scott.emp;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
6688 aa CLERK 1900
15 rows selected.
SYS@PRODCDB> select empno, ename, job, sal
2 from scott.emp
3 fetch first 5 row only;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
案例2：取得表中第3~4行数据(所有行的记录都是从0开始的)
SYS@PRODCDB> select empno, ename, job, sal
2 from scott.emp
3 offset 2 rows fetch next 2 rows only;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
案例3：按照10%和20%的百分比取数据
SYS@PRODCDB> select empno, ename, job, sal
2 from scott.emp
3 fetch next 10 percent rows only;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
SYS@PRODCDB> select empno, ename, job, sal
2 from scott.emp
3 fetch next 20 percent rows only;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
######oracle 19c
oracle 数据库扩展数据类型长度 32k Strings
12c开始 VARCHAR2, NAVARCHAR2 以及 RAW 这些数据类型的 大小可以扩展至32K 字节。
能够很大程度缓解之前长度偏少不得不使用CLOB、BLOB的情况。
开启方法：
1、关闭cdb
shutdown immediate;
2、启动数据库到upgrade模式
startup upgrade;
3、在cdb内修改初始化参数 MAX_STRING_SIZE
alter session set container=cdb$root;
alter system set max_string_size=extended scope=spfile;
4、以sys用户执行扩展脚本
@?/rdbms/admin/utl32k.sql
5、重启数据库到normal模式
shutdown immediate;
startup;
6、执行脚本编译无效对象
@?/rdbms/admin/utlrp.sql
参考:
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/MAX_STRING_SIZE.html#GUID-D424D23B-0933-425F-BC69-9C0E6724693C
————————————————
版权声明：本文为CSDN博主「数据库人生」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/ctypyb2002/article/details/120039699
#### ORACLE 19C
19c:ORA-01950
19c:ORA-01950: no privileges on tablespace 解决办法：
CREATE PLUGGABLE DATABASE testpdb2
ADMIN USER testadmin IDENTIFIED BY testadmin
ROLES = (dba)
DEFAULT TABLESPACE sales
DATAFILE 'testpdb2.dbf' SIZE 10M AUTOEXTEND ON
FILE_NAME_CONVERT = ('pdbseed','testpdb2')
STORAGE (MAXSIZE 2G)
PATH_PREFIX = '/oracledata/TESTDB/testpdb2';
alter pluggable database testpdb close ;
alter pluggable database testpdb open read only ;
sqlplus testadmin/testadmin@127.0.0.1:1521/testpdb2
insert into t select 1 from dual ;报如下错，是因为创建用户时没指定quota
19c:ORA-01950: no privileges on tablespace 'SALES'
解决办法如下：
ALTER USER testadmin QUOTA UNLIMITED ON sales;
GRANT RESOURCE TO testadmin;
————————————————
版权声明：本文为CSDN博主「xiaoxiao树」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u010719917/article/details/105552905
##oracle 19c
客户不想升级jdbc 和oracle_home版本，在数据库已经从11g 升级到19C 的大环境下。让oracle 19c 可以向下兼容10G 和老的JDBC
配置sqlnet.ora 文件如下：
# sqlnet.ora Network Configuration File: /crbank/abm/app/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
###oracle 19c
###
Error 16063 archiving LNO:n to '<db>' (Doc ID 2722180.1)
##
In the dataguard environment, v$dataguard_status is showing numerous Error 16063
The primary and standby are in sync.
Alert log:
Error 16063 archiving LNO:2 to 'ORCL_STBY'
CAUSE
