SQLcl is the new command line tool from Oracle, more specifically from the SQL Developer team. It is currently an Early Adopter (EA) release and you can download it from: http://www.oracle.com/technet
Source: Installing SQLcl
Advertisements
Category: Sqlplus
List all running jobs
SET PAUSE ON SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300 SET VERIFY OFF SELECT a.job "Job", a.sid, a.failures "Failures", Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date", Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date" FROM dba_jobs_running a /
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!!
Login as sysdba for ORA-00020: maximum number of processes 0 exceeded
sqlplus -prelim "/as sysdba"
