OraFAQ Forum: General » Query about PDB space consumption in XE

Home » Other » General » Query about PDB space consumption in XE (Oracle XE 18C)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query about PDB space consumption in XE [message #679985]

Thu, 16 April 2020 02:35

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?

Report message to a moderator

Re: Query about PDB space consumption in XE [message #679986 is a reply to message #679985]

Thu, 16 April 2020 04:09

John Watson
Messages: 8957
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.

Report message to a moderator

Re: Query about PDB space consumption in XE [message #680022 is a reply to message #679986]

Sun, 19 April 2020 00:25

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?

Report message to a moderator

Re: Query about PDB space consumption in XE [message #680023 is a reply to message #680022]

Sun, 19 April 2020 01:02

John Watson
Messages: 8957
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.

Report message to a moderator

Re: Query about PDB space consumption in XE [message #680025 is a reply to message #680023]

Sun, 19 April 2020 09:36

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:

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:

Any idea why I am observing this difference when checking from sql developer?

Report message to a moderator

Re: Query about PDB space consumption in XE [message #680027 is a reply to message #680025]

Sun, 19 April 2020 11:28

John Watson
Messages: 8957
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.

Report message to a moderator

Re: Query about PDB space consumption in XE [message #680114 is a reply to message #680027]

Wed, 22 April 2020 07:44

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.

Report message to a moderator

Re: Query about PDB space consumption in XE [message #680128 is a reply to message #680114]

Thu, 23 April 2020 04:03

John Watson
Messages: 8957
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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 05 19:20:59 CST 2024