Home » SQL & PL/SQL » SQL & PL/SQL » SQL Replace Question (Oracle 10g SQL)
SQL Replace Question [message #439801] Tue, 19 January 2010 08:23 Go to next message
baskaranprasad
Messages: 13
Registered: August 2006
Location: Bangalore
Junior Member
Hi,

I need to know how can I replace a set of numeric values in a text(say clob text) with a standard value.

My problem is that, during import of the site dumps in dev environments,
I need to prepare the INDEXFILE and need to replace all the tablespace related references
in the indexfile.Also I need to replace the storage settings in the indexfile with consitent values.
For Example,My indexfile consistes of table/index creation statements as below.
REM  CREATE TABLE "TBSONSITE"."APP_USER" (
REM  "USER_ID" VARCHAR2(12) NOT NULL ENABLE,  
REM  "USER_NAME" VARCHAR2(35), "USER_PASSWORD" VARCHAR2(32), 
REM  "STATUS_CHANGED_ON" DATE, "TILL_ALLOWED" CHAR(1), "USER_MOBILE" VARCHAR2(50), "USER_FAX" 
REM  VARCHAR2(50), "USER_PAGER" VARCHAR2(50), "EXT_USER_REF" VARCHAR2(20)) 
REM  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 1048576 
REM  NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL 
REM  DEFAULT) TABLESPACE "TBSONSITE" LOGGING NOCOMPRESS ;

REM  ALTER TABLE "TBSONSITE"."APP_USER" ADD CONSTRAINT "PK01_APP_USER" 
REM  PRIMARY KEY ("USER_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 
REM  255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 
REM  FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TBSONSITE" 
REM  LOGGING ENABLE ;

I want to replace
INITIAL 1048576 NEXT 1048576 to  INITIAL 65536 NEXT 65536

Here the initial/next extent values are not same and it has 20 to 30 different values.
Whereas the target initial/next extent value is constant i.e 65536
So is there any easiest way to replace the pattern ,
INITIAL <ANY NUMBER> NEXT <ANY NUMBER> to INITIAL 65536 NEXT 65536

either using REPLACE() or using Regular Expressions.
Thanks in advance.

Regards,
B.Prasad

[Updated on: Tue, 19 January 2010 12:22] by Moderator

Report message to a moderator

Re: SQL Replace Question [message #439804 is a reply to message #439801] Tue, 19 January 2010 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not a SQL or PL/SQL question, not even an Oracle one but a scripting issue and then it depends on your OS and shell which you didn't post.

Regards
Michel
Re: SQL Replace Question [message #439809 is a reply to message #439801] Tue, 19 January 2010 09:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If I were you, I'd set up the tablespaces on the system that you're going to import into to as Locally Managed, and then the import should just ignore all the initial extent sizings and do it automatically.

Or, if the user doing the import doesn't have access to any tablespaces with the required names, then I believe that the tables will be loaded into that users default tablespace, using the default extent sizes.

Or, if you feel like using ImpDB, you can use the METADATA_TRANSFORM that gives you some control over remapping of storage parameters during an import.

Failing that, you're probably going to have to use something like SED (if you're on unix/linux) to rewrite the import file.

icon6.gif  Re: SQL Replace Question [message #439824 is a reply to message #439809] Tue, 19 January 2010 10:01 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
OP does not mention what version of Oracle, but assuming it's 10g or greater (11g), the INITIAL parameter is not ignored for locally managed tablespaces, but rather used to determine the initial size of the segment depending on the type of local management: AUTOALLOCATE or UNIFORM.

For AUTOALLOCATE extent management, Oracle uses the INITIAL setting to optimize the number of extents allocated. Extents of 64K, 1M, 8M, and 64M can be allocated.

For UNIFORM extent management, the number of extents is determined from initial segment size and the uniform extent size specified at tablespace creation time.

Personnaly I would not be concerned with the "INITIAL/NEXT" extent values. Any over-allocation (if there is) can be easyly remedied once the data is loaded and the NEXT extent can also be easyly altered.

[Updated on: Wed, 20 January 2010 08:46] by Moderator

Report message to a moderator

Re: SQL Replace Question [message #439844 is a reply to message #439824] Tue, 19 January 2010 11:53 Go to previous messageGo to next message
baskaranprasad
Messages: 13
Registered: August 2006
Location: Bangalore
Junior Member
Hi,

Thanks for your valuble time and quick respose.

Our database is in 11g (Unix Server) and please be noted that I do not have access to the Oracle/Unix servers, sys or system users and only I can do cleanup the schema objects and do the import using IMP utility from windows XP machaine.

Since I do not have access to system tablespaces etc, I am replacing the storage parameters with text editors after getting the output of the below query from site.
select distinct initial_extent from user_segments  
where initial_extent <> 65536 or next_extent <> 65536
union
select distinct next_extent from user_segments  
where initial_extent <> 65536 or next_extent <> 65536


The initial/next extents settings at database level set as 65536 (as per the standards set by DBA Team) and the site dump have inconsitent storage settings, I have to follow this method.

Please suggest incase if you have any other better way to do this.Also please let me know how I can user regular expressions to replace the standard patterns as I mentioned earlier.

Thanks,
B.Prasad

icon6.gif  Re: SQL Replace Question [message #439866 is a reply to message #439844] Tue, 19 January 2010 15:13 Go to previous message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
If you have permissions to load (import) data, then you have 'CREATE/ALTER TABLE' privileges on that schema and can change the values of the NEXT parameter to whatever you need.
INITIAL parameter is only used at the creation of the table.

IF you have access to the source database (the origin of the export) use SQL Developer/TOAD or some other utlility to export the DDL for those table WITHOUT the storage parameters and then pre-create those tables on the target database.


[Updated on: Wed, 20 January 2010 00:51] by Moderator

Report message to a moderator

Previous Topic: UPDATE GLOBAL INDEXES vs. UPDATE INDEXES
Next Topic: display the first two node names from a variable
Goto Forum:
  


Current Time: Sun Dec 11 02:18:33 CST 2016

Total time taken to generate the page: 0.04274 seconds