oracle怎么写代码（oracle 代码）

大家好，我是讯享网，很高兴认识大家。

使用dbca方式创建数据库方便快速，但是臃肿，生成的数据库中包含大量预先部署组件。官方还提供了一种纯净建库方案，使用CREATE DATABASE语句。

本文参考网址：

https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/creating-and-configuring-a-cdb.html#GUID-54C5126C-427A-4BFE-81BF-950FB1A062D5

SQL语句要运行，需要一个可用的数据库实例。启动实例，需要一个数据库参数文件。这个参数文件，可以从\(ORACLE_HOME/dbs/init.ora文件修改得到。省略注释，init.ora原文如下：<blockquote>db_name='ORCL'memory_target=1Gprocesses = 150audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'audit_trail ='db'db_block_size=8192db_domain=''db_recovery_file_dest='<ORACLE_BASE>/fast_recovery_area'db_recovery_file_dest_size=2Gdiagnostic_dest='<ORACLE_BASE>'dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'open_cursors=300 remote_login_passwordfile='EXCLUSIVE'undo_tablespace='UNDOTBS1'# You may want to ensure that control files are created on separate physical# devicescontrol_files = (ora_control1, ora_control2)compatible ='11.2.0'</blockquote>将<ORACLE_BASE>全局替换成/u01/app/oracle。将数据库名ORCL全局替换成newcdb。将memory_target替换成sga+pga的方式。控制文件指定到具体的目录。兼容性调整到19c。最重要的是添加参数enable_pluggable_database=TRUE。复制改名成initnewcdb.ora文件，修改后的样子：<blockquote> db_name='newcdb'sga_target=1Gpga_aggregate_target=512Mprocesses = 150audit_file_dest='/u01/app/oracle/admin/newcdb/adump'audit_trail ='db'db_block_size=8192db_domain=''db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'db_recovery_file_dest_size=2Gdiagnostic_dest='/u01/app/oracle'dispatchers='(PROTOCOL=TCP) (SERVICE=newcdbXDB)'open_cursors=300 remote_login_passwordfile='EXCLUSIVE'undo_tablespace='UNDOTBS1'control_files = (/u01/app/oracle/oradata/newcdb/control01.ctl, /u01/app/oracle/oradata/newcdb/control02.ctl)compatible ='19.0.0'enable_pluggable_database=TRUE</blockquote>参数文件用到的几个目录，必须在实例启动前建好<blockquote> mkdir -p /u01/app/oracle/admin/newcdb/adumpmkdir -p /u01/app/oracle/fast_recovery_area</blockquote>官方给出的CREATE DATABASE语句如下： <blockquote> CREATE DATABASE newcdb USER SYS IDENTIFIED BY sys_password USER SYSTEM IDENTIFIED BY system_password LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512, GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512, GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M BLOCKSIZE 512 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 1024 CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16 EXTENT MANAGEMENT LOCAL DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED DEFAULT TABLESPACE deftbs DATAFILE '/u01/app/oracle/oradata/newcdb/deftbs01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/app/oracle/oradata/newcdb/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED UNDO TABLESPACE undotbs1 DATAFILE '/u01/app/oracle/oradata/newcdb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/', '/u01/app/oracle/oradata/pdbseed/') SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED SYSAUX DATAFILES SIZE 100M USER_DATA TABLESPACE usertbs DATAFILE '/u01/app/oracle/oradata/pdbseed/usertbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED LOCAL UNDO ON;</blockquote>在空实例中执行，一定会报错。需要把用户默认表空间去掉，CDB中的deftbs和SEED中的usertbs，为什么呢？不知道。反正加上这俩，一定报错。顺便把临时表空间tempts1名字改成了temp，更符合平时使用习惯。修改后的CREATE DATABASE语句如下：<blockquote> CREATE DATABASE newcdb USER SYS IDENTIFIED BY sys_password USER SYSTEM IDENTIFIED BY system_password LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512, GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512, GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M BLOCKSIZE 512 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 1024 CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16 EXTENT MANAGEMENT LOCAL DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/oradata/newcdb/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED UNDO TABLESPACE undotbs1 DATAFILE '/u01/app/oracle/oradata/newcdb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/', '/u01/app/oracle/oradata/pdbseed/') SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED SYSAUX DATAFILES SIZE 100M LOCAL UNDO ON;</blockquote>CREATE DATABASE语句运行前的目录必须建好<blockquote> #root executemkdir /u02chown oracle:oinstall /u02chmod 775 /u02#oracle executemkdir -p /u01/app/oracle/oradata/newcdbmkdir -p /u01/app/oracle/oradata/pdbseedmkdir -p /u01/logs/mymkdir -p /u02/logs/my</blockquote>准备结束，开始建库设置SID<blockquote>export ORACLE_SID=newcdb </blockquote>启动实例 <blockquote>sqlplus / as sysdbastartup nomount pfile='/home/oracle/initnewcdb.ora'</blockquote>执行前面修改后的CREATE DATABASE语句建库完成后执行catcdb.sql脚本，并输入参数<blockquote> @?/rdbms/admin/catcdb.sql SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2Enter value for 1: /tmpEnter value for 2: create_cdb.logEnter new password for SYS: Enter new password for SYSTEM: Enter temporary tablespace name: temp</blockquote>等待半小时以上，脚本才能执行完。sga和redo logfile调大一些，等待时间能够变短，我看到alert日志中多次出现Checkpoint not complete。此时 <blockquote>SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB\)SEED READ ONLY NO

新建pdb验证前，新建pdb所在目录

mkdir -p /u01/app/oracle/oradata/salespdb

讯享网

新建pdb

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY pwd FILE_NAME_CONVERT = (‘/u01/app/oracle/oradata/pdbseed/’, ‘/u01/app/oracle/oradata/salespdb/’);

验证

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

———- —————————— ———- ———-

2 PDB\(SEED READ ONLY NO 3 SALESPDB MOUNTEDSQL> alter pluggable database all open; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB\)SEED READ ONLY NO

3 SALESPDB READ WRITE NO

手工建库完成。缺少的数据库缺省用户表空间，可以手工添加，不影响正常使用。

oracle怎么写代码（oracle 代码）

相关推荐