Oracle tablespaces

Roger,

Lawson only cares about the tablespace definitions, both in dbdef (GEN) and what is actually available in Lawson when a table is created/dropped.  When accessing data, the location of the object in Oracle is not relevant.  You shouldn't run into any issues until you apply a patch that adds a table or modifies a table such that it needs to be unloaded to disk/dropped and recreated.  If you run into either of these situations and prod_data/prod_index do not exist then the dbreorg will fail.  I would recommend syncing up your lawson dbdef with the actual location of the objects in Oracle and then running a blddbdict followed by a dbreorg -d.  The -d will not actually affect anything in Oracle but at that point your Lawson and Oracle definitions will be in sync.

 

thanks,

Jason Beard

617-548-5568

jabeard3@gmail.com

Jason, good point.

Roger, you might want to make sure that the DBA isn't talking about data files rather than tablespaces.

Hi Jason... I understand what you are talking about and I agree with it....

Instead of synching up Lawson with what Oracle is, can we synch up Oracle to what Lawson is? Meaning, can we create the single tablespaces in Oracle prod_data, prod_index and move the data files in Oracle to those tablespaces? The run the blddbdict and dbreorg -d?

But still, the question begs... If the database space in Lawson is prod_data, prod_index and the table spaces in Oracle are law_data1, law_data2, etc... then where is the mapping or whatever which connects or links the two? How does Lawson know to connect prod_data, prod_index with law_data1, law_data2, etc....? This is the set up now, and things work fine.

Thanks Jason,

Roger

Roger, I suggest you look at the DBSPACE definitions, stored in the DBSPACE table in GEN. This table maps the DBSPACE name, to the oracle tablespaces. For each DBSPACE, there are two columns, LOCATION that gives the name of the Oracle tablespace for tables, and INDLOCATION which gives the name of the Oracle tablespace used for indexes.

I believe that you can also override this location at the table level by updating rows in the LAW_DBA_TABLE or LAW_DBA_INDEX tables, although I have never had a reason to do so.

Ben, I'm assuming that after making such changes to DBSPACE table that you would have to rebuild dict and dbreog -d. Is this correct?

 

Thanks,

Roger

Probably, but I'm not clear on just what you are changing. If you are actually moving tables from one tablespace to a different one you would probably want to do a dbreorg -G, but if you are just changing the Lawson side to make sure that it is in sync with the way the tables already exist in Oracle then I think -d shouldl do what you want.

Roger,

 

Ben is correct you can assign a dbspace at the productline/data are level or to an individual table.  You can certainly change Oracle rather than Lawson however there may be a reason that your DBA has configured the database this way.  Once your database grows beyond a certain size a single tablespace is not typically preferred by most DBA's there are a million schools of thought on the best way to lay things out.

Regarding your question of how Lawson knows where the tables are today.  When requesting data from a table you connect to a database and perform a select.  The database knows where the object is and returns the data to the calling program.  So, I connect to "prod" and execute "select * from lawson.glsystem"  It doesn't matter what tablespace the lawson object glsystem is defined in, the database simply returns the data. 

Thanks,

Jason Beard

617-548-5568

jabeard3@gmail.com

 

 

Gotcha...

Ok, so, I'm attempting to dump those two tables using sqlplus. I don't see those two tables (LAW_DBA_TABLE, LAW_DBA_INDEX) in GEN or the application productline. select * from law_dba_table; or select * from LAW_DBA_TABLE; results in saying table or view does not exist. select * from GEN.law_dba_table and select * from .law_dba_index says the same thing.

Where are they? I have the master lawson login/password of 'lawson'.

-R

How about the GEN.DBSPACE table?  That will tell you all the DBSPACE's defined in Lawson, the location and indlocation fields should match the Oracle names.   To see if there are spaces assigned directly to tables you can select  from GEN.FILELOC

the law_dba_table/index are optional and may or may not exist on your system

 

thanks,

Jason Beard

617-548-5568

jabeard3@gmail.com

If LAW_DBA_TABLE & LAW_DBA_INDEX do not exist that is fine -- they are entirely optional. If they did exist they would be in the productline schema, not GEN.

GEN.DBSPACE will always exist - dump and look at that. Dump with rngdbdump if you are on 803 environment, or you can use sqlplus if this is LSF.

LAW_DBA_TABLE and LAW_DBA_INDEX don't exist.

DBSPACE exists. It has rows which point to a single table and index space.. Everything works fine as is.. can run programs and queries, etc.

Oracle has 14 different tables spaces , 7 for tables and 7 for indices

Again, how does Lawson know or point the single table and index spaces to the Oracle 14 table spaces? Where does it "know" to do that? Where is the mapping? Where is the "magic" here? hah.

-R

 

Roger -
please see my note above
Regarding your question of how Lawson knows where the tables are today. When requesting data from a table you connect to a database and perform a select. The database knows where the object is and returns the data to the calling program. So, I connect to "prod" and execute "select * from lawson.glsystem" It doesn't matter what tablespace the lawson object glsystem is defined in, the database simply returns the data.
It doesn't need to "know" unless it is trying to create an object.

Roger,

If you really want to know how the product line connects to its own database then see the
ORACLE (all caps) file in $LAWDIR/apps/. The ORACLE_SID points to the schema.

Do the tablespace & indexspace listed in the DBSPACE table exist in Oracle? I hope so or you will have problems applying patches. My guess is that sometime after the tables were created, a DBA used Oracle (not Lawson) tools to move the tables to new tablespaces. This works fine as Jason said - when you are just reading or updating a table then you don't need to know where it is stored. However, when you are installing a patch that makes certain kinds of table definition changes, Lawson may try to unload and recreate the table. When this happens, Lawson will try to create the table in the tablespace it should be in according to the Lawson definitions. if DBSPACE is pointing to a valid tablespace, this means that the table will end up getting moved from one tablespace to another, which is usually not a problem. But if DBSPACE points to a tablespace that is not usable or is full, then the patch install will fail and you will have to figure out how to recover. It's better to avoid this problem by getting things straight in advance.

Perhaps what you should have is 7 different DBSPACEs, and map the Lawson tables to the appropriate DBSPACE.

Have you done a copy from one database or environment to another to create this setup? When we do a productline copy to a new environment, we have to add in the DBSPACE definitions to match the source environment.