ORA-1652: Index creation error

[Former Member]

At the time of table re-organisation, Index creation got aborted for the secondary index creation. then we have continued our activity leaving the index creation due to short of System downtime.

Again we have run the sql statment when the sap system is up, then it has thrown an error:

ORA-1652 unable to extend temp segment by 128 in tablespace PSAPTEMP.

In the current system, we are not ready to increase the size of datafile or resizing.where it leads to the increase the size of database.

Here we have option to create a new temp tablespace, will delete after index creation, but while doing this how do we point the new tablespace which is created for this activity.

SAP 4.6c oracle 9 sun solaris.

Kindly advise what can be done. reply ASAP

[Former Member · Accepted solution]

If you connect with SYSDBA you have to assign the new temporary tablespace to SYS:

ALTER USER SYS TEMPORARY TABLESPACE PSAPTEMP1;

[Former Member · Accepted solution]

Thankyou Very much for your help.

It is now started using the new tablespace with the user: SYS.

Really greatful to you.

[Former Member · Accepted solution]

Martin,

Could you please suggest me the possible technical ways to find the causes/reasons for the delay in Offline backup.

Our backup usually runs for the span of 21 hours. but from past two times it is consuming 22 hours plus. If you provide me your email id, I will send the two normal & delayed log files.

Thanks,

[Former Member · Accepted solution]

Please understand that I try to provide some quick answers for problems reported in SDN, but that I prefer not spend significant amounts of time to analyze different log files.

Nevertheless I suppose that note 842240 can be a good starting point for your analysis. Additionally you should check if the performance decrease started after you have activated direct I/O (either via filesystem mount option or via Oracle parameter FILESYSTEMIO_OPTIONS = SETALL).

[former_member204746 · Accepted solution]

1. post your logs here

2. going from 21 hours to 22+ hours is not really dramatic, just a 10% increase. if you add datafiles, this can be normal.

3. 21 hours for a backup is ALREADY to high! go get yourself a real backup solution and backup using multiple tape drives.

[Former Member · Accepted solution]

Hi,

We have done the new hardware migration for testing system(PSS) and PSS refresh from the production data. CheckDB job has been scheduled from DB13. here the job is getting failed. still it is referring the path of production "Function stat() failed for '/oracle/PRD/sapdata7'".

Could you please advise me in which file I need to make the changes so that this job executes successfully.

Thanks,

[Former Member]

I have written an answer in the other SDN thread. I think this thread here can be closed.

[Former Member]

In the CREATE statement you have written PSAPTEMP and not PSAPEMP1. Is this just a typo? To be on the safe side make sure that you log on with the SAPPRD user AFTER having assigned to new PSAPTEMP1 tablespace to this user.

[Former Member]

Martin,

We have logged into database server with the user: ORAPRD, there into SQL

with the command:

SQLPLUS '/ as sysdba'

we dont know the user id and pwd for the user : SAPPRD.

then we deleted this LMTS/T tablespace and trying to create the tablespace with the DMTS/P.

Martin if you could please give me your contact number so that you can advise the steps to go ahead.

please help us frm this.

[Former Member]

Martin, this is my email id: mutthu82@gmail.com and

contact number: 0 98861 45002.

I am from bangalore, India.

please revert back. its very urgent

[Former Member]

Martin,

Any idea, Index creation is still using the old tablespace again.

inspite of created the new tablespace it is not using.

please advise

[Former Member]

Please use the other SDN call to follow up on this problem. I would prefer to communicate via that other thread and not via mail.

[Former Member]

To be clean I would recommend to switch back to LOGGING, too (although NOLOGGING has only an influence on index builds and rebuilds):

ALTER INDEX "<index_name> LOGGING;

[Former Member]

Martin,

Hi,

we have created the temporary tablespace PSAPTEMP1 for the index creation.

CREATE TEMPORARY TABLESPACE "PSAPTEMP" TEMPFILE '/archive/temp' SIZE 51200M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10240K;

ALTER USER PSAPPRD TEMPORARY TABLESPACE PSAPTEMP1

but when we use the 2nd query to use for the index creation, but for the index creation it is using the old tablespace PSAPTEMP.

could any one suggest how do we make use of the newly created tablespace for the index creation.

This we are doing live PRD system with the down time.

My number: is 0 98861 45002 else you give me your number I will call you.

please do the needful.

Many Thanks,

[Former Member]

Answer given was quite appriciable.

[Former Member]

The order of the arguments doesn't matter, so your query is fine. Also the parallelism degree should be fine (you should run the CREATE during a time of low system activity of course).

[Former Member]

I should Thankyou a lot for your valuable suggestions.

[Former Member]

Martin,

After the index is created reset the parallel degree of the index using the following command:

<b> alter index <index name> parallel 1;</b>

Do we need to change/alter the option NOLOGGING command back to LOGGING.

please advise.

Thanks,

[Former Member]

NOLOGGING may be used if you are aware about the recovery restrictions. You may also use the PARALLEL option. See note 334224 for more details.

If the tablespace is dictionary managed, I would also recommend you to choose a larger value for NEXT in order to limit the number of extents.

[Former Member]

Martin,

in the query, how these sequence arrive..

do we need to consider the nologging first or parallel or any sequence.

for parallel do we need to use (n-1) suppose if we have 4 cpu the parallel value should be 3..?

PARALLEL 3

NOLOGGING

here is my query: please verify and correct it.

CREATE INDEX "SAPPRD"."COEP~1" ON "SAPPRD"."COEP" ("MANDT", "LEDNR", "OBJNR",

"GJAHR", "WRTTP", "VERSN", "KSTAR", "HRKFT", "PERIO", "VRGNG", "PAROB", "USPOB",

"VBUND", "PARGB", "BEKNZ", "TWAER")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

nologging

parallel 4

TABLESPACE "PSAPPRD" ;

Thanks,

[Former Member]

I would not worry too much about the normal SAP processes. Unless you use BW functionality the amount of PSAPTEMP accesses is rather small. So it should not matter if the normal SAP processes use the temporary temporary tablespace or not.

[Former Member]

Martin,

we dont have the BW component in our system, we have HR & FICO modules.

But I am totally confused how to go about this issue.

[Former Member]

If anything is still not clear, please let me know your concerns and questions.

[Former Member]

HI,

Could you please provide me the step by step procedure to go ahead with this issue. that will be great helpful for me.

Many Thanks,

[Former Member]

I would suggest the following steps:

- Create the new temporary tablespace (e.g. PSAPTEMP_NEW)

- Set the temporary tablespace for the SAP user to the new tablespace:

ALTER USER <sapuser> TEMPORARY TABLESPACE PSAPTEMP_NEW;

- Perform the index creation

- Set the temporary tablespace for the SAP user back to PSAPTEMP:

ALTER USER <sapuser> TEMPORARY TABLESPACE PSAPTEMP;

- Drop PSAPTEMP_NEW

[Former Member]

Many Thanks Martin for the suggestions,

You mentioned the SAP User: is the user name is: <b>SYS</b> in our SAP system or do we have a separate user at oracle level which will used to create the Index.

[Former Member]

See my SAP note 562863 that decribes the different Oracle users in SAP environments.

[Former Member]

Martin,

These are the user's, please advise which user from this list.

SQL> SELECT username, temporary_tablespace FROM dba_users;

USERNAME TEMPORARY_TABLESPACE

-

---

-

---

SYS PSAPTEMP

SYSTEM PSAPTEMP

OUTLN SYSTEM

OPS$PRDADM PSAPTEMP

SAPPRD PSAPTEMP

SC_FM SYSTEM

DBSNMP SYSTEM

OPS$ORAPRD SYSTEM

SAPR3 SYSTEM

which user we can use to go ahead with this transaction

"ALTER USER <sapuser> TEMPORARY TABLESPACE PSAPTEMP_NEW;"

Thanks,

[Former Member]

This depends on the way how you perform the CREATE INDEX operation. Simply take the user under which you execute the CREATE INDEX.

[Former Member]

Martin,

We have discussed with the team like,

1. Create new temporary tablespace with desired Size which should be minimum 25GB

CREATE temporary tablespace PSAPTEMP1......

2. If the original tablespace is a default temporary tablespace, set the new tablespace as default temporary tablespace in the database.

SQL> alter database default temporary tablespace PSAPTEMP1;

3. Perform the index creation

4. Make the old tablespace PSAPTEMP as the default temporary tablespace.

SQL> alter database default temporary tablespace PSAPTEMP

5. Drop the new tablespace.

SQL> drop tablespace temp including contents.

Here I have a question, while switching the default temporary tablespace from PSAPTEMP to the much bigger new PSAPTEMP1Tablespace whether this will affect the running transaction.

Any impact on switching the tablespace online..?

We are performing this activity in the online system(running sap system)

Thanks,

[Former Member]

Sorry, but switching the default temporary tablespace rather than the user specific temporary tablespace doesn't make much sense in my opinion because the user specific tablespace has higher priority.

[Former Member]

Martin sorry to trouble you, which is the user specific temporary tablespace who are talking about..?

we are trying to switch the default temporary tablespace to the newly created temporary tablespace.

please brief about this..

Thanks,

[Former Member]

The user specific temporary tablespace is the one you already determined above with the DBA_USERS query. The default temporary tablespace would only take effect if no user specific tablespace is set. See the Oracle documentation for more details.

[Former Member]

Martin,

Thanks for your reply, I have given you the points.

Many Thanks

[Former Member]

The default temporary tablespace is a global setting that is overwritten by user specific settings. The user specific tablespace (visible in DBA_USERS) can be changed based on the ALTER USER commands I already sent you.

[Former Member]

Martin, many thanks for ur suggestions we are implementing this solution.

<b>Here we Consider Creating Indexes with NOLOGGING</b>

by choosing this option we will:

Space is saved in the redo log files.

The time it takes to create the index is decreased.

Performance improves for parallel creation of large indexes.

below I have will give my index creation query please amend the query where the NOLOGGING will be used.

please reply ASAP.

"CREATE INDEX "SAPPRD"."COEP~1" ON "SAPPRD"."COEP" ("MANDT", "LEDNR", "OBJNR",

"GJAHR", "WRTTP", "VERSN", "KSTAR", "HRKFT", "PERIO", "VRGNG", "PAROB", "USPOB",

"VBUND", "PARGB", "BEKNZ", "TWAER")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "PSAPPRD" ;"

Many Thanks,

[Former Member]

A different temporary tablespace can be assigned to a schema user with the following command:

alter user <schemauser> temporary tablespace <new_temp_tablespace>;

<schemauser> is typically SAPR3 or SAP<sid>.

[Former Member]

Hi,

Could you please brief for your answer, after creating new temp tablespace how to you direct to use the new created tablespace. this we are doing online, then the current users of sap will try to use the new tablespace while running this activity.

is there a procedure to use this tablespace only for this activity and the existing tablesapce can be used by the sap users.

please breif.

Thanks