Query to find basic redo log information

COL MEMBER FORMAT A50
SET LINES 200
SELECT A.GROUP#,A.MEMBER, B.BYTES/1024/1024 "MB", B.ARCHIVED,B.STATUS,B.SEQUENCE#
FROM V$LOGFILE A INNER JOIN  V$LOG B ON A.GROUP# = B.GROUP#
ORDER BY GROUP# ASC;

Result:

GROUP#	MEMBER	MB	ARCHIVED	STATUS	SEQUENCE#
1	/u04/oradata/proddb01/log1a.ora	1024	YES	INACTIVE	90510
1	/u04/oradata/proddb01/log1b.ora	1024	YES	INACTIVE	90510
2	/u04/oradata/proddb01/log2b.ora	1024	YES	INACTIVE	90511
2	/u04/oradata/proddb01/log2a.ora	1024	YES	INACTIVE	90511
3	/u04/oradata/proddb01/log3b.ora	1024	NO	CURRENT	90508
3	/u04/oradata/proddb01/log3a.ora	1024	NO	CURRENT	90508

How to add a comment on a column

Use this syntax:

COMMENT ON COLUMN SCOTT.EMP.DOB IS 'Date of Birth';

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