How to identify Duplicates in a table using SQL PLUS

How to identify Duplicates in a table using SQL PLUS 

SELECT COUNT(*), COLUMN_A, COLUMN_B
FROM TABLE_B
GROUP BY COLUMN_A, COLUMN_B
HAVING COUNT(*)>1;

ORA-01618

ORA-01618: redo thread 2 is not enabled - cannot mount

Example

SQL> startup

ORACLE instance started.
 
Total System Global Area 4731174912 bytes
Fixed Size                  2061264 bytes
Variable Size             838863920 bytes
Database Buffers         3875536896 bytes
Redo Buffers               14712832 bytes
ORA-01618: redo thread 2 is not enabled - cannot mount

 

Details:

The above error will be displayed during the startup of a node in a rac cluster 

in the event that a redo thread is not enabled. You need to enable the thread 

instance prior to adding a redo log thread which is created using the 

            ALTER DATABASE ADD LOGFILE THREAD [NUMBER]

The respective instance threads can be identified from the init parameter files

below e.g.:

RACNODE1.THREAD=1  # RACNODE1 instance uses Thread 1
RACNODE2.THREAD=2  # RACNODE2 instance uses Thread 2

Solution:

Enabled the Thread on the active node as follows:

 

SQL> alter database enable public thread 2;
 
SQL> select thread#, enabled from v$thread;
 
   THREAD# ENABLED
---------- --------
         1 PUBLIC
         2 PUBLIC

Restart the node which displays the ORA error

ORA-29707

ORA-29707: inconsistent value string for initialization parameter string with other instances

Example:

SQL> startup

ORA-29707: inconsistent value 2 for initialization parameter cluster_database_in

stances with other instances

SQL>

SQL>

Details:

In the event that you startup a node on a RAC cluster and you receive an ORA-29707 as above 

check "cluster_database" parameter on all nodes in the rac cluster.

SQL> show parameter cluster
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string

Note: There is no need to set cluster_database_instances

Solution:

Set cluster_database parameter to true on all nodes of the cluster

SQL> alter system set cluster_database=true;

Delete a Service in Windows using SC (Service Control)

Delete a Service in Windows using SC ( Service Control)

C:\Documents and Settings\user>sc query| FIND "TEST"

SERVICE_NAME: OracleServiceTEST
DISPLAY_NAME: OracleServiceTEST

C:\Documents and Settings\user>sc stop OracleServiceTEST

SERVICE_NAME: OracleServiceTEST
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 3  STOP_PENDING
                                (STOPPABLE,PAUSABLE,ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x1
        WAIT_HINT          : 0x15f90

C:\Documents and Settings\user>sc stop OracleServiceTEST

[SC] ControlService FAILED 1062:

The service has not been started.

C:\Documents and Settings\user>sc DELETE OracleServiceTEST

[SC] DeleteService SUCCESS

C:\Documents and Settings\user>sc query| FIND "TEST"


C:\Documents and Settings\user>



Ref:
DESCRIPTION:
        SC is a command line program used for communicating with the
        NT Service Controller and services.
USAGE:
        sc <server> [command] [service name] <option1> <option2>...

        The option <server> has the form "\\ServerName"
        Further help on commands can be obtained by typing: "sc [command]"
        Commands:
          query-----------Queries the status for a service, or
                          enumerates the status for types of services.
          queryex---------Queries the extended status for a service, or
                          enumerates the status for types of services.
          start-----------Starts a service.
          pause-----------Sends a PAUSE control request to a service.
          interrogate-----Sends an INTERROGATE control request to a service.
          continue--------Sends a CONTINUE control request to a service.
          stop------------Sends a STOP request to a service.
          config----------Changes the configuration of a service (persistant).
          description-----Changes the description of a service.
          failure---------Changes the actions taken by a service upon failure.
          sidtype---------Changes the service SID type of a service.
          qc--------------Queries the configuration information for a service.
          qdescription----Queries the description for a service.
          qfailure--------Queries the actions taken by a service upon failure.
          qsidtype--------Queries the service SID type of a service.
          delete----------Deletes a service (from the registry).
          create----------Creates a service. (adds it to the registry).
          control---------Sends a control to a service.
          sdshow----------Displays a service's security descriptor.
          sdset-----------Sets a service's security descriptor.
          showsid---------Displays the service SID string corresponding to an ar
bitrary name.
          GetDisplayName--Gets the DisplayName for a service.
          GetKeyName------Gets the ServiceKeyName for a service.
          EnumDepend------Enumerates Service Dependencies.

        The following commands don't require a service name:
        sc <server> <command> <option>
          boot------------(ok | bad) Indicates whether the last boot should
                          be saved as the last-known-good boot configuration
          Lock------------Locks the Service Database
          QueryLock-------Queries the LockStatus for the SCManager Database
EXAMPLE:
        sc start MyService

Would you like to see help for the QUERY and QUERYEX commands? [ y | n ]: y
QUERY and QUERYEX OPTIONS :
        If the query command is followed by a service name, the status
        for that service is returned.  Further options do not apply in
        this case.  If the query command is followed by nothing or one of
        the options listed below, the services are enumerated.
    type=    Type of services to enumerate (driver, service, all)
             (default = service)
    state=   State of services to enumerate (inactive, all)
             (default = active)
    bufsize= The size (in bytes) of the enumeration buffer
             (default = 4096)
    ri=      The resume index number at which to begin the enumeration
             (default = 0)
    group=   Service group to enumerate
             (default = all groups)
SYNTAX EXAMPLES
sc query                - Enumerates status for active services & drivers
sc query messenger      - Displays status for the messenger service
sc queryex messenger    - Displays extended status for the messenger service
sc query type= driver   - Enumerates only active drivers
sc query type= service  - Enumerates only Win32 services
sc query state= all     - Enumerates all services & drivers
sc query bufsize= 50    - Enumerates with a 50 byte buffer.
sc query ri= 14         - Enumerates with resume index = 14
sc queryex group= ""    - Enumerates active services not in a group
sc query type= service type= interact - Enumerates all interactive services
sc query type= driver group= NDIS     - Enumerates all NDIS drivers

SQL Server Table Space Usage

Table Space Usage

Calculates space usage for all tables in a particular Database , Support 2000 and 2005:

BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
, schemaname VARCHAR(500) collate database_default
);
DECLARE @temp_table TABLE (
tablename sysname
, row_count INT
, reserved VARCHAR(50) collate database_default
, data VARCHAR(50) collate database_default
, index_size VARCHAR(50) collate database_default
, unused VARCHAR(50) collate database_default
);

INSERT INTO @tab1
SELECT t1.name
, t2.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );

DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );

OPEN c1;
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');

-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
BEGIN
INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
END

FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;
SELECT t1.*
, t2.schemaname
FROM @temp_table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,tablename;
END try
BEGIN catch
SELECT -100 AS l1
, ERROR_NUMBER() AS tablename
, ERROR_SEVERITY() AS row_count
, ERROR_STATE() AS reserved
, ERROR_MESSAGE() AS data
, 1 AS index_size, 1 AS unused, 1 AS schemaname
END catch

SQL Server Scripts

SQL Server Scripts

Table Space Usage

AIX Commands

AIX Commands

oslevel

AIX OS Level

OS Level

To display AIX version

# oslevel

To display AIX version and patch level. 

# oslevel -r

To display list of known service packs on a system

# oslevel -q -s

To display man page on oslevel 

# man oslevel