-
Good afternoon all,
I have 3 tablespaces, tblsp_1, tblsp_2, and tblsp_3. Tblsp_1 is a default tablespace for user1. When user1 was importaing tables, he got an error message
'ORA-01658: unable to create INITIAL extent for segment in tablespace tblsp_2'
After I added a datafile to tblsp_2, the import was able to continue. I checked the tables and tablespace from dba_tables, and found no tables are contained in the tblsp_2, yet there is no free space left in tblsp_2 neither. This is what I got,
TABLESPACE_NAME Total Free Space
------------------------------ -------------
TBLSP_1 248512512
TBLSP_3 6291456
This is what I have for all tablespaces,
TABLESPACE_NAME FILE_NAME BYTES AT
--------------- -------------------------------------------------- ---------- --
TBLSP_1 /oradata/data/tblsp_1.dbf 840957952 NO
TBLSP_2 /oradata/data/tblsp_2.dbf 419430400 NO
TBLSP_3 /oradata/data/tblsp_3.dbf 368050176 NO
TBLSP_2 /oradata/data/tblsp_2b.dbf 571473920 YS
I don't understand why TBLSP_2 has no free space while it contains no tables? And why error 'ORA-01658' on TBLSP_2 during import, yet the data are imported to the user's default tablespace? Can anyone explain why?
Thanks.
-
I don't understand why TBLSP_2 has no free space while it contains no tables?
How did you check that it has no tables?
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
This is what I have for checking the tables,
select table_name, tablespace_name
from dba_tables
where tablespace_name = 'TBLSP_2';
And the result returns no rows selected.
-
Can u show the out put for this.
select segment_name,sum(bytes) from dba_extents where tablespace_name='' group by segment_name;
Thanx
Jr
-
Run foll. SQL and see the result
select *
from dba_segments
where tablespace_name = 'TBLSP_2';
or
select tablespace_name from dba_segments
where segment_name = .
See what tablespace name it shows for that table.
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
Originally posted by samdba
Run foll. SQL and see the result
select *
from dba_segments
where tablespace_name = 'TBLSP_2';
or
select tablespace_name from dba_segments
where segment_name = .
See what tablespace name it shows for that table.
Here, it has truncated after segment_name = as I used less than sign.
Here is the actual text.
select tablespace_name from dba_segments
where segment_name = table_name, which you think is imported in TBLSP_2 tablespace
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
Thank you all for the help.
I have found why it's full. The tablespace does not contain any tables but it contains indexes. Thank you again for the help!
-
Check folowing information about this tablespace:
1) temporary segments:
select TABLESPACE_NAME, sum(BYTES_USED), sum(BYTES_FREE)
from V$TEMP_SPACE_HEADER
group by TABLESPACE_NAME;
2) u may also check sort segments:
select TABLESPACE, sum(EXTENTS) "# of extents"
from V$SORT_USAGE
group by TABLESPACE;
3) check users' accounts :
select USERNAME, TEMPORARY_TABLESPACE, DEFAULT_TABLESPACE
from dba_users
-- where TEMPORARY_TABLESPACE = 'TBLSP_2';
and then change TEMPORARY_TABLESPACE for all users that have
TEMPORARY_TABLESPACE = 'TBLSP_2'
4) try to use:
alter tablespace TBLSP_2 offline TEMPORARY;
and then
alter tablespace TBLSP_2 online;
5) probably u need execute:
ALTER TABLESPACE TBLSP_2 COALESCE;
When u collect all information u can deside what's happened with this tablespace.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|