Category Archives: Database

Regular Expressions in Oracle SQL

Posted on by
Reply

I Know Regular Expressions

For a project I needed to change the picture URL’s of all users. Fortunately Oracle SQL supports regular expressions making this problem much easier to solve.

SQL to select pictures to change:

SELECT picture FROM users
WHERE REGEXP_LIKE(picture, '^graph\.facebook\.com.*?type=square');

SQL to update pictures from type=square to type=large:  

UPDATE users SET picture =
REGEXP_REPLACE(picture,
               '^(graph\.facebook\.com.*?type=)square$', '\1large')
WHERE REGEXP_LIKE(picture, '^graph\.facebook\.com.*?type=square$');

SQL to select updated pictures:

SELECT user_id, picture FROM users
WHERE REGEXP_LIKE(picture, '^graph\.facebook\.com.*?type=large$');

For more information please read Using Regular Expressions in Oracle Database.

Create Tablespaces in Oracle

Posted on by
Reply

Zero Table | Flickr

Zero Table by CommandZed

Previously I posted about how to import and create users in Oracle and in those examples I used the tablespaces provided by Oracle, users and temp.  But often you will want to create your own tablespace and use that one as the default tablespace for your users.

Here is an example of how to create a tablespace with the name foo of size 1 GB. Note that it is created in /Users/oracle/oradata/orcl which is the default folder for dataspaces on Mac OS X.

create tablespace
foo
datafile '/Users/oracle/oradata/orcl/foo.dbf'
size 1g;

Here is an example of how to create the same tablespace except now you are allowing it to grow bigger in 100 MB increments up to a maximum of 2 GB.  If you don’t specify the maximum it will grow unlimited.

create tablespace
foo
datafile '/Users/oracle/oradata/orcl/foo.dbf'
size 1g
autoextend on
next 100m
maxsize 2g;

If your tablespace is not big enough you can resize it using syntax like this.

alter database
datafile '/Users/oracle/oradata/orcl/foo.dbf'
resize 10g;

To see how big your tablespaces are you can use this query.

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

Here is an example of how to drop a tablespace including its contents and datafiles.

drop tablespace foo including contents and datafiles

For further reading please see Oracle create tablespace & alter tablespace syntax, Tablespace – Oracle FAQ and How enlarge or decrease the size of an Oracle Tablespace.

SQL Delete in One Table Based on Values in Another Table

Posted on by
Reply

Growing | Flickr

Growing by Simon Peckham

Delete From One Table Whose Values Don’t Appear in Another Table

Sometimes you will find that you have items in a table whose values reference items in another table that no longer exist.  For example in ATG you may have orders that reference profiles that no longer exist.  This could happen if an order is for an anonymous profile that was deleted.

Here is an example of how to delete items in a table whose values reference items in another table that no longer exist, in this case ATG orders whose profiles no longer exist.

DELETE FROM dcspp_order
WHERE profile_id
NOT IN
(
  SELECT id
  FROM dps_user
);

This example does not actually work because of the dependencies on the dcspp_order table so please don’t try it. Smile

Delete From One Table Based on Values in Another Table

Sometimes you want to delete items in one tables based on values in another table.  You can do this similarly to the previous case.

DELETE FROM dcspp_order
WHERE profile_id
IN
(
  SELECT id
  FROM dps_user
  WHERE id LIKE '6%'
);

This example also does not actually work because of the dependencies on the dcspp_order table so please don’t try it. Smile

For further reading please see How to delete records from a SQL Server database and SQL Delete Rows Based on Another Table.

SQL Insert in One Table Based on Values in Another Table

Posted on by
Reply

Love's Old Sweet Song on Flickr

(Photo: Love’s Old Sweet Song by linda yvonne)

The syntax for doing this is similar to doing an update in one table based on values in another table yet simpler.

INSERT INTO suppliers (name)
SELECT customers.name
  FROM customers
  WHERE customers.id = suppliers.id);

If you want to add constant values into the insert you can do something like this.

INSERT INTO suppliers (name, city)
SELECT customers.name, 'Toronto'
  FROM customers
  WHERE customers.id = suppliers.id);

For further reading please see SQL INSERT INTO.

How to Alter Table

Posted on by
Reply

Rain on a window in Sunnyvale

(Photo: Rain on a window in Sunnyvale by basictheory)

There are various ways to alter a table and I usually forget what they are so I am writing this post to remind me. 🙂

Columns

ALTER TABLE foo DROP COLUMN nickname;
ALTER TABLE foo RENAME COLUMN name TO nickname;
ALTER TABLE foo ADD name VARCHAR2(254) DEFAULT 'Frank' NOT NULL;
ALTER TABLE foo ADD age INTEGER DEFAULT 0 NOT NULL;
ALTER TABLE foo MODIFY name VARCHAR2(500);

Constraints

ALTER TABLE foo DROP CONSTRAINT foo_a_f;
ALTER TABLE foo ADD CONSTRAINT foo_b_f FOREIGN KEY (bar_id) REFERENCES bar (id);

If you forget the name of a constraint and you can try to find it using this handy piece of SQL.

SELECT constraint_name FROM user_constraints WHERE constraint_name LIKE 'foo_%_f%';

SQL*Plus Commit on Exit

Posted on by
Reply

EXIT on Flickr

I was always doing a commit before exiting SQL*Plus when it occurred to me today that maybe I didn’t need to do that.  Doing a Google search quickly answered that for me.

If you issue a graceful exit (via the “exit” or “quit” command), sqlplus will always issue a commit. However, if you were to be ungracefully disconnected, for example by closing your terminal window, then PMON will issue a rollback like it does with any other disconnected session.

sqlplus commit-on-exit?

Therefore there is no need to do a commit before you exit.

You can also set autocommit on, it is off by default, but I would not recommend doing this.

During interactive usage with sqlplus, Oracle also supports an AUTOCOMMIT option. With this option set to ON each individual SQL statement is treated as a transaction an will be automatically commited right after it is executed. A user can change the AUTOCOMMIT option by typing 

 SET AUTOCOMMIT ON

or 

 SET AUTOCOMMIT OFF

whereas by typing 

 SHOW ALL

a user can see the current setting for the option (including other ones).

Oracle SQL Transactions

How to Import and Create Users in Oracle

Posted on by
Reply

Oracle When you do an import sometimes you will find you will need to also create a user for this new set of data. Today I find myself in that situation as I imported data from Bell Canada and set up a new user for that data.

  1. Create a new user for the data.

    This example creates a user named "foo" with the password "foo".  Foo is a typical user that can create sessions, synonyms, procedures and tables.

    CREATE USER FOO IDENTIFIED BY FOO
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

GRANT CREATE SESSION to FOO;
GRANT CREATE SYNONYM TO FOO;
GRANT CREATE PROCEDURE TO FOO;
GRANT CREATE TABLE TO FOO;
GRANT CREATE VIEW TO FOO;

    For more information please read Oracle’s CREATE USER documentation.

    If you want this user to be a DBA you can grant that to her too

    GRANT DBA TO FOO;
  2. Import the dump using the new user.

    This example imports the dump from the file "dump.dmp". This dump was created using the user "foo" and is being imported to the user "foo". The dump will be logged in the file "dump.log". 

    imp system/system@example file=dump.dmp log=dump.log fromuser=foo touser=foo

    For more information please read Oracle’s Import Export FAQ.

  3. To redo recreate user and then import again.

    If you need to redo an import the easiest thing to do is to drop the user, recreate her and then do the import again. When you drop the user specify CASCADE to drop all the objects in the user’s schema.

    DROP USER FOO CASCADE;

    For more information please read Oracle’s DROP USER documentation.

Ruby on Rails and Oracle

Posted on by
1
  1. Get the Ruby OCI8 driver.  Download the file that ends with “mswin32.rb” and install like this: 
    E:\ruby>ruby ruby-oci8-1.0.3-mswin32.rb
Copy OCI8.rb to e:/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8
Copy oci8.rb to e:/ruby/lib/ruby/site_ruby/1.8
Copy oci8lib.so to e:/ruby/lib/ruby/site_ruby/1.8/i386-msvcrt
OK?
Enter Yes/No: Yes
Copying OCI8.rb to e:/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8 ... done
Copying oci8.rb to e:/ruby/lib/ruby/site_ruby/1.8 ... done
Copying oci8lib.so to e:/ruby/lib/ruby/site_ruby/1.8/i386-msvcrt ... done
OK

    You can test the driver by running a query using Ruby.

    E:\>ruby -r oci8 -e "OCI8.new('foo','12345','sid').exec(
'SELECT * from users') do |r| puts r.join(' | ') ; end"
  2. Install the ActiveRecord Oracle adapter.gem 
    E:\ruby>install activerecord-oracle-adapter --source http://gems.rubyonrails.org
  3. Update config/database.yml to connect to Oracle 
    development:
  adapter: oracle
  database: sid
  username: foo
  password: 12345
  timeout: 5000
  4. Test by doing a rake db:migrate.
  5. Test by running the Ruby on Rails server and making sure there are no errors upon startup.

This article is based on these articles.

Best Practices for Creating Tables

Posted on by
Reply

Recently our DBA recommended the following for creating tables.

  1. All constraints (primary keys, unique keys, foreign keys, etc….) should be declared outside of the CREATE TABLE …. statements, and instead done as ALTER TABLE statements.
  2. All tables must have table and column comments to provide information for the data dictionary/schema metadata.

Not Best Practice:

CREATE TABLE items (
  id   VARCHAR2(40) NOT NULL,
  type NUMBER(5)    NOT NULL,
  PRIMARY KEY (id)
);

Best Practice:

CREATE TABLE items (
  id   VARCHAR2(40) NOT NULL,
  type NUMBER(5)    NOT NULL
);
ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY (id);
COMMENT ON TABLE items IS 'repository items';
COMMENT ON COLUMN items.id IS 'primary key (repository id)';
COMMENT ON COLUMN items.type IS 'item type';

I asked the DBA why this is considered best practice and this is what he said.

The DBA’s put indexes into a different tablespace than the table itself for storage, admin, and somewhat performance reasons (NetApp spreads out the I/O so does not quite apply to Upromise environment).

If the PK is part of the table create statement, they have to break out the statement in order to put the PK into a different tablespace or different storage parameters than the table. Having the statements separate from the start makes things smoother.

– Jeff Janousek, Upromise DBA