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)
Query to get the maxsize and unlimited of the TableSpace [message #677278] Mon, 09 September 2019 12:57 Go to next message
wtolentino
Messages: 284
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;

/foru/forum/fa/14162/0/

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


[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 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
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

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 Go to previous messageGo to next message
wtolentino
Messages: 284
Registered: March 2005
Senior Member
thanks. which views/table and columns that i can use to get the info about "AUTOEXTEND ON NEXT 10M".
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 Go to previous message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
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.


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:
  


Current Time: Fri Dec 13 06:59:19 CST 2019