Archive for the ‘SQL*Plus’ Category
If you’re not using Toad DBA Suite, it’s sometimes hard to find solutions. Somebody wanted to know how to find indexes that aren’t indirect. Indirect indexes are those created for a primary key because a primary key column or set of columns are both not null and uniquely constrained. Likewise, you create a unique index when you can create a unique constraint. You can’t drop a unique index for a primary key without dropping the primary key or unique constraint that indirectly created it.
The following query returns indexes with one or more columns that are created by a
CREATE INDEX statement on a target table. It excludes unique indexes created by a primary key constraint, and it returns the relative position of columns in an index:
COLUMN sequence_name FORMAT A22 HEADING "Sequence Name" COLUMN column_position FORMAT 999 HEADING "Column|Position" COLUMN column_name FORMAT A22 HEADING "Column|Name" SELECT uin.index_name , uic.column_position , uic.column_name FROM user_indexes uin INNER JOIN user_ind_columns uic ON uin.index_name = uic.index_name AND uin.table_name = uic.table_name WHERE uin.table_name = UPPER('&&table_name') AND NOT uin.index_name IN (SELECT constraint_name FROM user_constraints WHERE table_name = UPPER('&&table_name')) ORDER BY uin.index_name , uic.column_position;
It can be rewritten into a function, which can then drop indexes based on a table name:
CREATE OR REPLACE FUNCTION drop_indexes_on ( pv_table_name VARCHAR2 ) RETURN NUMBER IS /* A return value. */ lv_return NUMBER := 0; /* A query to return only directly created indexes. */ CURSOR find_indexes_on ( cv_table_name VARCHAR2 ) IS SELECT DISTINCT ui.index_name FROM user_indexes ui INNER JOIN user_ind_columns uic ON ui.index_name = uic.index_name AND ui.table_name = uic.table_name WHERE ui.table_name = UPPER(cv_table_name) AND NOT ui.index_name IN (SELECT constraint_name FROM user_constraints WHERE table_name = UPPER(cv_table_name)); /* Declare function autonomous. */ PRAGMA AUTONOMOUS_TRANSACTION; BEGIN /* Drop the indexes on a table. */ FOR i IN find_indexes_on(pv_table_name) LOOP EXECUTE IMMEDIATE 'DROP INDEX '||i.index_name; lv_return := 1; END LOOP; RETURN lv_return; END drop_indexes_on; /
You can call the
drop_on_indexes_on function like this:
SELECT drop_indexes_on(UPPER('address_lab')) FROM dual;
Hope this helps those who need to work with dropping indexes.
Sometimes my students find new errors that I’ve never seen. One student did that this week by including an
ORDER BY clause in a subquery that feeds an
INSERT statement. It raises an
ORA-00907 exception, like:
ORA-00907: missing right parenthesis
You can’t include a subquery with an
ORDER BY clause because it generates an error. The reason is simple. A subquery can’t perform a sort operation inside a subquery. Here’s a quick demonstration:
DROP TABLE destination; CREATE TABLE destination ( destination_id NUMBER , destination_name VARCHAR2(20)); INSERT INTO destination ( SELECT 1,'Sample1' FROM dual UNION ALL SELECT 2,'Sample2' FROM dual ORDER BY 1 DESC );
If you remove the
ORDER BY clause, the statement works without a problem. For example, here’s the working version:
INSERT INTO destination ( SELECT 1,'Sample1' FROM dual UNION ALL SELECT 2,'Sample2' FROM dual );
Alternatively, you can include an
ORDER BY clause when you remove the parentheses from around the subquery. This is an example:
INSERT INTO destination SELECT 1,'Sample1' FROM dual UNION ALL SELECT 2,'Sample2' FROM dual ORDER BY 1 DESC;
I hope this helps anybody who runs into the problem.
Somebody wants help provisioning a pluggable database (PDB). While it’s a new concept in the Oracle 12c database, I can sympathize with their plight. It wasn’t clear to me when first working with it, and I couldn’t seem to find a quick tutorial on the web. While it’s in the new Oracle 12c PL/SQL Programming Book, that won’t be out until January 2014.
If you’re new to Oracle 12c, which is most of us, then it’s important to understand that PDB is a private data context. Most of it’s data catalog is private and separate from the overall database. Only a small portion of the database catalog is stored in the container database catalog, and new CDB_ administrative views are added to the database. A PDB is a great solution when you’re leveraging the Editioning feature of Oracle 11g database.
You should note the guarantee from page #9 of Oracle’s Oracle Multitenant:
From the point of view of the client connecting via Oracle Net, the PDB is the database. A PDB is fully compatible with a non-CDB. We shall refer to this from now on as the PDB/non-CDB compatibility guarantee. In other words, the installation scheme for an application backend that ran without error against a non-CDB will run, with no change, and without error, in a PDB and will produce the same result.
Here are the steps that work on both Windows, Linux, or Windows:
- The following SQL command lets you create a pluggable database (PDB) with a
videouser assigned to it:
1 2 3 4 5 6 7
CREATE PLUGGABLE DATABASE videodb ADMIN USER videoadm IDENTIFIED BY Video1 ROLES = (dba) DEFAULT TABLESPACE videots DATAFILE 'C:\APP\ORACLE\ORADATA\ORCL\VIDEO01.DBF' SIZE 500M ONLINE FILE_NAME_CONVERT = ('C:\APP\ORACLE\ORADATA\ORCL\PDBSEED\', 'C:\APP\ORACLE\ORADATA\ORCL\VIDEOPDB\');
Don’t try to create the
DEFAULT TABLESPACE before you provision the database. If you do, you’ll get the following exception:
CREATE PLUGGABLE DATABASE videodb * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01537: cannot add file 'C:\APP\ORACLE\ORADATA\ORCL\VIDEO01.DBF' - file already part of database
- You need to stop the Oracle listener and modify the
listener.orafile. Lines 9 through 12 configure a
VIDEODBOracle SID. After you make the changes, start the Oracle listener.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = C:\app\oracle\product\12.1.0\dbhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll") ) (SID_DESC = (SID_NAME = VIDEODB) (ORACLE_HOME = C:\app\oracle\product\12.1.0\dbhome_1) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) )
- You also need to add a
VIDEOTNS alias to the
tnsnames.orafile for the
VIDEODBpluggable database (PDB).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) VIDEO = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = videodb) ) )
- You connect as the
VIDEOpluggable database with the following syntax (not presently an example in the SQL*Plus Guide). After connecting as the
VIDEODB, you’ll be prompted for a password. The required password is the
Video1password that you used when you set up the
sqlplus sys@VIDEO AS sysdba
- After authenticating as the
SYSDBA, you need to start the
VIDEODBpluggable database, like:
SQL> startup Pluggable DATABASE opened.
You can also start the pluggable database from the generic
SYSDBA account. You only need to change the
CONTAINER context to pluggable database. You can find the status of pluggable databases with the following query:
SQL> COLUMN RESTRICTED FORMAT A10 SQL> SELECT v.name 2 , v.open_mode 3 , NVL(v.restricted, 'n/a') AS restricted 4 , d.STATUS 5 FROM v$PDBs v INNER JOIN dba_pdbs d USING(guid) 6 ORDER BY v.create_scn;
The generic sample database returns this after restarting the database:
NAME OPEN_MODE RESTRICTED STATUS ------------------------------ ---------- ---------- -------- PDB$SEED READ ONLY NO NORMAL PDBORCL MOUNTED n/a NORMAL
The following changes the
CONTAINER context, opens the
pdborcl PDB, and creates a traditional Oracle 11g and backward standard user/schema account in the pluggable database.
SQL> ALTER SESSION SET container=pdborcl; SQL> ALTER PLUGGABLE DATABASE pdborcl OPEN; SQL> CREATE USER johnny IDENTIFIED BY johnny;
Re-query the status of the pluggable databases, and you’ll see:
NAME OPEN_MODE RESTRICTED STATUS ------------------------------ ---------- ---------- -------- PDB$SEED READ ONLY NO NORMAL PDBORCL READ WRITE NO NORMAL
If you have more than one pluggable database, you can use the following to open them all as the CDB’s
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
- As Oracle said during the Oracle 11gR2 release, the DBA role no longer grants
UNLIMITED TABLESPACE. That means you need to grant it as the
SYSDBAfor the PDB, like:
GRANT UNLIMITED TABLESPACE TO videoadm;
- After you’ve done all the prior steps, you can connect with the following as the Administrative
Although making such a connection is a poor choice, it would be better to make a secure connection like the one below. The problem with the former is that it exposes information at the OS command line that would be visible to other users. Connecting to the Oracle SQL*Plus with the
/NOLOG option prevents disclosure of that information.
C:\Users\mclaughlinm>sqlplus /nolog SQL*Plus: Release 188.8.131.52.0 Production ON Tue Aug 13 01:28:30 2013 Copyright (c) 1982, 2013, Oracle. ALL rights reserved. SQL> CONNECT videoadm@VIDEO/Video1 Connected. SQL> SHOW USER USER IS "VIDEOADM"
- Once you’re connected as the
SYSDBArole, you can create standard (pre-Oracle 12c) user/schemas with the old syntax. Below, we create a student account:
SQL> CREATE USER student IDENTIFIED BY student; SQL> GRANT CREATE cluster, CREATE indextype, CREATE operator 2 , CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION 3 , CREATE TABLE, CREATE TRIGGER, CREATE TYPE 4 , CREATE VIEW, UNLIMITED TABLESPACE TO student;
Naturally, you can also add a
USER from the CDB’s
SYSDBA role when in the proper
This has shown you how to provision a pluggable database (PDB). As a side note, you should know that you can only create user-defined common users (at the CDB-level) with a
DBMS_COMPARE isn’t provisioned automatically, and you need to manually apply it in your
ADMIN user for the PDB. I’ve blogged about it here.
Somebody said, I shouldn’t have buried so much information in an answer to a question posed in a comment on a summary blog page. They suggested I put it in a regular blog post, and here it is with as little editing as possible.
The Ron Quizon user provided this sample code and a “What’s wrong with this PL/SQL program?”
1 2 3 4 5 6 7 8 9 10 11 12 13
DECLARE v_name friends.fname%TYPE; v_grade friends.id%TYPE; BEGIN SELECT fname, grade INTO &ssv_name, v_grade FROM friends WHERE v_name = fname; DBMS_OUTPUT.PUT_LINE(NVL(v_name,'No Name ')||' has an id of '||NVL(v_grade, 0)); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('There is no record with '||'id 123'); END;
While this certainly looks like a question from a class on PL/SQL or something from Steven Feuerstein’s PL/SQL question quizzes, I paused before answering it. The give away is the style is what Steven’s advocated for two decades. My guess is that it’s for Steven’s Q&A stuff, which means there’s no harm in answering it because I’m likely not defeating a teacher’s learning objective.
There are two core errors. The first error is an inappropriate assignment target on line #6 and the second is failing to assign a value to the local
v_name variable. If you’d taken the time to create the tables and try it, you should generate an error like this:
SQL> / Enter VALUE FOR ssv_name: Harry OLD 6: INTO &ssv_name, v_grade NEW 6: INTO Harry, v_grade INTO Harry, v_grade * ERROR at line 6: ORA-06550: line 6, COLUMN 12: PLS-00201: identifier 'HARRY' must be declared ORA-06550: line 7, COLUMN 4: PL/SQL: ORA-00904: : invalid identifier ORA-06550: line 5, COLUMN 4: PL/SQL: SQL Statement ignored
Also, at this point if you couldn’t see the error by quick inspection, it should be transparent to you. However, I don’t believe in playing games. Here’s the answer you need:
- The SELECT-INTO statement is an left-to-right assignment operator in SQL (available in all dialects), and the right operand (variable) or list of operands (variables) must be identifier(s). “Identifiers are words. They can be reserved words, predefined identifiers, quoted identifiers, user-defined variables, subroutines, or user-defined types. (Oracle Database 11g PL/SQL Programming on page #51).” In this case, as the right operand(s), they are user-defined variables.
- The & (ampersand) preceding
ssv_namemakes that a substitution placeholder or target, which is a SQL*Plus prompt for a value. The value provided at run-time is assigned to the SQL*Plus placeholder as a string literal during the preparing phase. That phase precedes the anonymous block parse, fetch, and execute cycle. Therefore, you raise a parsing error while running the anonymous block unless the
&ssv_nameinput value is a valid locally declared variable or defined session bind variable name.
- Assuming you input a valid identifier, the next problem is that the
WHEREclause uses an equality comparison operator against the
v_namelocal variable, which holds a null value. That means the
SELECT-INTOalways fails on a no data found error unless you add an assignment statement for the
Anyway, I hope spelling it out in a formal blog post was helpful to someone in the future. If so, leave a comment and let me know.