check invalid directories
To get the status of a directory, I wrote my own function, which uses DBMS_LOB.FILEEXISTS. CREATE FUNCTION status (DIRECTORY_NAME VARCHAR2) RETURN VARCHAR2 IS BEGIN IF (DBMS_LOB.FILEEXISTS( BFILENAME (DIRECTORY_NAME, ‘.’)) = 1) THEN RETURN ‘VALID’; ELSE RETURN ‘INVALID’; END IF; EXCEPTION WHEN OTHERS THEN RETURN SQLERRM; END; / SELECT directory_name NAME, directory_path PATH, status (directory_name) STATUS […]
check invalid database link for the DBA
followup of check invalid database link If you need to check db link in another schema, you need to create code that run with that schema. base on the example from yesterday, here is an extended version for the dba CREATE FUNCTION dba_status (owner VARCHAR2, db_link VARCHAR2) RETURN VARCHAR2 IS PRAGMA AUTONOMOUS_TRANSACTION; status VARCHAR2 (4000); […]
check invalid database link
If one database link is invalid, you cannot select through it SQL> select * from dual@z; select * from dual@z * ERROR at line 1: ORA-02019: connection description for remote database not found However, there is no STATUS command in your user_db_links table that you could use. For this purpose, write a function. create function […]
list targets
$p=new XML::Parser;$p->setHandlers(Start=>&b);$p->parsefile("-");sub b{($i,$e,%a)=@_;if($e eq"Target"){print$a{"NAME"}.":".$a{"TYPE"}}} — laurentsch (@laurentsch) September 19, 2017 As correctly pointed out by dhoogfr , the proper way to list targets on an agent is to use list target $ emctl config agent listtargets Oracle Enterprise Manager Cloud Control 13c Release 2 Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved. [srv01.example.com, host] […]
sid and pluggable
I wrote about SID there. (CONNECT_DATA=(SID=DB01)) is undocumented since Oracle 11gR1 and breaks things with multi-tenant / single-tenant. You create a container CDB01 and a pluggable DB01, you can connect with sqlplus “scott/tiger@ (description=(address= (host=srv01)(port=1521)(protocol=tcp)) (connect_data=(service_name=DB01.example.com)))” But one of your application has hardcoded SID=DB01 sqlplus “scott/tiger@ (description=(address= (host=srv01)(port=1521)(protocol=tcp)) (connect_data=(sid=DB01)))” ORA-12514: TNS:listener does not currently know […]
default listener port
Long time ago, Maxime Yuen registered 1521 for nCube License Manager. By googling I found : Ellison cleans house at nCube, and since them 1521 has been used as a default port for Oracle. Still, you’ll see nCube in IANA.ORG service names port numbers and in /etc/services the nCube name. I don’t know which one […]
lock sys
In the old days, locking sys had not much effect. SQL> alter user sys identified by *** account lock; User altered. SQL> select account_status from dba_users where username=’SYS’; ACCOUNT_STATUS ——————————– LOCKED SQL> conn / as sysdba Connected. SQL> conn sys/** as sysdba Connected. SQL> conn sys/***@db01 as sysdba Connected. Well, in the very-old days, Oracle7, […]
Single-Tenant over bequeath connections
If you follow Oracle recommendation to use SingleTenant CDB instead of Non-CDB, then a lot of things will break. I won’t go into details, but basically, in this new architecture, you have one core container, called CDB$ROOT, which is the default container where you connect to if you connect locally sqlplus / as sysdba SQL> […]
Legacy users get ORA-01017 in 12.2
10g value string disappeared
remote transaction timeout
If you access one table via database link and the row is locked, you may get a timeout SQL> update emp@l set sal=sal+1 where ename=’SCOTT’; 1 row updated. SQL> update emp@l set sal=sal+2 where ename=’SCOTT’; update emp@l set sal=sal+2 * ERROR at line 1: ORA-02049: timeout: distributed transaction waiting for lock ORA-02063: preceding line from […]
ANNOUNCEMENT: 12cR2 is available on AIX and HPUX and #sqldev 4.2
download it from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html sqldeveloper 4.2 is there : http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html
Monitor audit_file_dest !
Until 11.2, audit_file_dest used to remain small with default settings and reasonably sized and active database. Suddenly, in 12c, you will sooned or later get ORA-09925: Unable to create audit trail file. At that point, no more connection is possible to the database, it is a complete loss of service. Why suddenly in 12c ? […]
connect / as sysoper generates ORA-1017
Today I had the issue that I could not login as sysoper on one database, despite being in the dba and oper groups. The problem is that the osoper group was -probably- not selected during installation e.g. in 12c /AIX it may be called differently on your system $ sqlplus -L -s / as sysoper […]
old jdbc driver generates ORA-28040
I read on note 401934.1 that Oracle 10gR2 jdbc clients are still supported with Oracle 12c. I have an application using an oracle10gr2 jdbc, and connection to 12c generates ORA-28040. Connection to 11gR2 works like a charm. O12.java import java.util.Properties; import java.sql.*; public class O12 { public static void main(String argv[]) throws SQLException { Properties […]
Restricted sqlplus and sql_script
Yesterday I wrote about execute sql script from plsql. Connor then posted a 12c new feature that I overviewed https://twitter.com/connor_mc_d/status/811563990346186752 If you are still considering the 11g approach with sqlplus, you should read about restricted sqlplus If you run sqlplus -L -s -R 3 scott/tiger@db01 lot’s of OS-command are disabled SQL> get /etc/passwd SP2-0738: Restricted […]
execute sql script from pl/sql
When you want to run a SQL script, you rather use sqlplus. If you want to run it within a PL/SQL stored procedure, you are screwed. You could redo the logic of sqlplus, this is what any decent IDE and code runnner does, and it’s a pile of work and you will get lot’s of […]
ANNOUNCEMENT: 12cR2 documentation is available
The doc docs.oracle.com/database/122 is available. Check new features and stay tuned. Okay, just 12 for today : HIGH compressed index, case insensitive database with bound collation, partitioned external tables, AL32UTF8 default for new database, listagg overflow, VALIDATE_CONVERSION, approx_percentile, json_exists, flashback pluggable database, the SHARING clause, RAC streched clusters site definition and INACTIVE_ACCOUNT_TIME in profile. Some […]
To shrink or to move
If you delete most of the rows in one table, you probably want to regain that space, to minimize IO and fragmentation. If you delete most of the rows in most of the tables, you probably also want to resize the datafile to reduce storage and backup space. In this case, you could move your […]
OTN Appreciation Day : Partition your table online !
#ThanksOTN @oraclebase No, I am not talking about DBMS_REDEFINITION, where you get a kind of online feeling. No, I don’t want to rename my table, rename my foreign keys, my primary key, my not-null-constraints, recreate my referential integrity, recompile my triggers. I just want to partition a non-partitioned table. ALTER TABLE EMP MODIFY PARTITION BY […]
Enterprise Manager 13c R2 is out
#EM13R2 is out https://t.co/dIwIn0jxp9 — laurentsch (@laurentsch) October 6, 2016 Download it here : oracle.com/technetwork/oem/grid-control/downloads Documentation : docs.oracle.com/cd/E73210_01
What is the instance name?
If your Oracle SID doesn’t match your instance name in init.ora, this is quite confusing. Check my previous post, what is sid in oracle In the instance_name column of the view v$instance, as well as in USERENV context, it matches the ORACLE_SID of the underlying operating system. SQL> var ORACLE_SID varchar2(9) SQL> set autoprint on […]
What is SID in Oracle ?
In the doc you’ll find, it’s the Oracle system identifier. Okay, let’s imagine the following Environment: ORACLE_SID=ORA001 init.ora: DB_NAME=DB001 DB_UNIQUE_NAME=UNI001 INSTANCE_NAME=INS001 SERVICE_NAMES=SVC001,SVC002 DB_DOMAIN=EXAMPLE.COM GLOBAL_NAMES=false database: SQL> select * from GLOBAL_NAME; GLO001.example.com listener.ora: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_NAME=GLO001.EXAMPLE.COM) (SID_NAME=ORA001) ) ) What is my SID? Actually there is more than one correct answer. In […]
duplicate to a future date
If you work with large databases, you often wait way to long for the clones. Typically you want to duplicate a 10TB database to production timestamp 9am, and you start at 9am and then you wait for hours. Is it possible to start the clone, let’s say, at midnight, and set until time 9am? No! […]
column width change in 12c
In 11g I used to have 30 characters width in my dictionary SQL> select table_name, column_name from user_tab_columns; TABLE_NAME COLUMN_NAME —————————— —————————— BONUS COMM BONUS SAL BONUS JOB BONUS ENAME DEPT LOC DEPT DNAME DEPT DEPTNO EMP DEPTNO EMP COMM EMP SAL EMP HIREDATE TABLE_NAME COLUMN_NAME —————————— —————————— EMP MGR EMP JOB EMP ENAME EMP […]
Drop table cascade and reimport
Happy new year 🙂 Today I had to import a subset of a database and the challenge was to restore a parent table without restoring its children. It took me some minutes to write the code, but it would have taken days to restore the whole database. CREATE TABLE t1( c1 NUMBER CONSTRAINT t1_pk PRIMARY […]
Licensing Cloud Control
I just read the Enterprise Manager Licensing Information User Manual today. They are a lot of packs there, and you may not even know that autodiscovering targets is part of the lifecycle management pack or that blackouts are part of the diagnostic pack. Have a look
powershell odbc sybase
Oracle goes there ==> …/oracle-odbc-hello-world-with-powershell.html To test Sybase ODBC driver with Powershell, it’s not much different configure the DsN with odbcconf or %SYSTEMROOT%\SysWOW64\odbcconf for the 32bits drivers under Windows 64. cmd /c “%SYSTEMROOT%\SysWOW64\odbcconf.exe /a {configdsn “”Adaptive Server Enterprise”” “”DSN=helloworld|database=mydb|port=25000|server=srv01″”}” The name of the driver is important. It is probably also called “Adaptive Server Enterprise” on […]
generate safe passwords
This is probably ambitious and I’ll start with a disclaimer, there is no such thing. But ok, we know that system/manager isn’t Apart from system/manager, there are hundreds of weak passwords that are commonly used and easy to guess. On your database server, after a few tries, the account is lock. And maybe the attacker […]
Generate 11g password hash
An easy way to generate a value string from the ssl is to use openssl Let’s take a random salt of ABCDEFGHIJ. The length of 10 is important. The hexadecimal representation is -41-42-43-44-45-46-47-48-49-4A- $ echo “SafePassw0rDABCDEFGHIJ\c” | openssl dgst -sha1 (stdin)= 47cc4102144d6e479ef3d776ccd9e0d0158842bb With this hash, I can construct my value SQL> create user testuser identified […]
delete all data
How do you delete all data? The simplistic approach would be to truncate all tables SQL> select table_name from user_tables; TABLE_NAME ———- T1 SQL> truncate table t1; Table truncated. You cannot truncate if you have referential integrity constraints. SQL> truncate table t2; ORA-02266: unique/primary keys in table referenced by enabled foreign keys Ok, let’s disable […]
tracefile tim to readable date format
In trace file, the time is recorded, this could be used to measure time between two timestamps But how do you convert 31796862227375 to a human format? This is how I proceeded : SQL> oradebug SETMYPID Statement processed. SQL> oradebug tracefile_name /u01/log/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_32440740.trc SQL> alter session set nls_date_format=’YYYY-MM-DD_HH24:MI:SS’ sql_trace=true; Session altered. SQL> select ‘&_DATE’,’Start’ from dual; […]