Naming Constraints and Moving Tables

Oracle 9i
Hi i hv started on Oracle9.2 running on win2k. A consultant we hired wrote all the shop floor programs and define all the tables, indexes, constraints. He also define for us the database config (init.ora). Because he is more into programming, i need to "clean-up" his act 1)Move all my uses even SYS to use temp and default tablespaces rather than SYSTEM tablespace (that is, redefine users with defaulted (eg, userstb) and temp tablespaces (eg, temptb) 2)Move all his indexes to use indxtb01 tablespace rather than SYSTEM 3) Move all his tables to use udatatb01 tablespace rather than SYSTEM 4) List all constraints and rename all with "SYS_C####" to a meaningful name Q1: How to accomplish all the above? First, i need to list all my users and their tablespaces allocated Q2: Are there any pitfalls if i moved his tables to another tablespace? (eg, i hv to move his indexes first, then moved his tables?) Are there any constraints that will be impacted if i moved tables to another tablespace? Q3: If i renamed constraints, will PL/SQL procedures, C++ programs need to be recompiled? All the consultant's programs are written in C++ and nobody in my dept knows the language and we are not given the source code

Answer Wiki

Thanks. We'll let you know when a new response is added.

What you’re asking is easy. You can do it with simple queries with SQL*Plus, or by using a tool such as OEM, etc. Here are a few thoughts:
1)Changing user’s tablespaces. Select from dba_users to determine the current assignments. Use “alter user …” to change assignments. Consider any quotas.
2)Moving indexes – Select from dba_indexes to determine current tablespaces. Use dba_tablespaces to find out about ts defaults. Use “alter index rebuild” to accomplish the move. Consider the schema owner’s quotas, the allocation methods, tablespace definitions.

4) Constraint naming – Use DBA_Constraints to get the constraint types and names, DBA_Cons_Columns to get more details, and “alter table rename constraint” to make the changes. You can pick the names manually, or build them in the select statement using your standards.

Other thoughts:

You only need to be concerned about PL/SQL, etc. code if that code is sensitive to constraint names in errors (SQLERRM), manages space, etc. This is unlikely, but it has been done!

You could use a C++ disassembler for a shot at getting the source, but you need to think about legal issues re: ownership of the source.

Tracing normal execution would give you an exhaustive list of everything the app is doing.

You would definitely want to do a full test before changing production.

Making a few changes at a time would uncover smaller quantities of issues which you could recover from more easily.

Prepare backout scripts for everything before impelemnting. It takes more time overall, but if you need to recover, its so much more convenient.

In summary, this is the sort of thing that could be very simple, but having an experienced DBA/Developer look at it would be good insurance.

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Mayleong
    How do i move the tables? Do i have to move the datafiles or is there a command to ALTER DATABASE/ALTER TABLE to point to another tablespace Right now, the tables and indexes all occupied the USER tablespace, and i dont like it due to possible performance/contention issues. There are 2 options: 1) I moved the indexes to INDX tablespace and left the tables in USERS tablespace 2) Moved the indexes to INDX tablespace and move the tables to DATA tablespace In moving tables and indexes, is there any considerations i have to beware? q1: Do i hv to move the tables first, then move my indexes with ALTER INDEX REBUILD TABLESPACE .... statement, so that the rebuild will point to the table in the correct tablespace? q2: If i moved my indexes to INDX tablespace using the ALTER TABLE REBUILD, followed by tables, do i need to rebuild the indexes again to ensure that they are valid? PS: Thanks a lot! I have defined my users with USERS (default) and TEMP (temporary) tablespaces, so that they can create objects in those tablespaces, instead of the defaulted SYSTEM tablespace
    0 pointsBadges:
  • Lastlaugh
    1. select username,DEFAULT_TABLESPACE TEMPORARY_TABLESPACE from dba_users order by username; --Here is a script to generate a script to change them. I would not change SYS, OUTLN, or DBSNMP. Also note, that SYSTEM and PERFSTAT and other database tools users should default to a tools tablespace if available. set pagesize 0 set feedback off set linesize 80 select 'alter user '||username ||chr(10)||' default tablespace userstb' ||chr(10)||' temporary tablespace temptb' ||chr(10)||' quota on usertb 200K;' a_line from dba_users where username not in ('SYS','SYSTEM','OUTLN','PERFSTAT','DBSNMP') union select 'alter user '||username ||chr(10)||' default tablespace tooltb' ||chr(10)||' temporary tablespace temptb' ||chr(10)||' quota on tooltb unlimited;' a_line from dba_users where username in ('SYSTEM','PERFSTAT') order by 1; set feedback on --A 9i new feature that will prevent newlly created users from using the the system tablespace as their temporary tablespace alter database default temporary tablespace temptb; 2. alter index owner.index_name rebuild tablespace indxtb01; --Available from 7.3.?. I would perform this after the moving of tables since all indexes will become invalid for a moved table and will need to be rebuilt. select owner||'.'||index_name from dba_indexes where status != 'VALID'; --The above statement will show any indexes that still need to be rebuilt. 3. alter table owner.table_name move tablespace udatatb01; -- Oracle takes care of triggers, constraints etc, but all indexes become invalid and statistics will need to be re-collected on tables and indexes. 4. --This is possible to do by dropping and recreating the constraint, but because there are probably foreign keys dependent on the primary keys, it will be difficult without a tool and may not be worth the trouble until a table change is needed that requires recreating the constraint. --C programs will not be affected, but PL/SQL programs can be affected by many changes made by a schema owner or DBA. I always see if there is anything that may need recompiled before and after any database maintenance. I use this script. column a_line format a80 word_wrap select 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)||' '||owner||'.'||object_name ||' compile'||decode(object_type,'PACKAGE BODY',' BODY;',';') a_line from dba_objects where status = 'INVALID' order by decode(object_type,'TYPE',1,'VIEW',2,'FUNCTION',3,'PACKAGE BODY',4,'PACKAGE',5,6),object_type,owner,object_name;
    0 pointsBadges:
  • Mljsher
    There's no need to mess around with any datafiles. Both of the two options you mention are viable. To move the indexes, you'll have to create the INDX tablespace and make sure that the schema which owns the indexes has sufficient quotas on that tablespace. Then you can do an 'alter index (index name) rebuild tablespace indx' for each index you want to move. That can be scripted using a SQL query. It's ok to leave the tabels in the users tablespace, there's no harm there. If you wish to move the tables, you can do a 'alter table (table_name) move tablespace (new tablespace name)' for each table. If you do decide to move the tables out of the users tablespace, move them first, and then move the indexes.
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: