Author Archives: James Coyle

Oracle ORA-02287: sequence number not allowed here

Get Social!

I’ve recently hit an issue when trying to include a sequence.nextval in an Oracle database view. the database throws the following error:

I can see why that might not be allowed – each time you select from the view you’ll get a new sequence number making it a bit daft for most circumstances.

I say most because I’ve had a need recently; ETL logic at my current client is held in database views with a boilerplate set of code that wraps around that to perform the table load. That’s fine until you want to fabricate ID’s (SIDs) as part of the load.

Solution

The solution here was to create a utility package to wrap the sequence that could be referenced in the view. See the below example code:

You can now query the view without any pesky ORAs.

See the test script here.


Oracle View Sequence Test

Get Social!

 


Move Proxmox Container to Different Storage (Updated for LXC)

Get Social!

2015-03-05 00_18_04-Proxmox Virtual Environment storageThe Proxmox Web GUI does not give us the ability to migrate a container from one storage device to another directly. To move a container onto different storage we have to take a backup of the container and restore it to the same ID with a different storage device specified. This can be time laborious when working with several containers.

This is an update to the OpenVZ script found here.

The below script allows you to move an LXC container from one storage device to another. The process requires that the container be stopped, which the script will handle.

Save the below script into a file called migrate.

Set execution permissions on the script:

The script has several parameters which are detailed below:

  • -d is specified if you would like the script to delete the temporary backup after the process has completed. Leave this out if you would like the backup tar file to be kept, just in case anything goes wrong.
  • -s is required to specify the name of the target storage. You can find this from the Proxmox Web GUI.
  • -c is required for the container ID to migrate.

In addition, the script contains the variable TMP. This will be the location of the backup tar created as part of the migration process and must contain enough space to store the content of the container being migrated. You can change this to suit your environment.

Example command: