Wednesday, July 26, 2006

Moving an IOT

There was a question on OTN DB general on how to move an IOT ( index organized table ) from one tablespace to another.

Pretty easy answer, but I realized I had never done it and was assuming it was a simple MOVE command. So what the heck, I am sick and tired of reading old emails and have finished laughing over the Chronicles of George. So lets go see what damage I can do and possibly learn something.



SQL> SET LINES 132
SQL> SELECT * FROM V$VERSION;

BANNER                                                                                                                              
----------------------------------------------------------------                                                                    
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production                                                                    
PL/SQL Release 9.2.0.7.0 - Production                                                                                               
CORE 9.2.0.7.0 Production                                                                                                           
TNS for HPUX: Version 9.2.0.7.0 - Production                                                                                        
NLSRTL Version 9.2.0.7.0 - Production                                                                                               

SQL> --CREATE TABLESPACE TEST1 DATAFILE 'namehere' SIZE 16M ;
SQL> --CREATE TABLESPACE TEST2 DATAFILE 'namehere' SIZE 16M;
SQL> DROP TABLE TIOT;

Table dropped.

SQL> CREATE TABLE TIOT
  2  (ID NUMBER
  3  ,DATA VARCHAR2(30)
  4  ,CONSTRAINT IDPK PRIMARY KEY(ID)
  5  )
  6  ORGANIZATION INDEX
  7  TABLESPACE TEST1
  8  OVERFLOW TABLESPACE TEST2
  9  ;

Table created.

SQL> INSERT INTO TIOT (ID,DATA) VALUES (1,'A');

1 row created.

SQL> INSERT INTO TIOT (ID,DATA) VALUES (2,'B');

1 row created.

SQL> INSERT INTO TIOT (ID,DATA) VALUES (3,'C');

1 row created.

SQL> INSERT INTO TIOT (ID,DATA) VALUES (4,'D');

1 row created.

SQL> INSERT INTO TIOT (ID,DATA) VALUES (5,'E');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TIOT;

        ID DATA                                                                                                                     
---------- ------------------------------                                                                                           
         1 A                                                                                                                        
         2 B                                                                                                                        
         3 C                                                                                                                        
         4 D                                                                                                                        
         5 E                                                                                                                        

SQL> SELECT IOT_TYPE,TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='TIOT';

IOT_TYPE     TABLESPACE_NAME                                                                                                        
------------ ------------------------------                                                                                         
IOT                                                                                                                                 

SQL> SELECT SEGMENT_TYPE,TABLESPACE_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='IDPK';

SEGMENT_TYPE       TABLESPACE_NAME                                                                                                  
------------------ ------------------------------                                                                                   
INDEX              TEST1                                                                                                            

SQL> SELECT INDEX_TYPE,TABLE_NAME,TABLESPACE_NAME,STATUS FROM USER_INDEXES WHERE INDEX_NAME LIKE 'IDPK';

INDEX_TYPE                  TABLE_NAME                     TABLESPACE_NAME                STATUS                                    
--------------------------- ------------------------------ ------------------------------ --------                                  
IOT - TOP                   TIOT                           TEST1                          VALID                                     

SQL> ALTER TABLE TIOT MOVE TABLESPACE TEST2;

Table altered.

SQL> SELECT * FROM TIOT;

        ID DATA                                                                                                                     
---------- ------------------------------                                                                                           
         1 A                                                                                                                        
         2 B                                                                                                                        
         3 C                                                                                                                        
         4 D                                                                                                                        
         5 E                                                                                                                        

SQL> SELECT IOT_TYPE,TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='TIOT';

IOT_TYPE     TABLESPACE_NAME                                                                                                        
------------ ------------------------------                                                                                         
IOT                                                                                                                                 

SQL> SELECT SEGMENT_TYPE,TABLESPACE_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='IDPK';

SEGMENT_TYPE       TABLESPACE_NAME                                                                                                  
------------------ ------------------------------                                                                                   
INDEX              TEST2                                                                                                            

SQL> SELECT INDEX_TYPE,TABLE_NAME,TABLESPACE_NAME,STATUS FROM USER_INDEXES WHERE INDEX_NAME LIKE 'IDPK';

INDEX_TYPE                  TABLE_NAME                     TABLESPACE_NAME                STATUS                                    
--------------------------- ------------------------------ ------------------------------ --------                                  
IOT - TOP                   TIOT                           TEST2                          VALID                                     

SQL> INSERT INTO TIOT (ID,DATA) VALUES (99,'Z');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TIOT;

        ID DATA                                                                                                                     
---------- ------------------------------                                                                                           
         1 A                                                                                                                        
         2 B                                                                                                                        
         3 C                                                                                                                        
         4 D                                                                                                                        
         5 E                                                                                                                        
        99 Z                                                                                                                        

6 rows selected.

SQL> ALTER TABLE TIOT MOVE TABLESPACE TEST1 OVERFLOW TABLESPACE TEST1;

Table altered.

SQL> SELECT * FROM TIOT;

        ID DATA
---------- ------------------------------
         1 A
         2 B
         3 C
         4 D
         5 E
        99 Z

6 rows selected.


SQL> SPOOL OFF


Tuesday, July 25, 2006

Questions

Question from Burelsons forum . Poster wanted to do a select from dba_segments and get a row count of the table without doing an analyze.

I can only fathom why.

"But who are we to ask why? Our task is to simply do, or die"

That is a quote as best I remember it, I wish I could remember it properly. Even google can't find it I have mangled it so bad.


The answer is in the link, but to have it here too:

CREATE OR REPLACE TYPE COUNTROWSTYPE AS OBJECT
( OWNER VARCHAR2(50),
SEGMENT_NAME VARCHAR2(100),
SEGMENT_TYPE VARCHAR2(100),
PARTITION_NAME VARCHAR2(100),
BYTES NUMBER,
NUMROWS NUMBER
)
/


CREATE OR REPLACE TYPE COUNTROWS AS TABLE OF
COUNTROWSTYPE
/

CREATE OR REPLACE PACKAGE COUNTROWSPKG
AS
FUNCTION DOCOUNT(P_SCHEMANAME VARCHAR2)RETURN COUNTROWS PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY COUNTROWSPKG
AS
FUNCTION DOCOUNT(P_SCHEMANAME VARCHAR2) RETURN COUNTROWS
PIPELINED
IS
O_ROWCOUNT NUMBER;
BEGIN
FOR X IN (SELECT OWNER, SEGMENT_NAME , SEGMENT_TYPE,PARTITION_NAME, BYTES
FROM DBA_SEGMENTS
WHERE OWNER = UPPER( P_SCHEMANAME ) AND ROWNUM<=30
ORDER BY SEGMENT_NAME) LOOP
IF X.SEGMENT_TYPE='TABLE' THEN
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||X.OWNER||'.'||X.SEGMENT_NAME INTO O_ROWCOUNT ;
END IF;
PIPE ROW ( COUNTROWSTYPE( X.OWNER,X.SEGMENT_NAME,X.SEGMENT_TYPE,X.PARTITION_NAME,X.BYTES,O_ROWCOUNT));
END LOOP;
RETURN;
END;
END;
/


select * from table( COUNTROWSPKG.DOCOUNT('SYSTEM') );

You can see I limited the cursor and warned the poster a couple of times. Dangerous package. I could just imagine if some unknowning person ran this on say a schema with 300-400 million records in each table and a few hundred tables.

Thomas Kyte is on the ball this week

asktom.oracle.com has been accepting questions 2 nights in a row!

Too bad I can never think of something interesting and relevant to ask him.


Backups

Get back from vacation. Check into the failed backups as per the previous post.

1 server with 4 databases, 2 24x7 high usage systems (200-300 users), 1 reporting DB (combining the previous two) and 1 sunsetted system DB (almost zero users). No backups (rman) for 16 days.

6 different people get the emails, including a manager and the lead system administrator. Nobody looked at the problem, everybody read the emails. Ignoring 16 obvious FAILED emails.
Fixed the problem which was the scratch backup disk was simply out of space on the server, took about 3 minutes. The best answer was "I thought it could wait until you get back."

I need a vacation.


Sunday, July 23, 2006

Back to work

I am back to work tomorrow (sigh).

393 emails waiting for me. All work related.

20 voice mail messages - but I do believe that is the maximum number of messages our system can hold.

I am going to need a vacation to recover from amount of work needed to catch up on work from my vacation.

I am more worried about the large number of failed back up messages with no visible or documented attempt to fix the problems than anything else that might have cropped up.


Wednesday, July 19, 2006

Learn Something Every Day

This once again was brought on by a question on OTN.

In a large migration process one of my steps is to drop and rebuild indexes. Do I still need to ALSO do a ANALYZE INDEX to make sure the index is up to date with data and good to go with the best performance possible? Doesn't drop and recreating force it to be most recent and I would basically be doing same work twice?
This is Oracle 8i I am running on

My initial answer nope, statistics are not created when you create an index.

I posted it, then thought - well, I posted without actually checking - and low AND behold. It doesn't do it for version 8 or 9. But it appears to for version 10.

You learn something every day.

To try it for yourself you can see the following:


The conclusion is - on oracle 8 or 9, if you drop and recreate an index the statistics are not gathered at create. In V10, the statistics are gathered at create. I guess I will have to stuff this away for an AskTom question because I can't find any documentation relating to this. But it is something difficult to search for.

Changing column order

Somebody on OTN asked the question:

I would like to know if there is an Oracle feature to change the order of columns in an existing table. The table is quite huge (several 100 millions rows). I want to avoid to re-create the table and migrate the data.

I have seen this question a couple of time on the net over the last little while. I posted the answer below, and thought I might as well put it on the blog as well. The thread continued on after the initial question to include a bunch of more detail that made my answer not the correct answer. I guess I should have simply asked "Why" instead of trying to figure out what the person really wanted.

Unfortunately I have had to do this in the past:

Don't drop your table of course, but I wanted this to be complete:

DROP TABLE REORDER;

CREATE TABLE REORDER
(COLUMN4 NUMBER
,COLUMN3 NUMBER
,COLUMN2 NUMBER
,COLUMN5 NUMBER
,COLUMN1 NUMBER
,COLUMN6 NUMBER
);

Now if you select it the columns come up in the order it was created
SELECT * FROM REORDER;

You can simply select the columns in the order you want:

SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM REORDER;

You can create a view that the users can use if you wish:

CREATE OR REPLACE VIEW ORDERED_REORDER AS SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM REORDER;

SELECT * FROM ORDERED_REORDER;


clean up
DROP VIEW ORDERED_REORDER;


I have done this on SMALLER tables less than a few thousand with 100 million+ rows, I wouldn't do this without some thorough testing.

change the name of the table


RENAME REORDER TO ORIG_REORDER;

create a view to look like the original table

CREATE VIEW REORDER AS SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM ORIG_REORDER;

Now the view looks the way you want:

SELECT * FROM REORDER;

You can insert etc:
INSERT INTO REORDER (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6)
VALUES (1,2,3,4,5,6);
COMMIT;

Some apps won't allow this, they need tables and won't allow views.
Test test test. But first, I would suggest getting a good business case for this as this is an oddball request. Those requests do exist but get it in writing and see if there isn't a better way to handle the request.

Tuesday, July 18, 2006

Zoundry is pretty good

I am getting more familiar with Zoundry and learning the work arounds and proper way of posting to a blog. I had been designing in MS word and then exporting as HTML. Well, I should have known better. Now I have learned to compose in Zoundry and export from Zoundry to MS word if I want the information in a different format.

I am learning. I went through most of the old posts and reformatted them to look MUCH better than before.

Zoundry is a good tool.


Oracle Trace Analyzer

Eddie Awad recently posted about Oracle Trace Analyzer here. An excellent post, the quality of posts I hope to achieve in the future. I use the trace analyzer quite a bit, it is an excellent tool and it is one of those questions "doesn't everybody use that? " I ask myself about.

If you don't use trace analyzer you should read Eddie's post and investigate it. The amount of information supplied by the tool is better than TKPROF and is well worth the extra initial installation time.

I have become completely disheartened with the ability of the mainstream blogs to display even the most simple HTML/CSS in the format that I desire. I have tested wordpress as well and just can not get any of the systems to display posts the way I want. Very disappointing.

I used Zoundry and reformatted my post on creating a UNIX script to trace your current session from March 10/2006.



Analytics

I put together a "test paper" on some SQL analytics I use a lot. The document was done for internal use with only positive feedback, so I thought I would share it here. Nothing ground breaking just some hopefully real world examples.

You can view it in HTML or PDF. The script used to generate the test invoice data is here.

As the "part 1" suggests I am planning on writing at least one companion to this.

Saturday, July 15, 2006

Vacation is almost over

My vacation is almost over. 2 of the 3 weeks vacation is over. Seems like I was working only yesterday. Starting to get bored though. The weather hasn't been the best for camping with cool nights and damp days so we have given up on that. I have been working around the house, that type of work never seems to end. I put built some shelves for my daughters room. They turned out much better than I thought they would. She was very excited at having more place to put her stuff. Actually, she is a lot like my wife, she was more excited at more room to put MORE stuff as the stuff she has already has its own spot. I have now started on a desk made out of MDF for my son's room to replace the small students desk he has. He would like to have a computer in his room instead of all of the computers in the office downstairs. We had his computer on his desk in his room, but there was no room for him to do his homework so we moved it into the office. Now, we will be moving it back. I already have network ran to his room, so once the desk is finished it should be a quick job of plugging it all in. I think for Christmas this year new computers for the kids with be the only present they get. My daughter uses a laptop , I bought her a toshiba 4090xDVD laptop last year (used) and she just loves having it. So I think a new Dell laptop for her, and possible a new Dell PC for my son as he is an avid gamer.

I checked my work email the other night, enough to get my blood boiling, the bad decisions that were made that I will have to deal with. I guess I will be rested and relaxed when I get back and will be able to deal with the problems as they rear their heads.

The new DBA starts next month, I am looking forward to that but he and I will work very little together unfortunately, he will probably be on a different floor. His job is going to be 100% dedicated to our Oracle Financial Applications suite. My junior DBA has realized he was in way over his head and took a transfer to a analysts position until he can learn more. It was a good choice all around. His DBA skills were good, but green, he was assumed to have the same skills and experience as me by others and he was just getting lost in the workload. I still will be giving him DBA related work so he can learn his desired career path. He needs to learn more about the company and how the systems he is working on relate to how the company does business. He was at the center of the company and getting bombarded from all sides with no time to sit and learn. The management finally went through (while I was on holidays) and removed the DBA job title from everybody who was just a DBA in title and no more. So, now, that means I am the only person with the DBA title in the company. All 10,000+ of us. Doesn't really mean anything to the company and it was like this before, but now I can be referred to as THE DBA instead of A DBA or THE SENIOR DBA. Can you be in a senior position with no juniors?

Friday, July 14, 2006

Random Data Generation

I was asked by a friend at another company if Oracle provides a tool for generating random data so he can fill up some test tables to test their index strategy with a large amount of data in the system. He is a "Developer" but I do not hold that against him, he is an upstanding guy and committed to his job. Instead of simply answering him, I decided to post what I was going to send. After fighting with blogger to get the layout the way I wanted, and moving from a post, to a linked HTML file, back to a post and trying out various free blog writing tools. I basically gave up and started using Zoundry, it does a fine job but I still can't get blogger to display the output the way I wanted. So be it...


Oracle provides the DBMS_RANDOM package for generating random numbers and even random strings. The package is available in oracle 9 or higher.


Random Numbers
The function DBMS_RANDOM.VALUE can be used for generating random numbers. If you do not pass any parameters the number generated is between 0 and 1.

SELECT DBMS_RANDOM.VALUE VALUE FROM DUAL;


This will generate a random number between 1 and 100.


SELECT DBMS_RANDOM.VALUE(1,100) VALUE FROM DUAL;


You can use ROUND or TRUNC to remove the decimals.


SELECT ROUND(DBMS_RANDOM.VALUE(1,100)) VALUE FROM DUAL;

This will generate a random number between 1000 and 9999.


SELECT ROUND(DBMS_RANDOM.VALUE(1000, 9999)) VALUE FROM DUAL;

Random Strings
You can use DBMS_RANDOM.STRING to generate random strings. The first parameter is OPT which allows you some control over the type of characters being generated. The following are the allowable values to be passed as the OPT parameter.

'u','U' : upper case alpha characters only
'l','L' : lower case alpha characters only
'a','A' : alpha characters only (mixed case)
'x','X' : any alpha-numeric characters (upper)
'p','P' : any printable characters


The second parameter is the number of characters to be generated. The maximum value is 2000.
The following will generate 30 random uppercase characters.


SELECT DBMS_RANDOM.STRING('U',30) VALUE FROM DUAL;


Generate 30 random printable characters


SELECT DBMS_RANDOM.STRING('P',30) VALUE FROM DUAL;


Lets say we want to generate a little more real world data for testing. We can generate a table of 50 rows of random data very easily, I tend to use the 'P' parameter because you get all of the printable characters so your test data is loaded with ampersands '&' and comma's ','.


CREATE TABLE TEST_DATA AS SELECT DBMS_RANDOM.STRING('P',30) VALUE FROM DUAL CONNECT BY LEVEL<=50;
SELECT * FROM TEST_DATA WHERE ROWNUM <= 5;

VALUE
------------------------------------------------------------------
71E/BQh;3[WU&f -d7IV22ZA".mNDi
j$ZG|(A^'YHL6A1N!n"q$|HID:A3^g
<T?n$U]GF(f.c_xC`[/%!qLm40>{[[
o]3FHL#shWVh|fR|A*&S?B&+VPd^j_
2M%YN0[gTa{7D-kTomx:c^>bgF.w%3
5 row(s) retrieved


Lets generate random lengths of data by combining the functions together


CREATE TABLE TEST_DATA_RAND_LENGTHS AS SELECT DBMS_RANDOM.STRING('P',DBMS_RANDOM.VALUE(1,30)) VALUE FROM DUAL CONNECT BY LEVEL<=50;
SELECT * FROM TEST_DATA_RAND_LENGTHS WHERE ROWNUM <= 5;


VALUE                          LENGTH(VALUE)
------------------------------ --------------------------
}+7ym-LI%DqHq                  13
Oki\!<4)UVDAubJ4Y?P^o          21
7(KFuAVN{v<_,XJefe             19
L-\9B&iV#b%*                   12
z2_|Wk1!q7PxAH2zYb|kMuZ        23


Ok, That is great you say, but what can you really do with that? If you want to see a SQL script that creates some tables and generates a few million rows of data, please click here.
That was just a quick introduction to the random number and character generation that is provided by oracle. Nothing new or innovative there, but something to plant the seed in your memory to grow and be used at a later time.To clone and fill an existing table, Thomas Kyte has an excellent procedure for doing this. Please See generate test data automagically


Zoundry

Well after trying the built in Blogger post editor and the Blogger word plugin, w.blogger and then finally Zoundry. I have decided I will give Zoundry a test for a few posts and see how it works. Sorry to those that might have seen the posts come and go throughout the day, but I was testing, and Blogger doesn't provide a test or development system, so production was the only place to try it out :)

I will rebuild the random data entry I did and see how Zoundry does for posting it.

You can find Zoundry at http://www.zoundry.com/download.html

It does appear to be the best so far today, but they do say it is in beta mode, so I have a feeling the "free" nature of the product may go away.