How to find the default tablespace type (smallfile vs bigfile)

SQL> select property_value 
from database_properties 
where property_name = 'DEFAULT_TBS_TYPE';

PROPERTY_VALUE
---------------
SMALLFILE

Change like this:

alter database set default bigfile tablespace;

The change will of course only take effect for future tablespaces, not existing ones.


Link to Oracle documentation

How to append text at beginning and at end of each line in vi (unix)

Go to top of line with :0

Append text to the end of each line, globally:

:%s/$/text/g

Add text to the beginning of each line, globally:

:%s/^/text/g

How to install and deinstall Oracle Spatial and Oracle Multimedia

The following advice is an excerpt from a Service Request with Oracle support services, on how to administer Spatial and Multimedia:

To remove Spatial:

 connect / as sysdba 
 drop user MDSYS cascade; 

To remove Multimedia:

 connect / as sysdba 
 @?/rdbms/admin/catcmprm.sql ORDIM 

You can reinstall both of these components later if needed by running the following scripts. Note, Multimedia must be installed first.

To Install Multimedia:

 connect / as sysdba 
 @?/ord/admin/ordinst.sql SYSAUX SYSAUX 
 @?/ord/im/admin/catim.sql 
 execute sys.validate_ordim; 

To install Spatial:

 connect / as sysdba 
 @?/md/admin/mdinst.sql 

How to check if spatial and multimedia is installed in the database

The following advice was given to me by Oracle Support services, to determine if either of these components are being used in the database:

 connect / as sysdba 

 set pagesize 10000 
 col owner format a12 
 col table_name format a35 
 col column_name format a25 
 col index_name format a25 

 -- Is Spatial being used? 

 select owner, index_name 
 from dba_indexes 
 where ityp_name = 'SPATIAL_INDEX'; 

 select owner, table_name, column_name 
 from dba_tab_columns 
 where data_type= 'SDO_GEOMETRY' 
 and owner != 'MDSYS'; 

If both of these queries return no rows, Spatial is not being used.

 -- Is Multimedia being used? 

 set serveroutput on; 
 @?/ord/im/admin/imremchk.sql 

If you're not using this component, you will get the message returned "Oracle Multimedia is not being used".

How to check if the database is in restricted mode

To check what kind of logins that are allowed in your instance, query the LOGINS field of the v$instance view:

select logins from v$instance;

Output here will be either ALLOWED or RESTRICTED

Join with v$database for more information:

select a.INSTANCE_NAME, a.ACTIVE_STATE,a.logins, b.open_mode 
from v$instance a inner join v$database b 
on UPPER(a.instance_name) = b.name;

Which will show the following output if your database is in restricted mode, and in this example, mounted:

INSTANCE_NAME    ACTIVE_ST LOGINS     OPEN_MODE
---------------- --------- ---------- --------------------
mydb01           NORMAL    RESTRICTED MOUNTED

and for normal mode:

INSTANCE_NAME    ACTIVE_ST LOGINS     OPEN_MODE
---------------- --------- ---------- --------------------
mydb01           NORMAL    ALLOWED    READ WRITE

ORA-01722 when running ausy1120.sql during preparation of new target database for EBS

If you are following note Doc ID 741818.1 "Export/import process for 12.0 or 12.1 using 11gR1 or 11gR2" and run into the follwing problem when running the ausy1120.sql script:

sqlplus system/*** @ausy1120.sql

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 2 14:19:31 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option


PL/SQL procedure successfully completed.

declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 5

the solution is to modify the script slightly and correct a typo.
Open the ausy1120.sql file in an editor and change

select
  to_number(substr(version,1,instr(version,'.')))
  into :dbver
  from v$instance
  where rownum=1;

to

select
  to_number(substr(version,1,instr(version,'.')-1))
  into :dbver
  from v$instance
  where rownum=1;

Then rerun the script. It should finish almost immediately and the output should be similar to:

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

--------------------------------------------------------------------------------
--- ausy1120 started at 02-NOV-2015 14:40:04 ---


'---AUSY1120COMPLETEDAT'||TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')||'----'
--------------------------------------------------------------------------------
--- ausy1120 completed at 02-NOV-2015 14:40:04 ----

Commit complete.

The same error seems to occur when running the aujv1120.sql and the aumsc1120.sql as well. The solution is the same for all three.

Syntax for setting hidden parameter in Oracle

Syntax example:

alter system set "_system_trig_enabled"=TRUE scope=both;