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 […]
DDL

You Cannot Resize a DataFile in “ALTER TABLESPACE” Except …

Posted on
According to SQL syntax of Oracle database, you can’t resize the data files by using ALTER TABLESPACE except the tablespace was created as BIGFILE, but we know most of tablespaces are created as SMALLFILE. So if you need to resize a normal data file, you’d better to use ALTER DATABASE. SQL> alter database datafile ‘/oracle/oradata/user01.dbf’ […]
DDL

The Default Values in “ALTER SYSTEM SET” Statement

Posted on
Without declaring SCOPEIf you do not specify this clause, then:If a server parameter file was used to start up the database, then BOTH is the default.If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.Started bySCOPEspfileBOTHpfileMEMORYWithout declaring SIDIf you do […]
SQL

How to Correct Invalid Identifiers in Statements

Posted on
Let’s see some wrong types of identifiers to clarify the principles. Must start with letters. RMAN> create restore point 20151028 preserve; … ORA-00904: : invalid identifier So I added a letter “s” before the identifier to correct the problem. RMAN> create restore point s20151028 preserve; Statement processed Must consist of only alphanumeric characters and the […]
PL/SQL

External Procedures May Consume a Lot of Memory

Posted on
External procedures are very special procedures stored outside of the database, and it can be coded by C or Java language. Since the external libraries were executed on OS-level, their behaviors are not managed and bounded by database anymore, so it could become a security leak. The users who have been granted the privilege to […]
Session

How to Compose Kill Blocking Sessions Statements at a Time

Posted on
After the blocking sessions are identified, DBA should notify the session owners and leave them a chance to exit sessions gracefully by themselves. If things go worse, DBA must terminate these sessions right away. Killing Blocking Sessions For Separate Nodes Here is a sample SQL which can both identify and compose kill session immediate statements […]