Oracle 19c 数据库如何修改 pdb名称

数据库信息

SQL> conn / as sysdba
Connected.
SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO

SQL> set lines 120
SQL> col name for a20
SQL> col NETWORK_NAME for a30
SQL> col PDB for a15
SQL> select service_id,name,network_name,creation_date,pdb,con_id from cdb_services;

SERVICE_ID NAME NETWORK_NAME CREATION_ PDB CON_ID
———- ——————– —————————— ——— ————— ———-
14 PDB3 PDB3 16-JUN-22 PDB3 5
1 SYS$BACKGROUND 17-APR-19 CDB$ROOT 1
2 SYS$USERS 17-APR-19 CDB$ROOT 1
5 ora19cXDB ora19cXDB 15-JUN-22 CDB$ROOT 1
6 ora19c ora19c 15-JUN-22 CDB$ROOT 1
8 PDB1 PDB1 15-JUN-22 PDB1 3
10 PDB2 PDB2 15-JUN-22 PDB2 4

7 rows selected.

修改PDB名字

SQL> alter pluggable database pdb3 close;

Pluggable database altered.

SQL> alter pluggable database pdb3 open restricted;

Pluggable database altered.

SQL> select name, open_mode, restricted from v$pdbs;

NAME OPEN_MODE RES
——————– ———- —
PDB$SEED READ ONLY NO
PDB1 READ WRITE NO
PDB2 READ WRITE NO
PDB3 READ WRITE YES

SQL> alter session set container=pdb3;

Session altered.

SQL> alter pluggable database rename global_name to newcust;

Pluggable database altered.

SQL> alter pluggable database close immediate;

Pluggable database altered.

SQL> alter pluggable database open;

Pluggable database altered.

重新连接到CDB验证
SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select name, open_mode, restricted from v$pdbs;

NAME OPEN_MODE RES
——————– ———- —
PDB$SEED READ ONLY NO
PDB1 READ WRITE NO
PDB2 READ WRITE NO
NEWCUST READ WRITE NO

SQL> select service_id,name,network_name,creation_date,pdb,con_id from cdb_services;

SERVICE_ID NAME NETWORK_NAME CREATION_ PDB CON_ID
———- ——————– —————————— ——— ————— ———-
8 PDB1 PDB1 15-JUN-22 PDB1 3
10 PDB2 PDB2 15-JUN-22 PDB2 4
1 NEWCUST NEWCUST 17-JUN-22 NEWCUST 5
1 SYS$BACKGROUND 17-APR-19 CDB$ROOT 1
2 SYS$USERS 17-APR-19 CDB$ROOT 1
5 ora19cXDB ora19cXDB 15-JUN-22 CDB$ROOT 1
6 ora19c ora19c 15-JUN-22 CDB$ROOT 1

7 rows selected.

SQL> alter session set container=newcust;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
————————————————————————————————————————
/opt/app/oracle/oradata/ORA19C/pdb3/system01.dbf
/opt/app/oracle/oradata/ORA19C/pdb3/sysaux01.dbf
/opt/app/oracle/oradata/ORA19C/pdb3/undotbs01.dbf
/opt/app/oracle/oradata/ORA19C/pdb3/user02.dbf
/opt/app/oracle/oradata/ORA19C/pdb3/user01.dbf

SQL> ! mkdir /opt/app/oracle/oradata/ORA19C/newcust

SQL> alter database move datafile ‘/opt/app/oracle/oradata/ORA19C/pdb3/system01.dbf’ to ‘/opt/app/oracle/oradata/ORA19C/newcust/system01.dbf’;

Database altered.

SQL> alter database move datafile ‘/opt/app/oracle/oradata/ORA19C/pdb3/sysaux01.dbf’ to ‘/opt/app/oracle/oradata/ORA19C/newcust/sysaux01.dbf’;

Database altered.

SQL> alter database move datafile ‘/opt/app/oracle/oradata/ORA19C/pdb3/undotbs01.dbf’ to ‘/opt/app/oracle/oradata/ORA19C/newcust/undotbs01.dbf’;

Database altered.

SQL> alter database move datafile ‘/opt/app/oracle/oradata/ORA19C/pdb3/user01.dbf’ to ‘/opt/app/oracle/oradata/ORA19C/newcust/user01.dbf’;

Database altered.

SQL> alter database move datafile ‘/opt/app/oracle/oradata/ORA19C/pdb3/user02.dbf’ to ‘/opt/app/oracle/oradata/ORA19C/newcust/user02.dbf’;

Database altered.

SQL> select file_name from dba_data_files;

FILE_NAME
————————————————————————————————————————
/opt/app/oracle/oradata/ORA19C/newcust/system01.dbf
/opt/app/oracle/oradata/ORA19C/newcust/sysaux01.dbf
/opt/app/oracle/oradata/ORA19C/newcust/undotbs01.dbf
/opt/app/oracle/oradata/ORA19C/newcust/user01.dbf
/opt/app/oracle/oradata/ORA19C/newcust/user02.dbf

SQL> select file_name from dba_temp_files;

FILE_NAME
————————————————————————————————————————
/opt/app/oracle/oradata/ORA19C/pdb3/temp01.dbf

SQL> alter database move tempfile ‘/opt/app/oracle/oradata/ORA19C/pdb3/temp01.dbf’ to ‘/opt/app/oracle/oradata/ORA19C/newcust/temp01.dbf’;
alter database move tempfile ‘/opt/app/oracle/oradata/ORA19C/pdb3/temp01.dbf’ to ‘/opt/app/oracle/oradata/ORA19C/newcust/temp01.dbf’
*
ERROR at line 1:
ORA-00905: missing keyword

SQL> select tablespace_name,file_name from dba_temp_files;

TABLESPACE_NAME
——————————
FILE_NAME
————————————————————————————————————————
TEMP
/opt/app/oracle/oradata/ORA19C/pdb3/temp01.dbf

SQL> alter tablespace temp add tempfile ‘/opt/app/oracle/oradata/ORA19C/newcust/temp01.dbf’;
alter tablespace temp add tempfile ‘/opt/app/oracle/oradata/ORA19C/newcust/temp01.dbf’
*
ERROR at line 1:
ORA-01119: error in creating database file ‘/opt/app/oracle/oradata/ORA19C/newcust/temp01.dbf’
ORA-17610: file ‘/opt/app/oracle/oradata/ORA19C/newcust/temp01.dbf’ does not exist and no size specified
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

SQL> alter tablespace temp add tempfile ‘/opt/app/oracle/oradata/ORA19C/newcust/temp01.dbf’ size 50M reuse;

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
————————————————————————————————————————
/opt/app/oracle/oradata/ORA19C/newcust/temp01.dbf
/opt/app/oracle/oradata/ORA19C/pdb3/temp01.dbf

SQL> alter database tempfile ‘/opt/app/oracle/oradata/ORA19C/pdb3/temp01.dbf’ drop including datafiles;

Database altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
————————————————————————————————————————
/opt/app/oracle/oradata/ORA19C/newcust/temp01.dbf

SQL> ! rmdir /opt/app/oracle/oradata/ORA19C/pdb3

SQL> alter session set container=cdb$root;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 NEWCUST READ WRITE NO

[oracle@db13105 dump_dir]$ sqlplus report/report@pdb3

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Jun 17 15:16:57 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

[oracle@db13105 admin]$ vim tnsnames.ora
newcust =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db13105)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = newcust)
)
)


数据运维技术 » Oracle 19c 数据库如何修改 pdb名称