| Re -org of Tablespaces (merged 3) [message #398689] |
Fri, 17 April 2009 02:37  |
lukas_pise@yahoo.ie
Messages: 2
Registered: April 2009
|
Junior Member |
|
|
Hello
I am performing Re - org of tablespaces on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
I have performed the necessary checks on the tablespace to be Re -Organised.
For tables having long & long raw datatype , I am using Datapump expdp & impdp.
Using Datapump Expdp have taken export of tablespace to be re - organised.
While Doing Import using impdp I would using option TABLE_EXISTS_ACTION=REPLACE
As I am using TABLE_EXISTS_ACTION=REPLACE do I need worry about Check constraints & dependencies.
On the Schema residing on the tablespace , I found Check constraints & dependencies.
Step 1
SQL> SELECT c.constraint_type, count(*) FROM dba_constraints c, dba_cons_columns cc
WHERE c.owner='TOPAS' AND c.owner = cc.owner
AND c.constraint_name = cc.constraint_name
group by c.constraint_type;
C COUNT(*)
- ----------
R 26
U 3
P 658
C 738
Step 2
SQL> set linesize 200
SQL> set pagesize 150
SQL> SELECT c.constraint_name AS "Foreign Key",
2 p.constraint_name AS "Referenced Key",
3 p.constraint_type,
4 p.owner,
5 p.table_name
6 FROM dba_constraints c, dba_constraints p
7 wHERE c.owner='TOPAS'
8 AND c.constraint_type='R'
9 AND c.r_owner=p.owner
10 AND c.r_constraint_name = p.constraint_name;
Foreign Key Referenced Key C OWNER TABLE_NAME
------------------------------ ------------------------------ - ------------------------------ ------------------------------
FK_SEVID44 SYS_C007066 P TOPAS SEVERITY
FK_CATID44 SYS_C007064 P TOPAS CATEGORY
FK_SEVID SYS_C007066 P TOPAS SEVERITY
FK_CATID SYS_C007064 P TOPAS CATEGORY
FK_GGO_ATTRIBUTE XPKGGO_TEMPLATE P TOPAS GGO_TEMPLATE
FK_INFOEVENT_SEVID SYS_C007066 P TOPAS SEVERITY
FK_INFOEVENT_CATID SYS_C007064 P TOPAS CATEGORY
FKDELNELR XPKFEATURELICENSE P TOPAS FEATURELICENSE
FKFLICENSEREVOCATION XPKFEATURELICENSE P TOPAS FEATURELICENSE
FK_GGO_OBJECT_INST XPKGGO_TEMPLATE P TOPAS GGO_TEMPLATE
FKGROUP_OP_WORKFLOW XPKGROUP_OP_WORKFLOW P TOPAS GROUP_OP_WORKFLOW
FKGROUP_OP_GROUP_OP_NODES XPKGROUP_OPERATION P TOPAS GROUP_OPERATION
FKGROUP_OP_NODES_NODE_STAT_LOG XPKGROUP_OP_NODES P TOPAS GROUP_OP_NODES
FKGROUP_OP_OPERATION_ATTRIBUTE XPKGROUP_OPERATION P TOPAS GROUP_OPERATION
FKGROUP_OP_OBJECT_INSTANCE XPKGROUP_OPERATION P TOPAS GROUP_OPERATION
FKMASTERLICENSE XPKMASTERLICENSE P TOPAS MASTERLICENSE
FKFEATURELICENSE XPKFEATURELICENSE P TOPAS FEATURELICENSE
FK_FILTERMAP PK_FILTER P TOPAS FILTER
FK_RULEMAP PK_FILTER P TOPAS FILTER
FK_MAPSEV SYS_C007066 P TOPAS SEVERITY
FK_MAPCAT SYS_C007064 P TOPAS CATEGORY
FK_ATTRMAP SYS_C007067 P TOPAS SEV_MAPPING
22 rows selected.
Regards
Lukas
[Updated on: Fri, 17 April 2009 03:08] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Re -org of Tablespaces (merged 3) [message #398776 is a reply to message #398767] |
Fri, 17 April 2009 09:02  |
lukas_pise@yahoo.ie
Messages: 2
Registered: April 2009
|
Junior Member |
|
|
Ahoj
As a result of update and delete operations , lot of segments have become fragmented.
Instead of Segment Advisor , we preferred to use Datapump utility for export & import operation to reclaim unused space.
As TABLE_EXISTS_ACTION=REPLACE - Existing tables will be dropped, recreated and then loaded from the source
All I need to know do i need to consider constraints & its dependencies.
Is there any need to disable the constraints.
Do we need to consider Constraints in this case.
Dekugi
Lukas
|
|
|
|
| Re: Re -org of Tablespaces (merged 3) [message #398782 is a reply to message #398689] |
Fri, 17 April 2009 09:18 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>As a result of update and delete operations , lot of segments have become fragmented.
Based upon which metric & what value do you conclude "lot of segments have become fragmented."?
dependencies.
>Is there any need to disable the constraints.
It depends upon the type of constraint.
What happens to application availability while this reorg is happening?
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
