Home » RDBMS Server » Server Utilities » Re -org of Tablespaces (merged 3)
Re -org of Tablespaces (merged 3) [message #398689] Fri, 17 April 2009 02:37 Go to next message
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 #398699 is a reply to message #398689] Fri, 17 April 2009 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not multipost your questions in several forums.
Please read OraFAQ Forum Guide.

Regards
Michel

[Updated on: Fri, 17 April 2009 03:09]

Report message to a moderator

Re: Re -org of Tablespaces (merged 3) [message #398750 is a reply to message #398699] Fri, 17 April 2009 07:35 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Hello,

What's your question/problem?

Babu
Re: Re -org of Tablespaces (merged 3) [message #398767 is a reply to message #398689] Fri, 17 April 2009 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I am performing Re - org of tablespaces
WHY?
Locally Managed Tablespace do not require any reorganization.

[Updated on: Fri, 17 April 2009 08:42]

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 Go to previous messageGo to next message
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 Go to previous message
BlackSwan
Messages: 25046
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.
Previous Topic: SCN/data doesn't update after the restore
Next Topic: IMP-00010: not a valid export file, header failed verification
Goto Forum:
  


Current Time: Fri Dec 09 06:07:06 CST 2016

Total time taken to generate the page: 0.24553 seconds