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)
)
)
