Space

How Big a Database Can Be

Posted on
Create Tablespace in a Database
Maximum Size Limit of a Database After knowing how big a data file can be and how big a tablespace can be in previous posts, we can continue to calculate how big a database can be. According to the physical database limits, we can have at most 65533 data files or 64000 tablespaces in a […]
Tablespace

How Big a Tablespace Can Be

Posted on
Add DataFile in a Tablespace
Maximum Size Limit of a Tablespace Basically, at least one data file should be specified in the tablespace creation, thereafter we can add more data files to the tablespace. After knowing how big a data file can be in the previous post, we can continue to calculate how big a tablespace can be. For a […]
DDL

How to Resolve ORA-02494: invalid or missing maximum file size in MAXSIZE clause

Posted on
Generated DDL from tools may not be executable sometimes. I got ORA-02494 when I execute DDL scripts that were generated from other database by TOAD, a very popular administration tool used by DBA. Irrational DDL SQL> CREATE TABLESPACE PERSON DATAFILE   2    ‘/u02/app/oracle/oradata/datastore/.ACFS/snaps/orcl/ORCL/datafile/user01.dbf’ SIZE 5G AUTOEXTEND ON NEXT 20M MAXSIZE 4G   3  LOGGING […]
Tablespace

How to Resolve ORA-23515: materialized views and/or their indices exist in the tablespace

Posted on
ORA-23515 I got this error when I was trying to drop a tablespace with all options. SQL> drop tablespace example including contents and datafiles cascade constraints; drop tablespace example including contents and datafiles cascade constraints * ERROR at line 1: ORA-23515: materialized views and/or their indices exist in the tablespace Let’s see the content of […]
Space

How to Choose the Most Needed to be Reorganized Tables

Posted on
We all know that table reorganization could improve the performance of DML on the tables. Your supervisor might ask you to do this action more proactively. Before deciding which tables should be reorganized, you have to know which table(s) is/are most needed to reorganize when next time available.In most cases, ranking the number of chain […]
Space

Must Dos after Moving a Table

Posted on
Moving a table is actually a reorganization process, The database will copy the original data to the new place. In which, the data will be stored optimally in terms of performance and space. But if you found the performance was degraded on this table after moving, it might be that your indexes have not been […]
UNDO

How to Resolve ORA-01555 UNDO_RETENTION

Posted on
ORA-01555 UNDO_RETENTION Let’s see the error ORA-01555 UNDO_RETENTION first: Description ORA-01555: snapshot too old: rollback segment number string with name “string” too small Cause rollback records needed by a reader for consistent read are overwritten by other writers Action If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments Before we […]
Initialization Parameter

When UNDO_RETENTION is Useless, When is Not

Posted on
At times, you may find out or feel that UNDO_RETENTION is useless at all. Raising the number of seconds cannot resolve ORA-01555. This is true under some specific conditions. Here I quote the requisites from the official document below: Fix-sized Undo Tablespace The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database […]
UNDO

How to Resolve SMON: about to recover undo segment xx SMON: mark undo segment xx as available

Posted on
Repeated messages were found in the alert log after instance startup:…SMON: about to recover undo segment 32SMON: mark undo segment 32 as availableSMON: about to recover undo segment 32SMON: mark undo segment 32 as available…It looked like that SMON was rolling back the database with undo segment. But somehow, it met problems when recovering undo segment […]