83 - [Oracle 19C] Create permanent tablespace - example

 

 

• Create permanent tablespace - example

SHOW USER
/*
USER is "PDB10_ADMIN"
*/


SHOW CON_NAME
/*
CON_NAME 
------------------------------
PDB10
*/

 

 

 

create tablespace t1;
/*
테이블 스페이스의 용도를 정하지 않으면 에러가 발생한다.

Error starting at line : 15 in command -
create tablespace t1
Error report -
ORA-02199: missing DATAFILE/TEMPFILE clause
02199. 00000 -  "missing DATAFILE/TEMPFILE clause"
*Cause:    A CREATE TABLESPACE statement has no DATAFILE/TEMPFILE clause.
*Action:   Specify a DATAFILE/TEMPFILE clause.
*/


/*
Oracle Managed Files
When creating a tablespace, either a permanent tablespace or an undo tablespace,
the DATAFILE clause is optional. 
When you include the DATAFILE clause, the file name is optional. 
If the DATAFILE clause or file name is not provided, then the following rules
apply:
� If the DB_CREATE_FILE_DEST initialization parameter is specified, then an Oracle
managed data file is created in the location specified by the parameter.
� If the DB_CREATE_FILE_DEST initialization parameter is not specified, then the
statement creating the data file fails
*/


show parameter DB_CREATE_FILE_DEST
/*
NAME                TYPE   VALUE 
------------------- ------ ----- 
db_create_file_dest string    
*/


alter system set DB_CREATE_FILE_DEST='/u01/app/oracle/oradata/ORCL/PDB10'; 
/*
System SET altered.
*/


show parameter DB_CREATE_FILE_DEST
/*
NAME                TYPE   VALUE                              
------------------- ------ ---------------------------------- 
db_create_file_dest string /u01/app/oracle/oradata/ORCL/PDB10 
*/

 

 

 

create tablespace t1;
/*
Tablespace T1 created.
*/



SELECT DBMS_METADATA.GET_DDL('TABLESPACE','T1') FROM dual;
/*
  CREATE TABLESPACE "T1" DATAFILE 
  SIZE 104857600
  AUTOEXTEND ON NEXT 1
  
  
[oracle@test datafile]$ pwd
/u01/app/oracle/oradata/ORCL/PDB10/ORCL/0BD9769D27CD39DCE063E102A8C059FB/datafile

[oracle@test datafile]$ ll
합계 102408
-rw-r----- 1 oracle oinstall 104865792 12월 11 14:41 o1_mf_t1_lqg7x03o_.dbf

*/

 

 

 

select TABLESPACE_NAME,BLOCK_SIZE,STATUS,CONTENTS,LOGGING,SEGMENT_SPACE_MANAGEMENt,COMPRESS_FOR
from dba_tablespaces;

 

 

 

 

select * from v$tablespace

 

 

 

 

select * from dba_data_files

 

 

 

create user hrms2 identified by hrms2 
default tablespace t1;
/*
User HRMS2 created.
*/


SELECT DBMS_METADATA.GET_DDL('USER','HRMS2') FROM dual;
/*
CREATE USER "HRMS2" IDENTIFIED BY VALUES 'S:40789A8BB7A89980549EF96D216
*/


CREATE TABLE HRMS2.TEST123 ( N NUMBER );
/*
Table HRMS2.TEST123 created.
*/

 

 

 

select * from dba_tables where owner='HRMS2';