ORACLE 表領域 ＆ ユーザ作成

表領域作成

# su - oracle

$ sqlplus / as sysdba

show con_name

----------------------------------------------------------------------------------------------------------------

※ 既に存在している場合は、削除

show pdbs

alter pluggable database arcsdbms close;

drop pluggable database arcsdbms including datafiles;

drop tablespace arcsdbms_tbl    including contents and datafiles cascade constraints;

drop tablespace arcsdbms_tmp including contents and datafiles cascade constraints;

-----------------------------------------------------------------------------------------------------------------

show pdbs

create pluggable database arcsdbms admin user admin identified by password file_name_convert=('ORCLCDB', 'ARCSDBMS');

alter pluggable database arcsdbms open;

show pdbs
ALTER SESSION SET CONTAINER=ARCSDBMS;

grant connect, create any table, drop any table, unlimited tablespace ,  select any table , update  any table , delete  any table , insert any table,  create any index, drop any index  to admin;

grant alter table  to admin;         # 権限エラー

-------------------------------------------------------------------------------------------------------------------

!mkdir  opt/oracle/oradata/ARCSDBMS/

CREATE TABLESPACE "ARCSDBMS_TBL" DATAFILE '/opt/oracle/oradata/ARCSDBMS/arcsdbms_tbl.dbf' SIZE 64M
AUTOEXTEND ON NEXT 32M MAXSIZE 1G LOGGING EXTENT MANAGEMENT
LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 CREATE TEMPORARY TABLESPACE "ARCSDBMS_TMP"  tempfile '/opt/oracle/oradata/ARCSDBMS/arcsdbms_tmp.dbf' size 64M AUTOEXTEND ON MAXSIZE UNLIMITED;

CREATE TABLESPACE "ARCSDBCB_TBL" DATAFILE '/opt/oracle/oradata/ARCSDBMS/arcsdbcb_tbl.dbf' SIZE 64M
AUTOEXTEND ON NEXT 32M MAXSIZE 1G LOGGING EXTENT MANAGEMENT
LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE "ARCSDBCB_IDX" DATAFILE '/opt/oracle/oradata/ARCSDBMS/arcsdbcb_idx.dbf' SIZE 64M

AUTOEXTEND ON NEXT 32M MAXSIZE 1G LOGGING EXTENT MANAGEMENT
LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 CREATE TEMPORARY TABLESPACE "ARCSDBMS_TMP"  tempfile '/opt/oracle/oradata/ARCSDBMS/arcsdbcb_tmp.dbf' size 64M AUTOEXTEND ON MAXSIZE UNLIMITED;

create user arcsdbcb identified by password00 default tablespace "ARCSDBCB_TBL"  temporary tablespace "ARCSDBMS_TMP" container=current;

grant connect, create any table, drop any table, unlimited tablespace ,  select any table , update  any table , delete  any table , insert any table  to arcsdbcb;

----------------------------------------------------------------------------------------------------------------------

※ プライマリーキーの作成とインデックスの作成、

ALTER TABLE "ARCSDBCB"."CCA021" ADD CONSTRAINT "CCA021_PRIMARY" PRIMARY KEY ("ACODE", "KGCODE", "KCCODE")

  

CREATE UNIQUE INDEX "ARCSDBCB"."CCA021_PRIMARY" ON "ARCSDBCB"."CCA021" ("ACODE", "KGCODE", "KCCODE") 

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "arcsdbcb_tbl" ;

----------------------------------------------------------------------------------------------------------------------

【表領域変更/削除】

ALTER USER admin DEFAULT TABLESPACE ARCSDBMS_TBL;

DROP TABLESPACE ARCSDBCB_TMP INCLUDING CONTENTS AND DATAFILES;

【切替】

1）プラガブル・データベース

       ALTER SESSION SET CONTAINER=ARCSDBMS;

2）session

       sqlplus      admin/passwd@localhost:1521/arcsdbms

       sqlplus       zabbix_mon/zbx%xxxxxx%@zabbix  

       connect    admin/passwd@localhost:1521/arcsdbms;

       connect  / as sysdba    

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

--【プラガブルDB作成】

conn / as sysdba

create pluggable database arcsdbms admin user admin identified by xxxxx00;

--create pluggable database arcsdbms admin user admin identified by xxxxx00 file_name_convert=('ORCLCDB', 'ARCSDBMS');

alter pluggable database arcsdbms open;

alter session set container=arcsdbms;

grant connect, create any table, drop any table, unlimited tablespace ,  select any table , update  any table , delete  any table , insert any table,  create any index, drop any index  to admin;

--【表領域作成】

!mkdir /opt/oracle/oradata/ARCSDBMS

CREATE TABLESPACE "ARCSDBMS_TBL" DATAFILE            '/opt/oracle/oradata/ARCSDBMS/arcsdbms_tbl.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TEMPORARY TABLESPACE "ARCSDBMS_TMP"  tempfile '/opt/oracle/oradata/ARCSDBMS/arcsdbms_tmp.dbf' size 64M AUTOEXTEND ON MAXSIZE UNLIMITED;

CREATE TABLESPACE   "ARCSDBCB_TBL" DATAFILE '/opt/oracle/oradata/ARCSDBMS/arcsdbcb_tbl.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE   "ARCSDBCB_IDX" DATAFILE '/opt/oracle/oradata/ARCSDBMS/arcsdbcb_idx.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE   "ARCSDBCS_TBL" DATAFILE '/opt/oracle/oradata/ARCSDBMS/arcsdbcs_tbl.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE   "ARCSDBCS_IDX" DATAFILE '/opt/oracle/oradata/ARCSDBMS/arcsdbcs_idx.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE   "ARCSDBSS_TBL" DATAFILE '/opt/oracle/oradata/ARCSDBMS/arcsdbss_tbl.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE   "ARCSDBSS_IDX" DATAFILE '/opt/oracle/oradata/ARCSDBMS/arcsdbss_idx.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE   "ARCSDBPA_TBL" DATAFILE '/opt/oracle/oradata/ARCSDBMS/arcsdbpa_tbl.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE   "ARCSDBPA_IDX" DATAFILE '/opt/oracle/oradata/ARCSDBMS/arcsdbpa_idx.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

--【ユーザ作成】

create user arcsdbcb identified by xxxxx00 default tablespace "ARCSDBCB_TBL"  temporary tablespace "ARCSDBMS_TMP" container=current;

create user arcsdbcs identified by xxxxx00 default tablespace "ARCSDBCS_TBL"  temporary tablespace "ARCSDBMS_TMP" container=current;

create user arcsdbss identified by xxxxx00 default tablespace "ARCSDBSS_TBL"  temporary tablespace "ARCSDBMS_TMP" container=current;

create user arcsdbpa identified by xxxxx00 default tablespace "ARCSDBSS_TBL"  temporary tablespace "ARCSDBMS_TMP" container=current;

grant connect, create any table, drop any table, unlimited tablespace ,  select any table , update  any table , delete  any table , insert any table  to arcsdbcb;

grant connect, create any table, drop any table, unlimited tablespace ,  select any table , update  any table , delete  any table , insert any table  to arcsdbcs;

grant connect, create any table, drop any table, unlimited tablespace ,  select any table , update  any table , delete  any table , insert any table  to arcsdbss;

grant connect, create any table, drop any table, unlimited tablespace ,  select any table , update  any table , delete  any table , insert any table  to arcsdbpa;

-- -----------------------------------------------------------------------------------------

--【全削除】

drop user arcsdbcb cascade;

drop user arcsdbss cascade;

drop user arcsdbpa cascade;

drop user arcsdbcs cascade;

drop tablespace arcsdbcb_tbl    including contents and datafiles cascade constraints;

drop tablespace arcsdbcb_idx    including contents and datafiles cascade constraints;

drop tablespace arcsdbcs_tbl    including contents and datafiles cascade constraints;

drop tablespace arcsdbcs_idx    including contents and datafiles cascade constraints;

drop tablespace arcsdbss_tbl    including contents and datafiles cascade constraints;

drop tablespace arcsdbss_idx    including contents and datafiles cascade constraints;

drop tablespace arcsdbpa_tbl    including contents and datafiles cascade constraints;

drop tablespace arcsdbpa_idx    including contents and datafiles cascade constraints;

drop tablespace arcsdbms_tbl    including contents and datafiles cascade constraints;

drop tablespace arcsdbms_tmp    including contents and datafiles cascade constraints;

conn / as sysdba

alter pluggable database arcsdbms close;

drop pluggable database arcsdbms including datafiles;

!rmdir /opt/oracle/oradata/ARCSDBMS

-- -----------------------------------------------------------------------------------------

conn admin/xxxxx00@localhost:1521/arcsdbms