Create Database Link using EZConnect Syntax

EZConnect syntax eliminates the need for service name lookup in tnsnames.ora files when connecting to an Oracle Database across a TCP/IP network. In a previous post we have already demonstrated how to use this syntax to establish connection in SQL*Plus.

Now, we are going even further. You can use the same syntax for creating Database Links (assuming that you have a privilege to create database links in general).

CREATE DATABASE LINK dbl_test
CONNECT TO scott
IDENTIFIED BY tiger
USING ‘scott/tiger@192.168.1.180:1521/ORCL’;

Database link created.

See http://www.orafaq.com/wiki/EZCONNECT for more information on the syntax and prerequisites.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to check and change Oracle ArchiveLog Mode

Level: Beginner (DBA)

Question: How to check database ArchiveLog mode?

Answer:

To check the database archive log mode you need to
Step 1: Connect as SYSDBA

ex: connect sys/sysdbapass@server as sysdba

Step 2: Execute one of the following commands:
a) ARCHIVE LOG LIST

--In SQL*PLus
SQL> ARCHIVE LOG LIST
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Current log sequence           36300

b) Select from v$database

SQL> SELECT log_mode
  2  FROM sys.v$database
  3  /

LOG_MODE
------------
NOARCHIVELOG

Question: How to enable ArchiveLog mode?

You can specify the initial archiving mode in the CREATE DATABASE statement. Normally, there is no need to use this option as you can always change the mode later.

Answer:

Step 1: Connect as sysdba user

Step 2: Assuming that you are using spfile for parameter initialization, we need to configure the archive log destination. We will use a simple single destination option. All available options are described in the Oracle documentation.

SQL> ALTER SYSTEM SET log_archive_dest='F:\OraArchivedLogs' SCOPE=spfile;

System altered.

REM Note that this change will only work if db_recovery_file_dest is set to a blank, so you can execute another command just in case:
SQL> ALTER SYSTEM SET db_recovery_file_dest='' SCOPE=spfile;

System altered.

Step 3: Shutdown the database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 4: Backup the database (Cold backup)
Step 5: Start a new instance and mount the database.

SQL> startup mount
ORACLE instance started.

Total System Global Area 5227814912 bytes
Fixed Size                  2264328 bytes
Variable Size            3321889528 bytes
Database Buffers         1895825408 bytes
Redo Buffers                7835648 bytes
Database mounted.

Step 6: Change the database archiving mode

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

Step 7: Open the database.

SQL> ALTER DATABASE OPEN;

Database altered.

Step 8: Shut down the database (see Step 3 above)

Step 9: Backup the database (see Step 4 above)

Step 10: Open the database:

SQL> STARTUP
ORACLE instance started.

Total System Global Area 5227814912 bytes
Fixed Size                  2264328 bytes
Variable Size            3321889528 bytes
Database Buffers         1895825408 bytes
Redo Buffers                7835648 bytes
Database mounted.
Database opened.

SQL> ARCHIVE LOG LIST
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Next log sequence to archive   36300
Current log sequence           36300

Question: How to disable ArchiveLog mode?

Answer:

Step 1: Connect as sysdba user
Step 2: Shutdown the database
Step 3: Backup the database (Cold backup)
Step 4: Start a new instance and mount the database.
Step 5: Change the archivelog mode:

SQL> ALTER DATABASE NOARCHIVELOG;

Database altered.

Step 6: Open the database

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ARCHIVE LOG LIST
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Current log sequence           36300

ORA-39087: directory name DATA_PUMP_DIR is invalid

Oracle 12c introduced a new concept of a pluggable database. It comes with many interesting features as well as some nuances that we need to know to perform such tasks as data pump export and import.

The main surprise comes with an undocumented fact that Oracle’s default Data Pump directory object, DATA_PUMP_DIR, cannot be used for data pump export and import.

When you try to attempt running expdp or impdp utilities using DIRECTORY=DATA_PUMP_DIR, you should get the following error message:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid

The solution is quite straightforward: do not use DATA_PUMP_DIR directory object for pluggable db.

You can create a new directory object (as a SYS user or as a user with CREATE ANY DIRECTORY privilege granted), grant READ and WRITE privileges on that DIRECTORY, and perform data pump export/import tasks.

Replace clause for changing one’s password.

Starting with Oracle 11g, if you get the following error when changing your own password, it means that you have to include REPLACE clause in your ALTER USER command:

ALTER USER testuser IDENTIFIED BY testpassword;

Oracle: “ORA-28221: REPLACE not specified” 

Syntax with REPLACE clause:

ALTER USER testuser IDENTIFIED BY testpassword REPLACE oldpassword;

User altered.

If your password contains special characters, you may need to put it in quotations:

ALTER USER testuser IDENTIFIED BY "testp@ssw@rd" REPLACE oldpassword;

User altered.

As per Oracle documentation,

You can omit the REPLACE clause if you are setting your own password or you have the ALTER USER system privilege and you are changing another user’s password. However, unless you have the ALTER USER system privilege, you must always specify the REPLACE clause if a password complexity verification function has been enabled, either by running the UTLPWDMG.SQL script or by specifying such a function in the PASSWORD_VERIFY_FUNCTION parameter of a profile that has been assigned to the user.

Source: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4003.htm#SQLRF01103

How to DELETE all records from all tables in a schema?

This is rather an exercise than a practical task. Anyway, the approach presented below might be helpful in some practical situations.

Problem Level: Beginner/Intermediate

Step 1: Disable all foreign key constraints.

DECLARE
  CURSOR c IS
  SELECT table_name, constraint_name
  FROM user_constraints
  WHERE constraint_type='R'
   AND status='ENABLED';
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'ALTER TABLE ' || v.table_name || ' DISABLE CONSTRAINT ' || v.constraint_name;
  END LOOP;
END;
/

Step 2: DELETE from all tables

DECLARE
  CURSOR c IS
  SELECT table_name
  FROM user_tables;
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'DELETE FROM ' || v.table_name;
  END LOOP;
END;
/

Step 3: COMMIT or ROLLBACK

ROLLBACK; --you can commit here instead if needed

STEP 4: Enable all FOREIGN KEY Constraints

DECLARE
  CURSOR c IS
  SELECT table_name, constraint_name
  FROM user_constraints
  WHERE constraint_type='R'
   AND status='DISABLED';
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'ALTER TABLE ' || v.table_name || ' ENABLE CONSTRAINT ' || v.constraint_name;
  END LOOP;
END;
/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to find all packaged procedures that have argument(s) of a specific data type?

This task is quite trivial if you know the right data dictionary to use.
Let’s say we need to see all procedures that has DATE arguments:

SELECT package_name, object_name AS procedure_name, argument_name, data_type
FROM user_arguments a
WHERE data_type='DATE'
  AND package_name IS NOT NULL
ORDER BY 1,2,3

How to create a report showing the number of records in each table of a given schema?

How to create a report showing the number of records in each table of a given schema?
Well, if you have just updated statistics for the entire schema, you can simply query a data dictionary table. We will not consider such case. Let’s assume that statistics is inaccurate, so we need a SQL or a PL/SQL solution.

1) PL/SQL solution
There are many approaches to procedural solution, for ex:
1. Loop over the cursor based on “SELECT table_name FROM user_tables”
2. Count number of rows using “EXECUTE IMMEDIATE” statement.
3. Use dbms_output package to produce the output or populate a collection and select from it to return a cursor to the caller.

2) SQL*Plus script generation
You can write a SQL script that will generate another script that counts number of rows and combines them all together with UNION ALL statement.

Options 1 and 2 have been around for years and are not of any significant interest anymore as Oracle now supports XML with many interesting applications – see the next option for details.

3) Update schema statistics and query user_tables view

SELECT table_name, num_rows 
FROM user_tables

This could be a time consuming process depending on the size of existing tables and number of indexes.

4) Use dbms_xmlgen.getxmltype
dbms_xmlgen package allows to dynamically create and execute numerous SELECT statements and parse the result XML to extract necessary information. Here is the most elegant solution to the problem:

col "Records" for a10

SELECT table_name,                  
       dbms_xmlgen.getxmltype('SELECT COUNT(1) cnt from '|| table_name).extract('/ROWSET/ROW/CNT/text()').getstringval() "Records"
FROM user_tables;

TABLE_NAME                     Records
------------------------------ ---------
DEPT                           4
EMP                            14
BONUS                          0
SALGRADE                       5

Special Note: Sometimes, if your schema has huge tables, this approach may fail if the database does not have enough memory resources available or allocated to process the query. In this case, we may suggest using one of the approaches mentioned above (1-3).

Suggested further reading:

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

A tricky Oracle DDL question from a real job interview

Recently I was asked the following question during the phone interview with one of the large companies:

Consider the following table:
[CREATE] TABLE table1
(
column1 NUMBER PRIMARY KEY,
column2 NUMBER,
column3 VARCHAR2(50),
column4 NUMBER
)

Change the data type of column3 from VARCHAR2(50) to CLOB while keeping the order of columns intact.

The anticipated answer was:

-- Step 1: Create a backup table with PK and last 2 columns
CREATE TABLE table1_bak AS
SELECT column1, column3, column4
FROM table1;

--Step 2: Drop last 2 columns
ALTER TABLE table1 DROP (column3, column4);

--Step 3: Add last 2 columns back:
ALTER TABLE table1 ADD(column3 CLOB, column4 NUMBER);

--Step 4: Restore the data
MERGE INTO table1 a
USING table1_bak b
ON (a.column1=b.column1)
WHEN MATCHED THEN
    UPDATE SET a.column3=b.column3,
               a.column4=b.column4;

--Step 5: Drop backup table
DROP TABLE table1_bak;

This answer was provided. In the spirit of finding workarounds I added another solution:

--Step 1: Add temp column
ALTER TABLE table1 ADD (column5 VARCHAR2(50));

--Step 2: Update column3 with NULLs
UPDATE table1
SET column3=NULL;

--Step 3: Change column3 data type to LONG first and CLOB after that:
ALTER TABLE table1 MODIFY (column3 LONG);
ALTER TABLE table1 MODIFY (column3 CLOB);

--Note: trying to change empty column VARCHAR2 to CLOB straight throws the following error:

ALTER TABLE table1 MODIFY (column3 CLOB)
                           *
ERROR at line 1:
ORA-22858: invalid alteration of datatype

--Step 4: Copy the content from column5 to column3:
UPDATE table1
SET column3=column5;

--Step 5: Drop the temp column:
ALTER TABLE table1 DROP COLUMN column5;

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to fix “ORA-28002: the password will expire within X days” errors, by Zahar Hilkevich

In Oracle 11g (and probably all other subsequent releases) the DEFAULT profile comes with PASSWORD_LIFE_TIME attribute equal to 180 days. So in about 6 month users will start getting ORA-28002 errors if they have not changed their passwords since their accounts were created.

If it is critical not to expire passwords automatically (which may or may not be a good idea), a DBA should alter the default profile as follows:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME UNLIMITED
/

This change will not apply immediately to those users who already started getting ORA-28002 errors. All they need to do is to change their password even to the same value.
At the same time, a DBA can help the users and “alter” their passwords even without knowing them. This becomes really helpful when the number of such database users is high.

When I first faced this issue I came up with the following script that does the trick. The script should be executed by a sysdba user.

DECLARE
  CURSOR c IS
   SELECT u.name, u.spare4
   FROM user$ u JOIN dba_users d ON u.name=d.username
   WHERE d.account_status='EXPIRED(GRACE)';
BEGIN
   FOR v IN c LOOP
	EXECUTE IMMEDIATE 'ALTER USER ' || v.name || ' IDENTIFIED BY VALUES ''' || v.spare4 || '''';
   END LOOP;
END;
/

Here you go!

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.