82 - [Oracle 19C] Helpful queries for tablespace

 

 

SQL> conn pdb10_admin/Manager123#@pdb10
Connected.


SQL> show user
USER is "PDB10_ADMIN"


SQL> show con_name
CON_NAME
------------------------------
PDB10

 

 

 

SQL> select * from database_properties where property_name 
in ('GLOBAL_DB_NAME','DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');

/*
PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION                             
------------------------------ -------------------- ----------------------------------------
GLOBAL_DB_NAME                 PDB10.COM            Global database name                    
DEFAULT_PERMANENT_TABLESPACE   USERS                Name of default permanent tablespace    
DEFAULT_TEMP_TABLESPACE        TEMP                 Name of default temporary tablespace    
*/

 

 

 

create user hrms identified by hrms;
/*
User HRMS created.
*/

 

 

 

-- SO WE CAN SEE THAT THE USER HRMS WILL HAVE
-- DEFAULT TABLESPACE "USERS" 
-- TEMPORARY TABLESPACE "TEMP"

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



CREATE TABLE HRMS.EMP ( EMP_ID NUMBER, ENAME VARCHAR2(100) );
/*
Table HRMS.EMP created.
*/



INSERT INTO   HRMS.EMP VALUES (1,'ford');
/*
Error starting at line : 55 in command -
INSERT INTO   HRMS.EMP VALUES (1,'ford')
Error at Command Line : 55 Column : 20
Error report -
SQL Error: ORA-01950: no privileges on tablespace 'USERS'
01950. 00000 -  "no privileges on tablespace '%s'"
*Cause:    User does not have privileges to allocate an extent in the
           specified tablespace.
*Action:   Grant the user the appropriate system privileges or grant the user
           space resource on the tablespace.
*/



# 테이블 스페이스 사용권한이 없으면 데이터를 삽입할 수 없다
grant unlimited tablespace to hrms;
/*
Grant succeeded.
*/



INSERT INTO   HRMS.EMP VALUES (1,'ford');
INSERT INTO   HRMS.EMP VALUES (2,'sami');
commit;
/*
1 row inserted.
1 row inserted.
Commit complete.
*/

 

 

 

--select * from dba_tables

select owner, table_name, tablespace_name from dba_tables
where owner='HRMS'; --SO THE TABLE WILL BE USERS TABLESPACE
/*
OWNER	   TABLE_NAME	   TABLESPACE_NAME
---------- --------------- ------------------------------
HRMS	   EMP		   USERS
*/

 

 

 

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

 

 

 

 

select * from v$tablespace;

 

 

 

 

--data_files info
--BYTES Size of the file in bytes, with metadata
--BLOCKS Size of the file in Oracle blocks
--MAXBYTES Maximum file size in bytes
--MAXBLOCKS Maximum file size in blocks
--INCREMENT_BY Number of tablespace blocks used as autoextension increment
--USER_BYTES The size of the file available for user data. without metadata.
--USER_BLOCKS Number of blocks which can be used by the data
--https://docs.oracle.com/database/121/REFRN/GUID-0FA17297-73ED-4B5D-B511-103993C003D3.htm#REFRN23049

select * from dba_data_files;

 

 

 

 

select * from v$datafile;

 

 

 

 

--temp_files info
select * from dba_temp_files;

 

 

 

 

select * from v$tempfile;