ORA-01450 maximum key length (3118) exceeded

All of a sudden, deploying a new version of the apps, I got this error. The fix is simple. The error is related to the block size of the tablespace where you want to deploy your object. In my case it was about the creation of an index an the key was exceeding the size of the default block size of the default tablespace. All I had to do is to enable the 16K block size and to create a new tablespace using the 16k block size.

SQL> ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "TBS_DEFAULT";
ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "TBS_DEFAULT"
*
ERROR at line 1:
ORA-01450: maximum key length (3118) exceeded

SQL> show parameter db_16

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
SQL> alter system set db_16k_cache_size=500M;

System altered.

SQL> CREATE TABLESPACE "NEW_TBS" DATAFILE ...... SIZE 100M BLOCKSIZE 16K;

Tablespace created.

SQL> ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "NEW_TBS";

Table altered.

SQL>

 

 

12cR1 not being able to connect “as / sysdba” locally

This came as a surprise when I came back from vacation to discover that all of a sudden can’t do this on my Linux box sqlplus / as sysdba. I was getting ORA-01017. Something like this:

[oracle@dbtest dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 3 09:59:29 2016

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

ERROR:
 ORA-01017: invalid username/password; logon denied

Enter user-name: ^C
 [oracle@dbtest dbs]$

In my case, one of my colleagues, took the liberty to set TWO_TASK variable. After removing/unsetting the variable, all came to my desired situation.

As you may know, this TWO_TASK variable overwrites the ORACLE_SID & ORACLE_HOME variables and prohibits the local connections and is letting you to use the sqlnet path.

ORA-20000: ORU-10027: buffer overflow

Recently I got this error when I tried to execute a script with lots of dbms_output lines in it and the solution was to alter the script and to add the following:

 SET LINESIZE 999
 SET PAGES 500
 SET TIMING ON
 SET SERVEROUTPUT ON size UNLIMITED
 .........................
BEGIN
begin
 dbms_output.enable(NULL);
end;
.............................
END;
 /

How to create manualy the AWR reports

Not everyone has a GUI to extract the AWR reports from an Oracle database, hence here is what you need to do:

SCOTT@DB_TEST> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 3621956494 DB_TEST              1 DB_TEST

Elapsed: 00:00:00.03
Elapsed: 00:00:00.01

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text

Type Specified:                  text
Elapsed: 00:00:00.02


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 2121932494        1 DB_TEST       DB_TEST       NODE1

Using 3621956494 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
DB_TEST       DB_TEST         64869 05 Nov 2015 00:00      1
                              64870 05 Nov 2015 01:00      1
                              64871 05 Nov 2015 02:00      1
                              64872 05 Nov 2015 03:00      1
                              64873 05 Nov 2015 04:00      1
                              64874 05 Nov 2015 05:00      1
                              64875 05 Nov 2015 06:00      1
                              64876 05 Nov 2015 07:00      1
                              64877 05 Nov 2015 08:00      1
                              64878 05 Nov 2015 09:00      1
                              64879 05 Nov 2015 10:00      1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 64878
Begin Snapshot Id specified: 64878

Enter value for end_snap: 64879
End   Snapshot Id specified: 64879



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_64878_64879.txt.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Have fun!!