How to drop a redo logfile group and redo logfile group members - syntax

alter database drop logfile group 4;

The drop statement will be confirmed by an entry in the database's alert log.

Don't forget to physically remove the file from disk afterwards.

You can also drop individual Group members:

alter database drop logfile member '/u03/oradata/proddb01/red04a.log';

Pay attention to the restrictions when dropping redo log groups and redo log members!

A good Query for redo log file information can be found here

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".