Home » RDBMS Server » Server Administration » Taking Tablespace Offline (Oracle 10G Database)
Taking Tablespace Offline [message #571369] Sat, 24 November 2012 23:21 Go to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Hi,

I am a newbie in DBA.

I have created an user named "Raja" with a default tablespace as "Raja_TBS" along with a datafile "rajadata.dbf".

I have taken the tablespace offline

SQL> alter tablespace raja_tbs offline;

Tablespace Altered.

If my understanding is correct, when I take a tablespace offline, which means I cannot read or write and the tablespace is currently unavailable for users.

Just wondering I am still able to create a table on the "Raja_TBS" while it is offline.

Could someone please throw some light?

Thanks,
Raja
Re: Taking Tablespace Offline [message #571370 is a reply to message #571369] Sat, 24 November 2012 23:28 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>Just wondering I am still able to create a table on the "Raja_TBS" while it is offline.
It depends.
Re: Taking Tablespace Offline [message #571371 is a reply to message #571370] Sat, 24 November 2012 23:34 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Swan,

I have gone through the link, Not sure if I am in an inappropriate section.

Raja
Re: Taking Tablespace Offline [message #571372 is a reply to message #571371] Sat, 24 November 2012 23:36 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
how can we reproduce what you report?
Re: Taking Tablespace Offline [message #571373 is a reply to message #571372] Sat, 24 November 2012 23:39 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Do you mean a screenshot of my Sql*Plus Screen?
Re: Taking Tablespace Offline [message #571377 is a reply to message #571373] Sun, 25 November 2012 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No screenshot, copy and paste the SQL*Plus session in TEXT online, no image.
For instance:
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

SQL> create tablespace test datafile 'E:\ORACLE\BASES\MIKA\TEST01.DBF' size 100m;

Tablespace created.

SQL> alter tablespace test offline;

Tablespace altered.

SQL>  create table test (v int) tablespace test;
 create table test (v int) tablespace test
*
ERROR at line 1:
ORA-01542: tablespace 'TEST' is offline, cannot allocate space in it

Regards
Michel
Re: Taking Tablespace Offline [message #571378 is a reply to message #571369] Sun, 25 November 2012 02:13 Go to previous messageGo to next message
John Watson
Messages: 4676
Registered: January 2010
Location: Global Village
Senior Member
Your exact release is critical, and so is whether you run the commands as SYS or as a regular user. See this:
orcl>
orcl> alter tablespace users offline;

Tablespace altered.

orcl>  create table t1(c1 date) tablespace users;

Table created.

orcl> insert into t1 values(sysdate);
insert into t1 values(sysdate)
            *
ERROR at line 1:
ORA-01542: tablespace 'USERS' is offline, cannot allocate space in it


orcl> select * from v$version;

BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

orcl> sho parameter defer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
deferred_segment_creation            boolean     TRUE
orcl>
and read up on the parameter I've shown at the end.
Re: Taking Tablespace Offline [message #571379 is a reply to message #571378] Sun, 25 November 2012 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that OP is in 10g and so this parameter did not exist (and this is why I 1) did not mention it, 2) (implicitly) asked OP to post his as I showed mine.

Regards
Michel
Re: Taking Tablespace Offline [message #571388 is a reply to message #571377] Sun, 25 November 2012 08:16 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Thanks to Michel and John,

I have upgraded to 11g Version.

I have tried the steps and I still get the same result.

Result : Table is created while the tablespace is offline.

I am a newbie and I am not able to understand the "sho parameter defer", however, I tried as shown in the below

SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL> create tablespace raja_tbs datafile 'd:\app\raja\oradata\orcl\rajadata.dbf' size 100m;

Tablespace created.

SQL> create table scott.table01(n number) tablespace raja_tbs;

Table created.

SQL> alter tablespace raja_tbs offline;

Tablespace altered.

SQL> create table scott.table02(n number) tablespace raja_tbs;

Table created.

SQL> sho parameter defer;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL>




Could you please make me understand why a table is created when a tablespace is offline?

Thanks,
Raja
Re: Taking Tablespace Offline [message #571389 is a reply to message #571388] Sun, 25 November 2012 08:20 Go to previous messageGo to next message
John Watson
Messages: 4676
Registered: January 2010
Location: Global Village
Senior Member
When you read the description of the parameter, all will become clear. You will never get anywhere in database admin if you do not read the docs.
Re: Taking Tablespace Offline [message #571391 is a reply to message #571389] Sun, 25 November 2012 08:57 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

John,

I am sorry if my previous question bothers you, I understand the point a parameter is something that should be configured before an oracle instance is started and I am unable to understand deferred_segment_creation concept even I found this link to be very useful.

www.dbsnaps.com/oracle/oracle-deferred-segment-creation

I have a poor internet connection at home of 256kbps and videos in the above link doesn't stream.

I understand it should be harder for a DBA role of my knowledge but I am sure reading this forum everyday and interacting with knowledgeable folks like you and Michel would definitely pitch me up as a DBA sooner than later.
Many thanks for all your time.

If you can spend few more minutes to help me understand why a table creation is allowed when I have taken the tablespace offline, your valuable time is deeply appreciated.

Regards,
Raja




Re: Taking Tablespace Offline [message #571392 is a reply to message #571391] Sun, 25 November 2012 09:06 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
At what point in time does table become instantiated?
Is a table more than only metadata?
Can/does table exist when it contains no data?
Re: Taking Tablespace Offline [message #571393 is a reply to message #571392] Sun, 25 November 2012 09:25 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

I don't know at point the table becomes instantiated.
If my understanding is correct, a table can be of anything to store values, not restricted to metadata.
Yes, a table structure can exist without actual data's inserted.
Re: Taking Tablespace Offline [message #571394 is a reply to message #571393] Sun, 25 November 2012 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
>Yes, a table structure can exist without actual data's inserted.
after metadata & before data, does "structure" really exist?
Re: Taking Tablespace Offline [message #571397 is a reply to message #571394] Sun, 25 November 2012 09:43 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

If my understanding is correct, A table structure does exists, after inserting any datas and so far what I have learned is to insert just data values.

SQL > insert into <table_name> values (1,oracle);

SQL> desc <table_name>

The table structure does remains the same after inserting datas.

[Updated on: Sun, 25 November 2012 10:05]

Report message to a moderator

Re: Taking Tablespace Offline [message #571402 is a reply to message #571397] Sun, 25 November 2012 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In your case:
1/ CREATE TABLE creates the definition of the table in Oracle dictionary
2/ The first INSERT then creates the segment, the associated physical object, in the tablespace.

Please read Database Concepts.

Note that if you post false information you will not have correct answers.
I hope you now know, from John's and my answers, how you have to post.

Regards
Michel

[Updated on: Sun, 25 November 2012 11:18]

Report message to a moderator

Re: Taking Tablespace Offline [message #571403 is a reply to message #571402] Sun, 25 November 2012 10:52 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables002.htm#ADMIN13319
Re: Taking Tablespace Offline [message #571406 is a reply to message #571369] Sun, 25 November 2012 11:20 Go to previous messageGo to next message
John Watson
Messages: 4676
Registered: January 2010
Location: Global Village
Senior Member
Raja, I think you should have enough information now to understand the difference between the logical table and the physical segment, and where these structures exist. When you are happy with that, here is your next exercise. Explain this behaviour:
orcl> create table t1 (c1 date) tablespace users;

Table created.

orcl> insert into t1 values(sysdate);

1 row created.

orcl> alter tablespace users read only;

Tablespace altered.

orcl> insert into t1 values(sysdate);
insert into t1 values(sysdate)
            *
ERROR at line 1:
ORA-00372: file 4 cannot be modified at this time
ORA-01110: data file 4: 'C:\APP\JOHN\ORADATA\ORCL\DATAFILE\USERS.DBF'


orcl> delete from t1;
delete from t1
            *
ERROR at line 1:
ORA-00372: file 4 cannot be modified at this time
ORA-01110: data file 4: 'C:\APP\JOHN\ORADATA\ORCL\DATAFILE\USERS.DBF'


orcl> drop table t1;

Table dropped.

orcl> --what?? how can that be possible????
orcl>
Re: Taking Tablespace Offline [message #571407 is a reply to message #571406] Sun, 25 November 2012 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Let him the time to read the book. Wink

Regards
Michel
Re: Taking Tablespace Offline [message #571421 is a reply to message #571407] Sun, 25 November 2012 20:38 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Dear Michel and John,

First of all, Thank you so very much for coming down to my level ( starting from the basics ) to make me in the way I can better understand is something I really have to appreciate, I am aware you guys have huge experience in the Oracle Market and to teach guys like me from the basis should really be boring for you and in fact it is not really that necessary to give much importance as you are way higher and higher than me. But your simplicity customizing here,

orcl> --what?? how can that be possible????

It really feels like I am in a classroom where my Guru's are training me to step me up as a DBA in real time. I am very happy and I am very thankful for all the pains you have taken to make me better understand in the Database Administration. I realize its not so simple to spend time for someone, that too taking SQL copy from window and giving me exercise so I better learn in real time. Once again, Thank You for your time.

Coming down to actual question,

Yes, I understand tablespace is where data's are logically stores and datafiles where the data' are physically stored, when you have changed the tablespace to the read-only mode, which means that no further Data Manipulation is allowed until we take the tablespace to the read/write mode.

INSERT is a DML statement and hence data to be inserted are barred and again with DELETE a DML Statement the manipulation is barred.

DROP on the other end is Data Definition Language and its auto-committed. DDL statements are the structural change and it does not have any impact on the read-only mode and hence the table is dropped.

I have read the link on the "Deferred Segment Creation" and the "Database Concepts" before it is already passed but only after your example and reading it again now makes me understand,

CREATE TABLE has nothing to do with the offline tablespace or an online tablespace and the structure is created in the Oracle Dictionary. In order to save disk resources, deferred_segment_creation is being used and only at the first insert, the segment will be created and the actual table occupies space. This way we can save a lot of space for many unpopulated tables.

Could you please tell me if my understanding is correct?

Many Thanks,
Raja
Re: Taking Tablespace Offline [message #571422 is a reply to message #571421] Sun, 25 November 2012 20:46 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
>Could you please tell me if my understanding is correct?
You are CORRECT.
Re: Taking Tablespace Offline [message #571423 is a reply to message #571422] Sun, 25 November 2012 20:48 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Swan,

WoW.. Thanks..! Smile

Raja
Re: Taking Tablespace Offline [message #571431 is a reply to message #571423] Sun, 25 November 2012 23:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
DROP on the other end is Data Definition Language and its auto-committed. DDL statements are the structural change and it does not have any impact on the read-only mode and hence the table is dropped.


Not only, DDL is also (or may also be) a physical change, but in this case this latter part is postponed when you will recover the tablespace to put back it online/read-write.

Regards
Michel
Re: Taking Tablespace Offline [message #571438 is a reply to message #571431] Mon, 26 November 2012 00:35 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Oh fine Michel.

When it comes to Swan's question,

At what point in time does table become instantiated?

What is it actually?



Re: Taking Tablespace Offline [message #571443 is a reply to message #571438] Mon, 26 November 2012 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you first INSERT, as I told you, in your specific case.

Regards
Michel
Re: Taking Tablespace Offline [message #571448 is a reply to message #571443] Mon, 26 November 2012 01:51 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Ah, fine, at the time it first consumes the memory space. I understood now. Thank You.

Michel, Please correct me if I am wrong.

When I am inserting data's without commit, it is temporarily stored in a redo log buffer, and when I commit the values, the Log Writer will write the values to the Redo Log files.

And when a redo log file is filled and when a log switch is about to happen, the Archiver will Archive the redo log files to the Archive log files.

Here, when is actually a data file is getting filled?

Raja.
Re: Taking Tablespace Offline [message #571457 is a reply to message #571448] Mon, 26 November 2012 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
When I am inserting data's without commit, it is temporarily stored in a redo log buffer, and when I commit the values, the Log Writer will write the values to the Redo Log files.


This is completly wrong.

Quote:
And when a redo log file is filled and when a log switch is about to happen, the Archiver will Archive the redo log files to the Archive log files.


Correct.

Quote:
Here, when is actually a data file is getting filled?


Either it extends if it is in auto-extensible mode, either the current operation fails.

Please read the book I pointed you to, you obviously didn't read it as these points are explained.

Regards
Michel
Re: Taking Tablespace Offline [message #571881 is a reply to message #571457] Mon, 03 December 2012 09:19 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

[quote title=Michel Cadot wrote on Mon, 26 November 2012 14:25]Quote:
When I am inserting data's without commit, it is temporarily stored in a redo log buffer, and when I commit the values, the Log Writer will write the values to the Redo Log files.

This is completly wrong.



Could you please tell me where I am wrong? It is my understanding, any changes made to the database will be recorded in the redo log files before it is being written to the data file.

When I am updating a name "Austin" to "Steve Austin" without COMMIT using the UPDATE statement, it will be in the redo log buffer.

When I roll back, the undo segment will be used and it will roll back to "Austin" again.

This will again be recorded in the redo log buffer.

And when I COMMIT after using an UPDATE statement, the LogWriter will write the updated values to the Redo Log Files.

Please tell me if my understanding is correct.

Thank You.








Re: Taking Tablespace Offline [message #571882 is a reply to message #571881] Mon, 03 December 2012 09:25 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
what happen in single UPDATE when amount of changed data exceeds both RAM & total REDO size (prior to COMMIT?
Re: Taking Tablespace Offline [message #571888 is a reply to message #571882] Mon, 03 December 2012 09:43 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Same clarification I have in mind but with different scenario,

When I set the parameter Undo_tablespace=Undotbs1 and when the size of my undotbs1 is set to 1 GB in Size.

Now I have a big table of size with thousands of rows and its size is 2GB.

SQL> delete from bigtable;

Now when I have only 1GB of Undo Space available, how Oracle will handle this undo generation for "rollback"?

Please write to me for your question and my question as I do not know the answer for both. Smile
Re: Taking Tablespace Offline [message #571889 is a reply to message #571888] Mon, 03 December 2012 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
when all else fails Read The Fine Manual

http://docs.oracle.com/cd/E11882_01/server.112/e25789/toc.htm
Re: Taking Tablespace Offline [message #571890 is a reply to message #571889] Mon, 03 December 2012 09:49 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

I do read but I sometimes do not get the point or I lack understanding reading the manual, and only when all fails, I am here for a real time help with an example from real time people and I am thankful for whoever makes this jumbo head understand the concept. Thank You.

I do read here.. But I lack understanding, help me please if you can spare your valuable time.

http://www.ixora.com.au/tips/tuning/log_buffer_size.htm

Copied from above link :
What if the log buffer is too small?

If the log buffer is too small, then log buffer space waits will be seen during bursts of redo generation. LGWR may not begin to write redo until the _log_io_size threshold (by default, 1/3 of the log buffer or 1M whichever is less) has been exceeded, and the remainder of the log buffer may be filled before LGWR can complete its writes and free some space in the log buffer.

Ideally, the log buffer should be large enough to cope with all bursts of redo generation, without any log buffer space waits. Commonly, the most severe bursts of redo generation occur immediately after a log switch, when redo generation has been disabled for some time, and there is a backlog of demand for log buffer space.

[Updated on: Mon, 03 December 2012 09:50]

Report message to a moderator

Re: Taking Tablespace Offline [message #571891 is a reply to message #571890] Mon, 03 December 2012 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Copied from above link :

Ixora was written for version 7 (and a little bit for 8i), so forget it.


raja_dba wrote on Mon, 03 December 2012 16:19
Michel Cadot wrote on Mon, 26 November 2012 14:25
Quote:
When I am inserting data's without commit, it is temporarily stored in a redo log buffer, and when I commit the values, the Log Writer will write the values to the Redo Log files.


This is completly wrong.


Could you please tell me where I am wrong? It is my understanding, any changes made to the database will be recorded in the redo log files before it is being written to the data file.


Modifications are written in redo log buffer then in redo log files, and in buffer cache then in files, with or without commit, depending on the timing between all operations.

Regards
Michel

[Updated on: Mon, 03 December 2012 10:21]

Report message to a moderator

Re: Taking Tablespace Offline [message #571892 is a reply to message #571891] Mon, 03 December 2012 10:13 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Michel,

So the undo information stored in the undo tablespace or in the undo segments are also included in the redo, thus protecting both redo and undo in the same place?

Could you please also help me in this?

When I set the parameter Undo_tablespace=Undotbs1 and when the size of my undotbs1 is set to 1 GB in Size.

Now I have a big table of size with thousands of rows and its size is 2GB.

SQL> delete from bigtable;

Now when I have only 1GB of Undo Space available, how Oracle will handle this undo generation for "rollback"?


[Updated on: Mon, 03 December 2012 10:14]

Report message to a moderator

Re: Taking Tablespace Offline [message #571894 is a reply to message #571892] Mon, 03 December 2012 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So the undo information stored in the undo tablespace or in the undo segments are also included in the redo, thus protecting both redo and undo in the same place?


Yes, in the same redo entry.

Quote:
how Oracle will handle this undo generation for "rollback"?


It cannot and, as soon as the undo data will exceed 1GB, you will have an error "ORA-01650: "unable to extend rollback segment %s by %s in tablespace %s" or "ORA-30036: "unable to extend segment by %s in undo tablespace '%s'"" or the like.

Regards
Michel

[Updated on: Mon, 03 December 2012 10:25]

Report message to a moderator

Re: Taking Tablespace Offline [message #571895 is a reply to message #571894] Mon, 03 December 2012 10:26 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

Were you trying to mean that the delete would not happen if we try to delete the bigtable of 2gb?
Re: Taking Tablespace Offline [message #571898 is a reply to message #571895] Mon, 03 December 2012 10:38 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
>Were you trying to mean that the delete would not happen if we try to delete the bigtable of 2gb?
The DELETE would fail.
Re: Taking Tablespace Offline [message #571899 is a reply to message #571882] Mon, 03 December 2012 10:40 Go to previous messageGo to next message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

BlackSwan wrote on Mon, 03 December 2012 20:55
what happen in single UPDATE when amount of changed data exceeds both RAM & total REDO size (prior to COMMIT?


So the update here would fail just as the delete bigtable we discussed before?


Re: Taking Tablespace Offline [message #571900 is a reply to message #571899] Mon, 03 December 2012 10:48 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
>So the update here would fail just as the delete bigtable we discussed before?
answer can be obtained by running a simple test SQL.
Re: Taking Tablespace Offline [message #571901 is a reply to message #571899] Mon, 03 December 2012 10:52 Go to previous messageGo to previous message
raja_dba
Messages: 33
Registered: November 2012
Location: India
Member

I am not sure how to try with a update statement that equals my redo log size.

Previous Topic: Difference between bytes & user_bytes in dba_data_files
Next Topic: about tablespace
Goto Forum:
  


Current Time: Tue Oct 21 23:01:48 CDT 2014

Total time taken to generate the page: 0.26973 seconds