建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
查看當(dāng)前容器和當(dāng)前用戶(hù)
創(chuàng)新互聯(lián)自2013年起，先為鄂城等服務(wù)建站，鄂城等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢(xún)服務(wù)。為鄂城企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問(wèn)題。
show con_name user
select sys_context('userenv', 'con_name'),sys_context('userenv','session_user') from dual;
查看所有PDB
show pdbs
select con_id, dbid, guid, name , open_mode from v$pdbs;
啟動(dòng)PDB：
alter pluggable database all open; --開(kāi)啟所有PDB
alter pluggable database PDBNAME open; --開(kāi)啟名稱(chēng)為PDBNAME的PDB
alter session set container=PDBNAME;--切換名稱(chēng)為PDBNAME的PDB里面去開(kāi)啟
startup
關(guān)閉PDB：
alter pluggable database all close immediate; --關(guān)閉所有PDB,不加immediate就是默認(rèn)的normal
alter pluggable database PDBNAME close immediate;--關(guān)閉名稱(chēng)為PDBNAME的PDB,不加immediate就是默認(rèn)的normal
alter session set container=PDBNAME;--切換名稱(chēng)為PDBNAME的PDB里面去關(guān)閉
shutdown immediate
創(chuàng)建一個(gè)新的PDB：（已經(jīng)存在db_create_file_dest的情況下）
create pluggable database test admin user admin identified by admin;
alter pluggable database test open; --將test打開(kāi)
根據(jù)seed模板創(chuàng)建一個(gè)新PDB(不存在db_create_file_dest的情況下,必須加上file_name_convert)
create pluggable database pdb1 admin user pdb1 identified by pdb1 file_name_convert=('/u01/app/oracle/oradata/ocp/pdbseed','/u01/app/oracle/oradata/ocp1/pdb1');
alter pluggable database pdb1 open; --將test打開(kāi)
克隆PDB
create pluggable database test2 from test; --test必須是打開(kāi)的，才可以作為克隆源
alter pluggable database test2 open; --然后打開(kāi)這個(gè)pdb
刪除PDB
alter pluggable database test2 close immediate; --關(guān)閉之后才能刪除
drop pluggable database test2 including datafiles; --刪除PDB test2并同時(shí)把數(shù)據(jù)文件也從磁盤(pán)上刪除了,必須加上including datafiles，不加including datafiles或加keep datafiles都會(huì)報(bào)錯(cuò)ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged
unplug拔下某個(gè)數(shù)據(jù)庫(kù)（必須要into到具體的xml格式的文件）
SQL> alter pluggable database pocp1 close immediate;--之后執(zhí)行cdb_data_files查不到該pdb的數(shù)據(jù)文件，但是該pdb的數(shù)據(jù)文件還在, show pdbs還可以看到該pdb
SQL> alter pluggable database pocp1 unplug into '/home/oracle/pocp1.xml';--之后執(zhí)行cdb_data_files查不到該pdb的數(shù)據(jù)文件，但是該pdb的數(shù)據(jù)文件還在, show pdbs還可以看到該pdb
SQL> drop pluggable database pocp1; --unplug后drop database不加including datafiles就是默認(rèn)的keep datafiles;該pdb的數(shù)據(jù)文件還在,show pdbs看不到該pdb;加了including datafiles的話(huà),該pdb的數(shù)據(jù)文件被刪除了
drop pluggable database pocp1=drop pluggable database pocp1 keep datafiles
插上這個(gè)數(shù)據(jù)庫(kù)（上面的拔下操作執(zhí)不執(zhí)行第三步drop操作的話(huà)，執(zhí)行如下語(yǔ)句會(huì)報(bào)錯(cuò)ORA-65012: Pluggable database pocp1 already exists，上面的拔下操作執(zhí)第三步時(shí)加了including datafiles則執(zhí)行如下語(yǔ)句會(huì)報(bào)錯(cuò)ORA-19505: failed to identify file,ORA-27037: unable to obtain file status)
SQL> create pluggable database pocp1 using '/home/oracle/pocp1.xml' nocopy tempfile reuse;
SQL> alter pluggable database pocp1 open;
插上一個(gè)數(shù)據(jù)庫(kù)時(shí)可以修改PDB名稱(chēng)，/home/oracle/pocp1.xml是pocp1創(chuàng)建的，該xml文件中顯示
SQL>create pluggable database TDB101 using '/home/oracle/pocp1.xml' nocopy tempfile reuse;
查詢(xún)CDB$ROOT的數(shù)據(jù)文件
select file_name from cdb_data_files where con_id=1;
查詢(xún)某個(gè)PDB的數(shù)據(jù)文件
select file_name from cdb_data_files a,cdb_pdbs b where a.con_id=b.con_id and b.pdb_name='XX'
查詢(xún)某個(gè)數(shù)據(jù)文件對(duì)應(yīng)的PDB
select pdb_name from cdb_data_files a,cdb_pdbs b where a.con_id=b.con_id and a.file_name like '%XX%';
查詢(xún)PDB$SEED的數(shù)據(jù)文件
alter session set "_exclude_seed_cdb_view"=FALSE;
select file_name from cdb_data_files where con_id=2;
每個(gè)PDB執(zhí)行如下語(yǔ)句只能看到自己這個(gè)容器下的數(shù)據(jù)文件，CDB才能看到所有容器的數(shù)據(jù)文件
select file_name,file_id,CON_ID from cdb_data_files;
每個(gè)容器執(zhí)行如下語(yǔ)句只能看到自己這個(gè)容器下的數(shù)據(jù)文件，CDB也只能看到自己這個(gè)容器的數(shù)據(jù)文件
select file_name,file_id,CON_ID from cdb_data_files;
本文名稱(chēng)：12C多租戶(hù)關(guān)于CDB、PDB的常用SQL語(yǔ)句
文章轉(zhuǎn)載：http://www.ef60e0e.cn/article/poehoc.html
