Home » Other » General » Query about PDB space consumption in XE (Oracle XE 18C)
Query about PDB space consumption in XE [message #679985] Thu, 16 April 2020 02:35 Go to next message
Akhtar Usman
Messages: 4
Registered: April 2020
Junior Member
Hi,

I just installed Oracle18c XE and storage is limited to 12G.

But I am confused about space occupied by PDB XEPDB1 & SEEDPDB. Both these are having their own datafiles and I can see physical size of datafiles on disk is in GBs.




Does it mean all this space will be consumed for 12G of total space?
Re: Query about PDB space consumption in XE [message #679986 is a reply to message #679985] Thu, 16 April 2020 04:09 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

If you look at the installation guide,
https://docs.oracle.com/en/database/oracle/oracle-database/18/xeinl/oracle-database-xe-user-data-limitations.html
you will see that the 12GB limit applies to user data. The pdb$seed container is not user data.
Re: Query about PDB space consumption in XE [message #680022 is a reply to message #679986] Sun, 19 April 2020 00:25 Go to previous messageGo to next message
Akhtar Usman
Messages: 4
Registered: April 2020
Junior Member
Thanks John for your reply. I got it that pdbseed is not user data.
But what about pdb$root & xepdb1?
1) With default installation, both have their own data files and occupying space (as seen from dba_data_files & v$data files). This space is occupied even before I can insert any user data in database. This will contribute to 12G limit?
2) physical datafile size on file system is high as compared to size shown in dba_data_files. Any clue for this?
Re: Query about PDB space consumption in XE [message #680023 is a reply to message #680022] Sun, 19 April 2020 01:02 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
Have you read the docs on Multitenant yet? When you do, it will become clear that there should be no user data in cdb$root: that is what pluggable containers are for.

As for your second question, you would need to give some evidence to support your assertion.
Re: Query about PDB space consumption in XE [message #680025 is a reply to message #680023] Sun, 19 April 2020 09:36 Go to previous messageGo to next message
Akhtar Usman
Messages: 4
Registered: April 2020
Junior Member
ok for 1st query I was able to sort it out.
For 2nd query,

b/m is snapshot from sql developer where TEMP tablespace is showing ~9GB as Max Bytes:
https://imgur.com/aA1uGzx

whereas actually I set 1GB as Max Bytes for TEMP tablespace which is evident when I query the same thing from sql. In b/m TEMP tablespace is showing 1GB as MAX BYTES:
https://imgur.com/f0MpOFl

Any idea why I am observing this difference when checking from sql developer?
Re: Query about PDB space consumption in XE [message #680027 is a reply to message #680025] Sun, 19 April 2020 11:28 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
I have no idea what you are trying to show. Please use SQL*Plus, and copy/paste the queries and the results. Remember to enclose the text within [code] tags.

Also, when you work out an answer to something it is good forum etiquette to post the solution in order to help others who may have the same questions.
Re: Query about PDB space consumption in XE [message #680114 is a reply to message #680027] Wed, 22 April 2020 07:44 Go to previous messageGo to next message
Akhtar Usman
Messages: 4
Registered: April 2020
Junior Member
Well I regret i wasn't able to explain the query properly as I was unable to display images in my last response.
I will try to elaborate a bit further:

1st query:
After new installation of Oracle XE, how pdb$root, pdbseed & xepdb1 will contribute to storage limit of 12G?
Answer:
pdbseed -> It is a system template. So it wont contribute to user data & space limit.
pdbroot -> Yes, if any user tables or data is created here, it will contribute to space limit.
xepdb1 -> Yes, if any user tables or data is created here, it will contribute to space limit.


2nd query:
I changed max size of tablespace TEMP datafile to 1GB with b/m command:
alter database tempfile 'C:\ORACLE\ORADATA\XE\XEPDB1\TEMP01.DBF' autoextend on maxsize 1024M;
Then I verified above change from dba_temp_files and it was correctly set to 1GB:
select FILE_NAME, TABLESPACE_NAME, MAXBYTES/1024/1024/1024 from dba_temp_files;
But when I am verifying same thing from sql developer tablespaces, TEMP is showing MAXBYTES as 9216M

This can be either some sql developer issue or may be I am missing something here.

Re: Query about PDB space consumption in XE [message #680128 is a reply to message #680114] Thu, 23 April 2020 04:03 Go to previous message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
Quote:
This can be either some sql developer issue or may be I am missing something here.
Stop messing about with GUI tools, and use SQL*Plus. That way you can show what queries you are running and what the results are.
Previous Topic: New to OCI, then I need some free little and simply projects
Next Topic: Storage Space Requirements in OLAP and OLTP
Goto Forum:
  


Current Time: Mon Sep 28 15:02:04 CDT 2020