« June 2010 | Main | March 2011 »
December 9, 2010
How To Import Data From a File To A Table Using PL/SQL
If you have some columns of data in Excel (or any other way you can get either comma delimited or other standard data format), you can import this data into an Oracle table pretty easily using PL/SQL. Here's how:
Create the table you want to import the data into.
Create a .csv comma delimited file of your data. (You can use a lot of other ways to delimit your data, but this is an easy example.)
Next, go to PL/SQL and open Tools -> Text Importer. There are two tabs in the Text Importer - one to set up your data to import, and one to tell it which table and column to put the data in. Do not go to the second tab until you have got your data set up on the first tab - it just erases all of it if you go back to the first tab and change anything.
Just above the first tab, named "Data from Textfile" there is a row of icons. The very first icon is sort of an "open" icon, and sure enough that is what you use to open your .csv file.
Once your data file is open, PL displays a list of the data in the top pane. You go in to the Configuration pane and set up Fieldcount (how many columns). It will then display entries such as Field1 (+0 ..",") and Field2(blah,blah). Just highlight field one and see if it picks the first field of data from your file. And so on for each field. If it doesn't pick the right field, adjust Field Start or Field End (in this case select Character and ",").
Once you have it set so that it picks the correct data for each field, you are ready for Tab 2 - Data to Oracle. This is pretty simple. Pick your schema, table name, and select each field displayed. Make sure each field is mapped to the correct table column.
Press Import.
That's it!
Posted by rossm at 2:57 PM | Comments (0)
Movin' On
Well, it's time to bid farewell to Sundown, our workhorse Sun server that houses the production Oracle database and many other critical services for campus. I kind of hate to see it go - it has been an extremely reliable platform with plenty of horsepower and a couple of humongous storage arrays hooked to it. But, as everyone knows, nothing stays put for long in the computing world. So, Sundown is going to be replaced with a Solaris LDOM (much like a Windows VM) - actually, two LDOM's.
Michael Ellis and I have been work, work, working on Aero to get it ready to move. This included such things as cleaning up old schemas, removing unused tablespaces, and general db housekeeping. It is now in pretty good shape.
The process for moving an Oracle db from one server to another is a pretty good challenge. Luckily for us, we are maintaining the same OS (Solaris 10, 64 bit), and we have configured the new LDOM to mirror the existing file structure. I installed and patched the same software version onto the LDOM, so now we just need to move the database.
I have gained a better appreciation for the difference between the "binaries" of an Oracle install, and the "database". The binaries includes all of the software (Explained to me as the Car. The database is the Gas.) So we have this Car sitting there on the LDOM, which for all intents and purposes is a mirror of our existing production database. Here's how we fill 'er up, then start the engine.
First, shutdown the existing db with shutdown immediate (not abort). Then copy the database files, control files, and redo files over to the LDOM. Tweak the initaero.ora, listener config, shell (environment variables), tnsnames.ora, and probably one or two more files to reflect the new server's name.
Once all this is in place, we "startup mount pfile=path/initaero.ora" and see what happens. In practice we have always forgotten a parameter or two, or something, but the db will tell you why it can't mount. You fix the problem, then try again. Pretty soon, the db will mount. Then, it's "alter database open". With a little luck, what you now have is the production db sitting on your new server.
This is one of those situations in life where you can describe the process pretty easily. However, actually making *everything* work upon the move is way more complicated. The production db has tons of bells and whistles, special cases, and add-ons that have been configured over time. Each of these needs special attention.
Stay tuned for the movement of the HTTP Server (thanks Ron), and the process for moving the dictionary objects to a new file system.
Posted by rossm at 10:37 AM | Comments (0)
Moving Objects To New Tablespace
Today I took a bit of time to clean up a messed up tablespace on Aero. A while back Dale and I reorganized a tablespace to make it locally managed with automatic (as opposed to manual) segment management.
This is an automated process, and the database renames the tablespace from myspace to myspace_reorg0. Well, we forgot to modify the schema owner's properties to default to the new tablespace. End result = all new objects went right back in to the original tablespace.
My plan is to now fix the schema properties to default to the auto tablespace, then move all objects into the new tablespace. This is a pretty straightforward process for "normal" objects such as tables and indexes. A command such as this does the trick, and takes less than a second for most reasonably sized objects:
ALTER TABLE [TABLENAME] MOVE TABLESPACE [TABLESPACE NAME];
or
ALTER INDEX [INDEX NAME] REBUILD TABLESPACE [NEW TABLESPACE NAME];
Well, that's fine, but this particular tablespace also contains a bunch of these:
MYSPACE.SYS_LOB0944542804C48302 - LOBSEGMENT
Ya, dang blobs and clobs everywhere - all segments stored in the wrong tablespace. Well, this can also be fixed, but first you have to know the tables to which these lobsegments belong. I haven't yet figured out a good way to identify the table-lobsegment relationship out of the data dictionary. Instead, I just looked through the schema for tables with blob or clob columns in them. Once located, this is the syntax for moving the lobsegment to the correct tablespace:
ALTER TABLE myspace.test_lobtable
MOVE LOB(blobclobcolumnnamehere) STORE AS (
TABLESPACE myspace_reorg0
)
The second complication is that the tablespace contains a bunch of these, too:
MYSPACE.BIN$bp8vY4FWOlfgRAADunpu8Q==$0 - TABLE
Unless someone is a naming putz, this is a flashback/recycle bin object. You can get rid of these (theoretically) with a command like this:
PURGE TABLESPACE MYSPACE;
Unfortunately, this didn't remove any of these objects - they are apparently no longer recognized by the database - I'm really not sure why it doesn't remove them. It just doesn't.
Once everything except these crazy-named objects are all moved, the next step is to take the original myspace tablespace offline, and see what happens/breaks, if anything.
Assuming nothing breaks, then the myspace tablespace can be dropped, and the myspace_reorg0 with the correct segment management can be used.
Posted by rossm at 10:14 AM | Comments (0)