How to extract all datafile names without path using substr and instr functions

select trim(
            substr(file_name,
                (instr(file_name,'/', -1, 1) +1)
                )
               )  "file name"
from  dba_data_files;

Result:

file name
system01.dbf
sysaux01.dbf
undotbs01.dbf
users01.dbf
appl_data.dbf

More on how to display the current container in a multitenant database

My listener is listening for connections to a service called "myservice1", which runs out of the pluggable database "pdb1":

lsnrctl status
Service "pdb1" has 1 instance(s). <-- the default service for the pluggable database
  Instance "cdb", status READY, has 1 handler(s) for this service...

Service "myservice1" has 1 instance(s). <-- service defined by the DBA
  Instance "cdb", status READY, has 1 handler(s) for this service...

In the code snippet below, I am executing a script called disp_session.sql which will

connecting directly to a service served by the listener

switching schema within my session

The following will display the username, current schema, and the service my connction is connecting to:

alter session set current_schema=scott;
set lines 200
col service format a20
col container format a20
col username format a20
col "container ID" format a20
col schemaname format a20
select sys_context ('userenv','SERVICE_NAME')   service,
       sys_context ('userenv','CON_NAME')       container,
       sys_context ('userenv','CON_ID')         "container ID",
       sys_context ('userenv','CURRENT_USER')   username,
       sys_context ('userenv','CURRENT_SCHEMA') schemaname
from dual;

oracle@server1.oric.no:[cdb]# sqlplus system@myservice1 @disp_session.sql

Session altered.

SERVICE              CONTAINER            container ID         USERNAME             SCHEMANAME
-------------------- -------------------- -------------------- -------------------- --------------------
myservice1           pdb1                 4                    SYSTEM               SCOTT

Useful information when debugging client connections ;-)

How to exchange a line in a text file with a different one, when using double quotation marks in the text itself?

The following sed command will exchange the string

initParams=

with

initParams=undo_tablespace=UNDOTBS1,sga_target=9GB,db_block_size=32768BYTES,control_files=\"\/u02\/oradata\/{DB_UNIQUE_NAME}\/control01.ctl\"\,\"\/recovery\/fra\/{DB_UNIQUE_NAME}\/control01.ctl\"),db_recovery_file_dest=\/recovery\/fra\/{DB_UNIQUE_NAME}

in the file "myresponsefile.rsp":

sed -i "s/initParams=/initParams=undo_tablespace=UNDOTBS1,sga_target=9GB,db_block_size=32768BYTES,control_files=\"\/u02\/oradata\/{DB_UNIQUE_NAME}\/control01.ctl\"\,\"\/recovery\/fra\/{DB_UNIQUE_NAME}\/control01.ctl\"),db_recovery_file_dest=\/u04\/recovery\/{DB_UNIQUE_NAME} /" myreponse_file.rsp

Note that all double quotes (") and forward (/) slashes must be masked by the backslash charcater \ to be interpreted correctly.

Cause and solution to ORA-00058: DB_BLOCK_SIZE must be 8192 to mount this database (not 16384)

You attempt to execute dbca silently, using a response file, as shown below:

dbca -createDatabase -responsefile mydb01.rsp -silent

But it errors out with the error:

[WARNING] ORA-00058: DB_BLOCK_SIZE must be 8192 to mount this database (not 16384)

and the instance is left running, with an unmounted database.

Cause:

This means you are using a pre-defined template for dbca that includes datafiles.

When you do this, you cannot change the db_block_size. It is set to 8K, and carved in stone.

Solution:

Create your own, customized template and refer to that instead.

Remember that templates ending with the extension .dbc contains database files, so they will be significantly faster to use, than custom made templates ending with the extension .dbt - the latter will have to go through the "CREATE DATABASE" statements from scratch.

Templates that do not contain datafiles have the extension .dbt

What you can do in order to have your cake and eat it, too, is to first create a database with a non-confirming db_block_size, then create a dbca template based on this new database, including its datafiles.

I will see if I have time to write a blog post about how to do extactly this.

Simple PL/SQL script to alter parameter in database

At my current workplace, we use emcli for mass-updates of database parameters in groups of databases.

Below is a simple PL/SQL script that can be used to change parameter if needed. If the parameter is already set, nothing happens.

In my case, it was the parameter "recyclebin" that was needed to be switched from OFF to ON for some databases, but it could be any parameter.

Surely there are many ways to solve such a problem, this was how I solved it with very little effort :-)

set serveroutput on
set feedback off
set echo off
set verify off

DECLARE

 v_db_name v$database.name%%TYPE;
 v_rb v$system_parameter.name%%TYPE;


BEGIN
 execute immediate 'alter session set nls_language=''american''';
 select name
 into v_db_name
 from v$database;

 select value
 into v_rb
 from v$system_parameter
 where name = 'recyclebin';
 
--dbms_output.put_line('v_db_name: ' || v_db_name);
--dbms_output.put_line('v_rb:      ' || v_rb);

CASE
   WHEN v_rb = 'ON' THEN
    dbms_output.put_line('Database ' || v_db_name || ': recyclebin already enabled.');
   ELSE
    dbms_output.put_line('Database ' || v_db_name || ': recyclebin is currently disabled. Turning it on now...');
    execute immediate 'alter system set recyclebin=ON scope=spfile';
     dbms_output.put_line('Database ' || v_db_name || ' now has recyclebin=ON in spfile. Database must be bounced in in order to enable the setting');
   END CASE;
END;
/
exit

When we execute the script through emcli, the syntax comes to:

emcli execute_sql -sql="FILE" -iemcli execute_sql -sql="FILE" -input_file="FILE:/scripts/chk_and_alter_param.sql" -targets="TEST_DBS:composite"

How to trace the dbca tool

These notes are taken directly from MOS, and jotted down here so I have it readily available for the future.

To trace the DBCA, we need to perform the following.

1. Do a copy of the original dbca file in $ORACLE_HOME/bin. For example:

cp $ORACLE_HOME/bin/dbca $ORACLE_HOME/bin/dbca.ori

2. vi the dbca file in the $ORACLE_HOME/bin directory.

3. At the end of the file, look for the following line:

# Run DBCA
$JRE_DIR/bin/jre -DORACLE_HOME=$OH -DJDBC_PROTOCOL=thin -mx64m -classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS

4. Add the following just before the -classpath in the '$JRE_DIR' line:

-DTRACING.ENABLED=true -DTRACING.LEVEL=2

5. At the end of the dbca file, the string should now look like this:

# Run DBCA
$JRE_DIR/bin/jre -DORACLE_HOME=$OH -DJDBC_PROTOCOL=thin -mx64m -DTRACING.ENABLED=true -DTRACING.LEVEL=2 -classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS

6. To trace, run:

[host]/u01/home/usupport> dbca > dbca.out >

The output will be written to the dbca.out file.

Script to check the services in a multitenant container databaser

A simple script to check services running out of a specific PDB, or for all PDBs if the root container is selected:

column pdb_name format a20
column status format a20
prompt
prompt List of PDBs available on this server:
prompt
select p.pdb_name
from dba_pdbs p join v$pdbs v
on (p.pdb_id = v.con_id)
where open_mode='READ WRITE'
/

accept pdb_name DEFAULT 'CDB$ROOT' prompt 'Select a PDB (default=root container): '
alter session set container=&&pdb_name;
col con_name format a40
set lines 200
select 'You are connected to: ' || sys_context('USERENV','CON_NAME') "con_name"
from dual;

col name format a20
col global format a20
col pdb format a20
col con_name format a20
col network_name format a20
col creation_Date format a20
prompt V$SERVICES
select name,global,pdb,network_name from v$services
/

prompt V$ACTIVE_SERVICES
select name,global,con_name,network_name from v$active_services
/

prompt DBA_SERVICES
select service_id,name,network_name,creation_date,pdb
from dba_services
/

prompt CDB_SERVICES
select service_id,name,network_name,creation_date,pdb
from cdb_services
/
exit