ORA-01691 (unable to extend lob segment) even though my tablespace has AUTOEXTEND on
I am getting an error:
ORA-01691: unable to extend lob segment ABC.SYS_LOB0000167347C00131$$ by 1024 in tablespace XYZ
But this is very strange to me because the tablespace has AUTOEXTEND on. What is the likely reason that this happens and how can I overcome it?
Thanks.
oracle tablespaces
asked Sep 3 '16 at 13:25
Balkrishna RawoolBalkrishna Rawool
111113
add a comment |
I am getting an error:
ORA-01691: unable to extend lob segment ABC.SYS_LOB0000167347C00131$$ by 1024 in tablespace XYZ
But this is very strange to me because the tablespace has AUTOEXTEND on. What is the likely reason that this happens and how can I overcome it?
Thanks.
oracle tablespaces
asked Sep 3 '16 at 13:25
Balkrishna RawoolBalkrishna Rawool
111113
If that is a small file tablespace, maybe it has reached the 32GB limit?
– a_horse_with_no_name
Sep 3 '16 at 14:37
Yes, it is a small file tablespace. And it is very much possible that it has reached 32GB limit. What options do I have now?
– Balkrishna Rawool
Sep 3 '16 at 15:04
2
Add data files.
– tesla747
Sep 3 '16 at 15:58
thanks tesla747 and @a_horse_with_no_name. I'll try that option.
– Balkrishna Rawool
Sep 4 '16 at 20:10
add a comment |
I am getting an error:
ORA-01691: unable to extend lob segment ABC.SYS_LOB0000167347C00131$$ by 1024 in tablespace XYZ
But this is very strange to me because the tablespace has AUTOEXTEND on. What is the likely reason that this happens and how can I overcome it?
Thanks.
oracle tablespaces
asked Sep 3 '16 at 13:25
Balkrishna RawoolBalkrishna Rawool
111113
I am getting an error:
ORA-01691: unable to extend lob segment ABC.SYS_LOB0000167347C00131$$ by 1024 in tablespace XYZ
But this is very strange to me because the tablespace has AUTOEXTEND on. What is the likely reason that this happens and how can I overcome it?
Thanks.
oracle tablespaces
oracle tablespaces
asked Sep 3 '16 at 13:25
Balkrishna RawoolBalkrishna Rawool
111113
asked Sep 3 '16 at 13:25
Balkrishna RawoolBalkrishna Rawool
111113
asked Sep 3 '16 at 13:25
Balkrishna RawoolBalkrishna Rawool
111113
asked Sep 3 '16 at 13:25
Balkrishna RawoolBalkrishna Rawool
111113
asked Sep 3 '16 at 13:25
Balkrishna RawoolBalkrishna Rawool
111113
111113
If that is a small file tablespace, maybe it has reached the 32GB limit?
– a_horse_with_no_name
Sep 3 '16 at 14:37
Yes, it is a small file tablespace. And it is very much possible that it has reached 32GB limit. What options do I have now?
– Balkrishna Rawool
Sep 3 '16 at 15:04
2
Add data files.
– tesla747
Sep 3 '16 at 15:58
thanks tesla747 and @a_horse_with_no_name. I'll try that option.
– Balkrishna Rawool
Sep 4 '16 at 20:10
add a comment |
If that is a small file tablespace, maybe it has reached the 32GB limit?
– a_horse_with_no_name
Sep 3 '16 at 14:37
Yes, it is a small file tablespace. And it is very much possible that it has reached 32GB limit. What options do I have now?
– Balkrishna Rawool
Sep 3 '16 at 15:04
2
Add data files.
– tesla747
Sep 3 '16 at 15:58
thanks tesla747 and @a_horse_with_no_name. I'll try that option.
– Balkrishna Rawool
Sep 4 '16 at 20:10
If that is a small file tablespace, maybe it has reached the 32GB limit?
– a_horse_with_no_name
Sep 3 '16 at 14:37
If that is a small file tablespace, maybe it has reached the 32GB limit?
– a_horse_with_no_name
Sep 3 '16 at 14:37
Yes, it is a small file tablespace. And it is very much possible that it has reached 32GB limit. What options do I have now?
– Balkrishna Rawool
Sep 3 '16 at 15:04
Yes, it is a small file tablespace. And it is very much possible that it has reached 32GB limit. What options do I have now?
– Balkrishna Rawool
Sep 3 '16 at 15:04
2
2
Add data files.
– tesla747
Sep 3 '16 at 15:58
Add data files.
– tesla747
Sep 3 '16 at 15:58
thanks tesla747 and @a_horse_with_no_name. I'll try that option.
– Balkrishna Rawool
Sep 4 '16 at 20:10
thanks tesla747 and @a_horse_with_no_name. I'll try that option.
– Balkrishna Rawool
Sep 4 '16 at 20:10
add a comment |
2 Answers
2
active
oldest
votes
Autoextensible datafiles still have a size limit.
select file_id, bytes, maxbytes from dba_data_files where tablespace_name = 'XYZ';
Maxbytes is the maximum size of the datafile, it can not grow beyond that. If bytes = maxbytes, you have two options:
1) increase the maximum size (maxbytes) as (15 is the file_id from above query), for example to 10G:
alter database datafile 15 autoextend on maxsize 20G;
Datafiles have a hard limit for the maximum size, it is 2^22 * block_size for smallfile tablespaces, and 2^32 * block_size for bigfile tablespaces.
If you use a tablespace with 8K block size, then the maximum size of your datafile is 32 GB. If your datafile is already at that size, you can not increase it any further, there comes option number two:
2) add a new datafile to the tablespace, for example:
alter tablespace xyz add datafile '/path/to/datafiles/xyz02.dbf' size 100M autoextend on next 100M maxsize unlimited;
answered Sep 3 '16 at 18:28
Balazs PappBalazs Papp
26.7k2931
Thanks @Balazs Papp. Now that I have better understanding of this, I'll try this option.
– Balkrishna Rawool
Sep 4 '16 at 20:11
add a comment |
Thank u Boss it is a complete answer.........!!
answered 12 mins ago
Mostafiz MItulMostafiz MItul
1
New contributor
Mostafiz MItul is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f148747%2fora-01691-unable-to-extend-lob-segment-even-though-my-tablespace-has-autoexten%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Autoextensible datafiles still have a size limit.
select file_id, bytes, maxbytes from dba_data_files where tablespace_name = 'XYZ';
Maxbytes is the maximum size of the datafile, it can not grow beyond that. If bytes = maxbytes, you have two options:
1) increase the maximum size (maxbytes) as (15 is the file_id from above query), for example to 10G:
alter database datafile 15 autoextend on maxsize 20G;
Datafiles have a hard limit for the maximum size, it is 2^22 * block_size for smallfile tablespaces, and 2^32 * block_size for bigfile tablespaces.
If you use a tablespace with 8K block size, then the maximum size of your datafile is 32 GB. If your datafile is already at that size, you can not increase it any further, there comes option number two:
2) add a new datafile to the tablespace, for example:
alter tablespace xyz add datafile '/path/to/datafiles/xyz02.dbf' size 100M autoextend on next 100M maxsize unlimited;
answered Sep 3 '16 at 18:28
Balazs PappBalazs Papp
26.7k2931
Thanks @Balazs Papp. Now that I have better understanding of this, I'll try this option.
– Balkrishna Rawool
Sep 4 '16 at 20:11
add a comment |
Autoextensible datafiles still have a size limit.
select file_id, bytes, maxbytes from dba_data_files where tablespace_name = 'XYZ';
Maxbytes is the maximum size of the datafile, it can not grow beyond that. If bytes = maxbytes, you have two options:
1) increase the maximum size (maxbytes) as (15 is the file_id from above query), for example to 10G:
alter database datafile 15 autoextend on maxsize 20G;
Datafiles have a hard limit for the maximum size, it is 2^22 * block_size for smallfile tablespaces, and 2^32 * block_size for bigfile tablespaces.
If you use a tablespace with 8K block size, then the maximum size of your datafile is 32 GB. If your datafile is already at that size, you can not increase it any further, there comes option number two:
2) add a new datafile to the tablespace, for example:
alter tablespace xyz add datafile '/path/to/datafiles/xyz02.dbf' size 100M autoextend on next 100M maxsize unlimited;
answered Sep 3 '16 at 18:28
Balazs PappBalazs Papp
26.7k2931
Thanks @Balazs Papp. Now that I have better understanding of this, I'll try this option.
– Balkrishna Rawool
Sep 4 '16 at 20:11
add a comment |
Autoextensible datafiles still have a size limit.
select file_id, bytes, maxbytes from dba_data_files where tablespace_name = 'XYZ';
Maxbytes is the maximum size of the datafile, it can not grow beyond that. If bytes = maxbytes, you have two options:
1) increase the maximum size (maxbytes) as (15 is the file_id from above query), for example to 10G:
alter database datafile 15 autoextend on maxsize 20G;
Datafiles have a hard limit for the maximum size, it is 2^22 * block_size for smallfile tablespaces, and 2^32 * block_size for bigfile tablespaces.
If you use a tablespace with 8K block size, then the maximum size of your datafile is 32 GB. If your datafile is already at that size, you can not increase it any further, there comes option number two:
2) add a new datafile to the tablespace, for example:
alter tablespace xyz add datafile '/path/to/datafiles/xyz02.dbf' size 100M autoextend on next 100M maxsize unlimited;
answered Sep 3 '16 at 18:28
Balazs PappBalazs Papp
26.7k2931
Autoextensible datafiles still have a size limit.
select file_id, bytes, maxbytes from dba_data_files where tablespace_name = 'XYZ';
Maxbytes is the maximum size of the datafile, it can not grow beyond that. If bytes = maxbytes, you have two options:
1) increase the maximum size (maxbytes) as (15 is the file_id from above query), for example to 10G:
alter database datafile 15 autoextend on maxsize 20G;
Datafiles have a hard limit for the maximum size, it is 2^22 * block_size for smallfile tablespaces, and 2^32 * block_size for bigfile tablespaces.
If you use a tablespace with 8K block size, then the maximum size of your datafile is 32 GB. If your datafile is already at that size, you can not increase it any further, there comes option number two:
2) add a new datafile to the tablespace, for example:
alter tablespace xyz add datafile '/path/to/datafiles/xyz02.dbf' size 100M autoextend on next 100M maxsize unlimited;
answered Sep 3 '16 at 18:28
Balazs PappBalazs Papp
26.7k2931
answered Sep 3 '16 at 18:28
Balazs PappBalazs Papp
26.7k2931
answered Sep 3 '16 at 18:28
Balazs PappBalazs Papp
26.7k2931
answered Sep 3 '16 at 18:28
Balazs PappBalazs Papp
26.7k2931
26.7k2931
Thanks @Balazs Papp. Now that I have better understanding of this, I'll try this option.
– Balkrishna Rawool
Sep 4 '16 at 20:11
add a comment |
Thanks @Balazs Papp. Now that I have better understanding of this, I'll try this option.
– Balkrishna Rawool
Sep 4 '16 at 20:11
Thanks @Balazs Papp. Now that I have better understanding of this, I'll try this option.
– Balkrishna Rawool
Sep 4 '16 at 20:11
Thanks @Balazs Papp. Now that I have better understanding of this, I'll try this option.
– Balkrishna Rawool
Sep 4 '16 at 20:11
add a comment |
Thank u Boss it is a complete answer.........!!
answered 12 mins ago
Mostafiz MItulMostafiz MItul
1
New contributor
Mostafiz MItul is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Thank u Boss it is a complete answer.........!!
answered 12 mins ago
Mostafiz MItulMostafiz MItul
1
New contributor
Mostafiz MItul is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Thank u Boss it is a complete answer.........!!
answered 12 mins ago
Mostafiz MItulMostafiz MItul
1
New contributor
Mostafiz MItul is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Thank u Boss it is a complete answer.........!!
answered 12 mins ago
Mostafiz MItulMostafiz MItul
1
New contributor
Mostafiz MItul is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 12 mins ago
Mostafiz MItulMostafiz MItul
1
New contributor
Mostafiz MItul is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 12 mins ago
Mostafiz MItulMostafiz MItul
1
answered 12 mins ago
Mostafiz MItulMostafiz MItul
1
1
New contributor
Mostafiz MItul is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Mostafiz MItul is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Mostafiz MItul is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f148747%2fora-01691-unable-to-extend-lob-segment-even-though-my-tablespace-has-autoexten%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
If that is a small file tablespace, maybe it has reached the 32GB limit?
– a_horse_with_no_name
Sep 3 '16 at 14:37
Yes, it is a small file tablespace. And it is very much possible that it has reached 32GB limit. What options do I have now?
– Balkrishna Rawool
Sep 3 '16 at 15:04
2
Add data files.
– tesla747
Sep 3 '16 at 15:58
thanks tesla747 and @a_horse_with_no_name. I'll try that option.
– Balkrishna Rawool
Sep 4 '16 at 20:10