According to this Oracle document, the maximum number of database files per tablespace is "Operating system dependent; usually 1022".

I know there are queries for some of the other limits mentioned in that document (e.g. to determine the database block size: select value from v$parameter where name='db_block_size').

Is there a query to determine the maximum number of datafiles per tablespace? (I tried select name from v$parameter where value='1022' but that returned no rows.)

  • I guess that you can have lower limit on some exotic operating systems which support less than 1022 files on the filesystem. In all "normal" cases limit is 1022. But once again that's a guess. – Mindaugas Riauba Mar 5 '15 at 6:32
  • 1
    It is probably contained in some variable, but may not be accessible. MOS note 262384.1 may provide some more light on this. – Raj Mar 6 '15 at 20:23

db block size is a database parameter but maximum number of tablespace or datafile is a physical limit and you can not find them in v$parameter view. You can use documentation for that: https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits002.htm#i287915

| improve this answer | |
 select count(*) from dba_data_files where tablespace_name='TBSNAME';
| improve this answer | |
  • 2
    Does this give the maximum number of datafiles or the actual number of datafiles? – joanolo Jul 15 '17 at 12:10
  • This doesn't answer the question. – Colin 't Hart Jul 15 '17 at 13:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.