Category Archives: oracle

Points to consider for creating new tables

  1. Comments ( Always add comments to columns and tables)
  • I always find comments incredibly helpful, they could answer some of the business / content questions. You can add table and column comments.
  • Add comments at the table level at the very least to indicate the contents of the table and the relationship between the two (other than simply knowing parent-> child by way of the Foreign Key).
  • Column comments are useful, as long as they do not simply restate the column name
  • There are undoubtedly some terms for which there is a Glossary of Terms etc. in which case repeating the definition given there is also of little value
  • However if the contents may not be immediately obvious / there is no pre-defined term it is definitely useful.

  2. Column naming convention

  • While I like the underscores (they are natural for DB developers)
  • As a general translation from camel case java attribute names, we have generally ended up without any underscores.
  • Maintain consistency with your database tables.

3. NOT NULL Columns

  • You have only defined the primary key columns as not null (because you have to)
  • There are more columns that cannot be null(i.e The foreign key column for example)
  • If a column cannot be null, we should have a not null constraint on the column. This will prevent an application error from going undetected if a null value is inadvertently inserted.

4. TIMESTAMP/DATE columns

  • Timestamps are ‘interesting’ beasts in oracle, when you consider timezone and some arithmetic functions and implicit type conversions that take place
  • When to use Date and when to use Timestamp?
    • If you have milliseconds, then clearly you have to use timestamps
    • If they are simply to the second precision (or worse a given day), then using a DATE column makes more sense

5. Surrogate Primary Keys and Compound Keys / Unique Constraints

  • We seem to have a general pattern of using surrogate Primary Keys everywhere
  • Sometimes there is a perfectly natural primary key, for example if you have a table defining enumerations, you might as well use the enumeration value as the primary key, there is no need to define a separate PKEY column as well
  • The tables as they stand with the surrogate PKEY give no indication as to which columns should be unique.
    • i.e. could we have multiple records for the same PKEY+SOME_COLOUMN

6 . Performance Considerations

  •  Indexes on Foreign Key Columns
    • You are undoubtedly going to wish to query the child records for a given parent record. You are not allowing for this, so querying child table for a given parent table record will require a full table scan.

7. Caching on Sequences

  • Create sequences  to generate values for the PKEYs
  • Is there is no order dependency on the PKEY column values then should allow caching of the sequence values.
  • This improves the efficiency significantly, but at the cost of potentially having gaps in the sequence (which you could always have if a transaction rolls back) and ‘out of order’ inserts in the case of databases with multiple (RAC) instances.

8 . Partitioning Considerations 

  • Expected Data Volumes
  • Is the data write time critical?
  • Do we expect to read the data far more than we write the data?
  • Do we have to retain the data indefinitely?
  • Do we expect to read ‘ALL current’ records on a frequent basis?
  • can we sacrifice some performance when writing the data in order to optimise the data read

82 total views, 1 views today

SQL Error: ORA-14402: updating partition key column would cause a partition change

Error:

SQL Error: ORA-14402: updating partition key column would cause a partition change
14402. 00000 - "updating partition key column would cause a partition change"
*Cause: An UPDATE statement attempted to change the value of a partition
key column causing migration of the row to another partition
*Action: Do not attempt to update a partition key column or make sure that
the new partition key is within the range containing the old

Solution :

 ALTER TABLE <table name> ENABLE ROW MOVEMENT;

Example:

select * from part_table;

CREATE TABLE part_table (ID NUMBER)
PARTITION BY RANGE (ID)
(partition p0 values less than (1),
PARTITION p1 VALUES LESS THAN (MAXVALUE));

insert into part_table values (0);

UPDATE part_table SET ID = 2;

when you try to update this partition column it gives you ORA-14402.

By default ROW MOVEMENT is DISABLE.

you should alter your table to enable the row movement.

ALTER TABLE part_table ENABLE ROW MOVEMENT;

81 total views, 1 views today

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Solution :

Try changing your ORA properties.

from this :
ORA11gR23 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS =

(PROTOCOL = TCP)

(HOST = orasrv-alanb-lx06)

(PORT = 1521)

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORA11gR23)

)

)

)

  

To this:
ORA11gR23 =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)

(HOST = orasrv-alanb-lx06)

(PORT = 1521)

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORA11gR23)

)

)

  
your SERVICE_NAME = ORA11gR23 should match the name you have given before description.



73 total views, 1 views today

ORACLE : Io exception: The Network Adapter could not establish the connection

Not connecting to correct Database in oracle :



jdbc:oracle:thin:@<server_host>:1521:<instance_name>

the following commands will help:

1. Oracle query command to check the SID (or instance name):

select sys_context(‘userenv’,’instance_name’) from dual; 


2. Oracle query command to check database name (or server host):

select sys_context(‘userenv’, ‘server_host’) from dual;

67 total views, no views today