In case you missed this, here I am in video giving a 2 Minute tech tip on QB_NAME. There is a rumor I mention my favorite water hole in this, you'll have to watch it to see if I do or not.
https://www.youtube.com/watch?v=3t5NHtPKji0
Monday, July 31, 2017
Tuesday, July 25, 2017
SQLPLUS, login.sql and 12.2 in the cloud or not
Another little hiccup in 12.2 this time with SQLPlus. It appears that it will not run the login.sql in your current directory as
it always has in the past.
There seems to be a problem with SQLPlus and
SQLCL using the SQLPATH setting. SQLPlus
as far as I can tell, still uses SQLPATH as it always has for running a SQL
file like:
SQL>
@emp
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
---------------
-------------------- ------------------------- -------------------------
105 David Austin DAUSTIN
151 David Bernstein DBERNSTE
165 David Lee DLEE
SQL>
In SQLPlus, the GET command as far as I know has never used
SQLPATH to find files, it only looks in the current directory, and that seems
to be working like normal.
But SQLCL the GET command doesn’t appear to be working
correctly. You can get it to look in
what SQLPATH is set to as long as SQLPATH only has ONE directory in it. If SQLPATH has multiple entries (which mine
does) then it doesn’t use it at all.
This is a known issue
in SQLPlus and you can look it up under this document number in your
myoraclesupport account. Doc ID
2274608.1 By the way, the doc talks
about the glogin.sql file not
being run, that doesn’t appear to be the case for me. My fix was to add a call to my login.sql file in the glogin.sql file (in ORACLE_HOME/sqlplus/admin) and it worked
just fine. The doc indicates that if the glogin.sql
file is in some other directory pointed to by SQLPATH it doesn’t work, that
very well may be true.
I’ve logged an SR about the issue with SQLCL. I presume there
is some common code between the two hence they seem to be related.
Thursday, July 13, 2017
Going to 12.2 in the cloud or not
UPDATE: 25JULY2017 - It appears that an UPGRADE to 12.2 from 12.1 does not generate the issues described below. Only in a fresh install will these problems appear. The upgrade on windows used the oracle account to install the software which I believe to be the source of the issue with the ORA-27027 error. - Ric
We all have to upgrade at some point to the latest and greatest version of oracle. And this is really a good thing as the database does get better with each release. And every time we do there are always little (or big) gotchas along the way.
We all have to upgrade at some point to the latest and greatest version of oracle. And this is really a good thing as the database does get better with each release. And every time we do there are always little (or big) gotchas along the way.
In 12.2 I’ve found two things. First on Windows (yes I do install Oracle on
my Windows laptop), I’d recommend NOT
using the anonymous user for the owner of the software. This is recommended by oracle but my experience
is it generates a heap of errors in the alert.log and made my laptop
un-bootable. Yea that was fun.
The errors were like this, over and over again. There were associated with the monitoring
back ground processes (MMON and MMNL in particular). The database did work and log switches worked
fine as did DML activity:
ORA-27037: unable to obtain file status,
OSD-04011: GetFileInformationByHandle()
failure, unable to obtain file info
To solve this I reinstalled 12.1 and then upgraded to 12.2,
and used the oracle user that owned the 12.1 software to install the 12.2 software. The upgrade took quite a big longer to do
then just installing 12.2 alone but I ended up with a database that didn’t
cause a ton of errors and I can reboot my laptop too!! Win!
But wait there’s more!
Another issue I’ve found is that with a fresh install of
12.1 I can’t get STATISTICS_LEVEL to work properly. With a new 12.2 install once I set STATISTICS_LEVEL
to ALL
it seems to always stay that way. On a
Linux cloud instance I have 12.2 installed as a fresh install (which doesn’t have the
errors I saw in my Windows install) once I set STATISTICS_LEVEL to ALL
it never seems to change, even when it “looks” like it’s changed. This same thing did happen on my Windows
laptop when I did the fresh install, but doesn’t happen after the upgrade, very
odd to be sure.
As a quick example:
SQL>
SQL>select sql_trace, count(*) from v$session group by sql_trace;
SQL_TRAC COUNT(*)
-------- ---------------
DISABLED 42
SQL>
SQL>select count(*) from DBA_ENABLED_TRACES;
COUNT(*)
---------------
0
SQL>
SQL>show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL>
SQL>select * from dual;
D
-
X
SQL>select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual
Plan hash value: 272002086
---------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 1 | 1 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------
SQL>select sql_trace, count(*) from v$session group by sql_trace;
SQL_TRAC COUNT(*)
-------- ---------------
DISABLED 42
SQL>
SQL>select count(*) from DBA_ENABLED_TRACES;
COUNT(*)
---------------
0
SQL>
SQL>show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL>
SQL>select * from dual;
D
-
X
SQL>select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual
Plan hash value: 272002086
---------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 1 | 1 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------
The statistics/tracing level sure appears to be typical and
no trace appears to be turned on, yet it still shows all the stats you’d see if
it was on.
What you should get for this plan is something like this,
note the warning at the bottom:
SQL> select * from dual;
D
-
X
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual
Plan hash value: 272002086
----------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS FULL| DUAL | 1 |
----------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
D
-
X
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual
Plan hash value: 272002086
----------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS FULL| DUAL | 1 |
----------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
I have created an SR with Oracle Support on both these issues
and they have their top people working on them. Top people.
If anyone else can confirm or deny my findings I’d sure like
to hear from you. Have a great day!
Subscribe to:
Posts (Atom)