OraFAQ Forum: Server Administration » Query to get the maxsize and unlimited of the TableSpace

Home » RDBMS Server » Server Administration » Query to get the maxsize and unlimited of the TableSpace (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production)

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

Query to get the maxsize and unlimited of the TableSpace [message #677278]

Mon, 09 September 2019 12:57

wtolentino
Messages: 421
Registered: March 2005

Senior Member

CREATE TABLESPACE hr_tablespace
DATAFILE '+DATA001/App/DATAFILE/datafile.001.0000001' SIZE 60M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

using a query to get info from the dba views or data dictionary views. which views/tables and columns should i use to get the info based on the DDL above? so far what i have is this

select tablespace_name, 
       FILE_NAME, 
       BYTES/(1024*1024) filesize, 
       MAXBYTES/(1024*1024) maxbytes, 
       increment_by/(1024*1024) increment_by, 
       AUTOEXTENSIBLE 
  from DBA_DATA_FILES
 where tablespace_name;

but it still missing info on how i can get the MAXSIZE and unlimited. please advise. thanks.

Attachment: TablespaceDDL_usingQuery.jpg
(Size: 49.67KB, Downloaded 2736 times)

[Updated on: Mon, 09 September 2019 12:58]

Report message to a moderator

Re: Query to get the maxsize and unlimited of the TableSpace [message #677285 is a reply to message #677278]

Mon, 09 September 2019 15:18

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you have specified UNLIMITED this view will give the physical maximum size the file can be given the tablespace block size.

The following documentation page will give the database physical limits:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/physical-database-limits.html#GUID-939CB455-783E-458A-A2E8-8117 2B990FE9

Report message to a moderator

Re: Query to get the maxsize and unlimited of the TableSpace [message #677297 is a reply to message #677285]

Tue, 10 September 2019 14:17

wtolentino
Messages: 421
Registered: March 2005

Senior Member

thanks. which views/table and columns that i can use to get the info about "AUTOEXTEND ON NEXT 10M".

Report message to a moderator

Re: Query to get the maxsize and unlimited of the TableSpace [message #677299 is a reply to message #677297]

Tue, 10 September 2019 14:41

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The view you currently use:

SQL> desc DBA_DATA_FILES
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 FILE_NAME                                 VARCHAR2(513)
 FILE_ID                                   NUMBER
 TABLESPACE_NAME                           VARCHAR2(30)
 BYTES                                     NUMBER
 BLOCKS                                    NUMBER
 STATUS                                    VARCHAR2(9)
 RELATIVE_FNO                              NUMBER
 AUTOEXTENSIBLE                            VARCHAR2(3)
 MAXBYTES                                  NUMBER
 MAXBLOCKS                                 NUMBER
 INCREMENT_BY                              NUMBER
 USER_BYTES                                NUMBER
 USER_BLOCKS                               NUMBER
 ONLINE_STATUS                             VARCHAR2(7)

See AUTOEXTENSIBLE and INCREMENT_BY (you currently use in your query).
Have a look at the view definition in the documentation to know the unit of each column.

Report message to a moderator

Previous Topic:	ORA-00205: Standby database
Next Topic:	ORA-00851: SGA_MAX_SIZE 1073741824 cannot be set to more than MEMORY_TARGET 738197504

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 28 06:34:04 CST 2024