Here is a quick way to create a copy of table:

SQL Server:
SELECT * INTO {NEWTABLENAME} FROM {ORIGINALTABLENAME};

Oracle:
CREATE TABLE {NEWTABLENAME} AS (SELECT * FROM {ORIGINALTABLENAME});

I was working on a maintenance pack application to a finance environment the other day and ran into a problem when doing the alter builds.  When building a table I received the ORA-02221 error: invalid maxtents storage option value.  It would appear that at some time in the past an upgrade occurred where the setindex and settable SQR’s ran.  These SQR’s will set the maxextents override incorrectly in a specific situation. The situation is if the USER_INDEXES.MAX_EXTENTS is null in the database.

The end result is  an override of zero being stored in PSIDXDDLPARM and PSRECDDLPARM.  The DDL later generated by Application Designer, to create tables and indexes,  will include a “MAXEXTENTS 0” clause for the index.  This is an illegal clause for the Oracle database platform.  The MAXEXTENTS parameter should be unlimited and not set to zero.

Fix 1:

Delete from PSRECDDLPARM where PARMNAME = ‘MAXEXT’ AND PARMVALUE = 0;
Delete from PSIDXDDLPARM where PARMNAME = ‘MAXEXT’ AND PARMVALUE = 0;

Fix 2: Alternately, run the following sql:

UPDATE PSRECDDLPARM SET PARMVALUE = 2135468 WHERE PARMVALUE = 0;
UPDATE PSIDXDDLPARM SET PARMVALUE = 2135468 WHERE PARMVALUE = 0;

I was recently trying to test a system for a client that was trying to do some year end testing. In order to do this testing in the past they use to simply use the Oracle Database function “FIXED_DATE” to future date their database. When you use the FIXED_DATE feature it effects the SYSDATE variable and returns a date that is not the date of the operating system.

Unfortunately, with the more current releases of PeopleSoft, they have changed several of their system calls from SYSDATE to SYSTIMESTAMP. For some reason SYSTIMESTAMP is NOT effected by the FIXED_DATE override parameter, and from everything I have read there is no override parameter for changing the SYSTIMESTAMP. This leads to the problem of how to make future date testing work within PeopleSoft.

The only real solution is to future date the operating system. However, there are some real concerns that can come with this idea. I personally would highly recommend that you clone the server you are working on and use the cloned machine for this future date testing.

My testing system is stored on RackSpace servers, and when I made the cloned server and tried to change the OS date, it said it changed it, but when you looked again, it was still the current date/time. It turned out that the RackSpace system uses XEN on the RHEL OS to keep the server times in sync. I found the best way to change this was to add the sysctl parameter:

xen/independent_wallclock = 1

Once, I rebooted the server, I had no issue controlling the OS clock.

I found this video that shows you have to create a database when ASM is installed.

YouTube Video

I found that when I manually created the Oracle Database, that because the ASM dynamically names everything, I simply removed the directory & file name reference and replaced it with the +ASM group where I wanted the datafile to be located.  If you look at the ptddl script for a PeopleTools database or the hcddl script for an HR database you will see how it will say the directory+file name:

‘/oracle/oradata/dbfiles/tablespace1.dbf’ is replaced with:

‘+ASMHRORPTGROUP1’ depending on what you named it.

This little SQL is a sweet little piece of code I found somewhere in my travels.  It will find duplicate rows of data based on a keyset, however, this only works in Oracle.  I will try to find a similar piece for SQL Server, but for now here we go:

select * from table_name
where rowid in (select min(rowid) from table_name group by key_values having count(*) > 1);

delete from table_name where rowid in (select min(rowid) from table_name group by key_values having count(*) > 1);