当前位置：网站首页 > R语言数据分析 > 正文

oracle怎么写代码（oracle 代码）

R语言数据分析 来源： 网络 编辑：小编 更新时间：2025-03-31 23:45:07 浏览量：24



使用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原文如下：

db_name='ORCL'

memory_target=1G

processes = 150

audit_file_dest=' /admin/orcl/adump'

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest=' /fast_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_dest=' '

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

# devices

control_files = (ora_control1, ora_control2)

compatible ='11.2.0'

将 全局替换成/u01/app/oracle。将数据库名ORCL全局替换成newcdb。将memory_target替换成sga+pga的方式。控制文件指定到具体的目录。兼容性调整到19c。最重要的是 添加参数enable_pluggable_database=TRUE。复制改名成initnewcdb.ora文件，修改后的样子：

db_name='newcdb'

sga_target=1G

pga_aggregate_target=512M

processes = 150

audit_file_dest='/u01/app/oracle/admin/newcdb/adump'

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_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

参数文件用到的几个目录，必须在实例启动前建好

mkdir -p /u01/app/oracle/admin/newcdb/adump

mkdir -p /u01/app/oracle/fast_recovery_area

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;

在空实例中执行，一定会报错。需要把用户默认表空间去掉，CDB中的deftbs和SEED中的usertbs，为什么呢？不知道。反正加上这俩，一定报错。顺便把临时表空间tempts1名字改成了temp，更符合平时使用习惯。修改后的CREATE DATABASE语句如下：

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;

CREATE DATABASE语句运行前的目录必须建好

#root execute

mkdir /u02

chown oracle:oinstall /u02

chmod 775 /u02

#oracle execute

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

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

mkdir -p /u01/logs/my

mkdir -p /u02/logs/my

准备结束，开始建库

设置SID

sqlplus / as sysdba

startup nomount pfile='/home/oracle/initnewcdb.ora'

执行前面修改后的CREATE DATABASE语句

建库完成后执行catcdb.sql脚本，并输入参数

@?/rdbms/admin/catcdb.sql

SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2

Enter value for 1: /tmp

Enter value for 2: create_cdb.log

Enter new password for SYS:

Enter new password for SYSTEM:

Enter temporary tablespace name: temp

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   MOUNTED

SQL> 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 代码）的文章就介绍到这了,更多相关内容请继续浏览下面的相关推荐文章，希望大家都能在编程的领域有一番成就！

  • 上一篇： top18女rapper（top18女rapperSex）
  • 下一篇： arcpy模块（Arcpy模块的特点）

    • 版权声明：

    本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
    如若内容造成侵权、违法违规、事实不符，请将相关资料发送至xkadmin@xkablog.com进行投诉反馈，一经查实，立即处理！


    转载请注明出处，原文链接：https://www.xkablog.com/rfx/21823.html

    # Oracle # 代码 # 怎么

    相关文章：

  • top18女rapper（top18女rapperSex）2025-03-31 23:45:07
  • ifstream写文件（ifstream.read）2025-03-31 23:45:07
  • redis desktop manager 连接集群（redis集群连接池配置）2025-03-31 23:45:07
  • store苹果商店下载（store苹果商店下载不了软件）2025-03-31 23:45:07
  • rk3288开机启动app（开机启动app下载）2025-03-31 23:45:07
  • arcpy模块（Arcpy模块的特点）2025-03-31 23:45:07
  • github docker镜像（docker镜像库）2025-03-31 23:45:07
  • swagger2配置（swagger2配置登录）2025-03-31 23:45:07
  • orcale默认用户（oracle 用户默认表空间）2025-03-31 23:45:07
  • chronyc sources输出详解（chronyc sources显示的结果）2025-03-31 23:45:07
    • ❮ 全屏图片 ❯