PL/SQL

How to Use Bind Variable in Oracle

Posted on
Shareable Cursor Using bind variables can make statements with minor differences become literally the same so as to share the same cursor when executions. That means, SQL engine doesn’t have to parse those similar statements every time they are executed, it reuses the shared cursor to service millions of executions. Unshareable Cursors In contrary, dynamic […]
Query

How to Compare Two Tables Definition

Posted on
Compare Two Tables’ Column Names Whenever you concern the differences between two tables’ definition, you can use queries to compare the columns of two tables. For example, a table named EMPLOYEES_2 which is very similar with the original table EMPLOYEES. And we’d like to know the differences in columns. We can compare both tables like […]
SQL

How to Resolve ORA-00947: not enough values

Posted on
ORA-00947 With respect to ORA-00913: too many values, column list mismatching can result another type of error, called ORA-00947: not enough values. Let’s see how we reproduce the error. First of all, we create an empty table employees_2 from employees. SQL> create table employees_2 as select * from employees where 1 = 2; Table created. […]
Query

How to Resolve ORA-00913: too many values

Posted on
ORA-00913 When SELECT Let’s see a SQL statement containing a subquery that can reproduce ORA-00913 for an example. SQL> select * from employees where department_id in (select department_id, manager_id from departments where location_id = 1700); select * from employees where department_id in (select department_id, manager_id from departments where location_id = 1700)         […]
Query

How to Limit Number of Rows Returned in Oracle

Posted on
Limit Number of Rows Returned In MySQL, we use LIMIT n cluase to limit number of rows returned. In Oracle, we may need to do more works to fetch our target rows. First, we listed all sorted data like the following: SQL> conn hr/hr; Connected. SQL> select employee_id, first_name, last_name from employees order by 3; […]
DDL

How to Resolve OGG-00519 REPLICAT DDL Errors in GoldenGate

Posted on
OGG-00519 In the remote database, you may not create all objects to resolve dependency problems. Here are some OGG-00519 DDL errors in REPLICAT report below: 2017-08-22 19:52:03  ERROR   OGG-00519  Fatal error executing DDL replication: error [Error code [1435], ORA-01435: user does not exist SQL ALTER SESSION SET CURRENT_SCHEMA=”PUBLIC”], no error handler present. 2017-08-23 20:18:54 […]
SQL

How to Move Tables by SQL Script

Posted on
I usually move some tables for data reorganization, and I found the procedure is pretty routine:Moving the table.Rebuilding indexes related to this table.Analyzing all segments related to this table.So I wrote a SQL script to handle this automatically, one table at a time.[oracle@test ~]$ cat alter_table_move.sqlset escape on;accept table_owner char prompt ‘Table Owner: ‘;accept table_name […]
DDL

How to Resolve ORA-02494: invalid or missing maximum file size in MAXSIZE clause

Posted on
Generated DDL from tools may not be executable sometimes. I got ORA-02494 when I execute DDL scripts that were generated from other database by TOAD, a very popular administration tool used by DBA. Irrational DDL SQL> CREATE TABLESPACE PERSON DATAFILE   2    ‘/u02/app/oracle/oradata/datastore/.ACFS/snaps/orcl/ORCL/datafile/user01.dbf’ SIZE 5G AUTOEXTEND ON NEXT 20M MAXSIZE 4G   3  LOGGING […]