Tablespace

Creating Tablespaces in ASM

When ASM creates a datafile for a permanent tablespace (or a temporary file for a temporary tablespace), the datafile is set to auto-extensible with an unlimited maximum size and 100 MB default size. You can use the AUTOEXTEND clause to override this default extensibility and the SIZE clause to override the default size. ASM applies attributes to the datafile, as specified in the system default template for a datafile as shown in the table in “Managing Disk Group Templates”. You can also create and specify your own template. Files in a tablespace might be in both ASM files and non-ASM Read More

Creating Tablespaces in ASM

  When ASM creates a datafile for a permanent tablespace, the datafile is set to auto-extensible with an unlimited maximum size and 100 MB default size. You can use the AUTOEXTEND clause to override this default extensibility and the SIZE clause to override the default size. ASM applies attributes to the datafile, as specified in the system default template for a datafile as shown in the table in Managing disk group templates If there is a disk group named DATA, you can create a tablespace TBSLAVORO in that disk group with the following SQL statement: CREATE TABLESPACE TBSLAVORO DATAFILE ‘+DATA’; Read More

Moving Audit Trail Table

Reading the previous article posted on June 3 about enabling auditing in an Oracle Database, I’d like to add some other useful information. Oracle stores all the audit results in the SYS.AUD$ table, as stated in the previous article. But the bad news are that the table is by default in the SYSTEM tablespace: SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FORM DBA_TABLES WHERE TABLE_NAME = ‘AUD$’; OWNER  TABLE_NAME      TABLESPACE_NAME —— ————— —————————— SYS    AUD$            SYSTEM So the risk of having a full SYSTEM tablespace is very high if you plan to enable the audit feature, leading to possible unpredictable errors. The solution Read More

Overview of an interesting bug

 Often you may need to load data to the database under a different schema and different tablespace. With Oracle Data Pump all this is possible in a very simple way. Among the various options of the routine impdp you can find:   REMAP_SCHEMA REMAP_TABLESPACE   REMAP_SCHEMA allows us to load the exported objects under a different scheme; REMAP_TABLESPACE allows us to load the exported objects under a different tablespace. Obviously, options can be used separately.   For example if we have the table ADMIN.TAB1 on tablespace ADMIN_TBS (which is not the default tablespace of schema ADMIN):   SQL> select owner, Read More