Server Administration :: Create Tablespace For Administration

Nov 29, 2010

i'm a student currently learning database administration security.

I need to create a tablespace for administration of database but i don't know what datafile settings are best suited for admin usage.

I have attached the schema that was given to me for this assignment.

View 12 Replies


Server Administration :: ORA-01658 / Unable To Create INITIAL Extent For Segment In Tablespace?

Aug 11, 2011

I have executed the below statement to move a bulk of table from source table space-SHOP3_DATA to destination tablespace -LINUX_CATA. Before executing the statement the source table space was having 85% full message . While executing the statement this is giving the error for the source tablespace means the the tablespace is getting consumed in the source TS. Currently no-one is using the tablespace SHOP3_DATA' . If I add datafile to the SHOP3_DATA the problem may be solved but why the space is getting consumed in the source. Now the tablespace SHOP3_DATA is 95% full

SQL> alter table LINUX_CATA.TST_FOLDERS move tablespace LINUX_CATA;
Table altered.
SQL> alter table LINUX_CATA.TST_SEARCH_TESTS move tablespace LINUX_CATA;
alter table LINUX_CATA.TST_SEARCH_TESTS move tablespace LINUX_CATA
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace SHOP3_DATA


View 10 Replies View Related

Server Administration :: Creating A New Tablespace With Datafile / Assign Users To Current Tablespace

May 27, 2011

i have a tablespace which contains 121 datafile(max limit reached) as a dba what we have to do?

creating a new tablespace with a datafile and assign the users to the current tablespace which i created now.iif the above process is correct,after some time the tablespace which was filled up got freed can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces

View 9 Replies View Related

Server Administration :: Single Big Tablespace Versus Multiple Tablespace?

Jan 26, 2011

My database version is

Oracle Database 11g Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE Production
TNS for Linux: Version - Production
NLSRTL Version - Production

My os version is

Linux damdat01 2.6.18-128.7.1.el5 #1 SMP Wed
Aug 19 04:00:49 EDT 2009 x86_64 x86_64 x86_64

My database is OLP system.

My question is what are the advantages and disadvantages having one single tablespace versus multiple tablespace?

Easy to maintain when you have single tablespace. but hard to track the IO issues if you have one single tablespace.

View 7 Replies View Related

Server Administration :: Convert Dictionary Managed Tablespace To Locally Managed Tablespace?

Jan 6, 2012

I have one database which is recently upgraded from oracle 8.1.5 to oracle database is having around 300 tablespace and total size of the database is 1.5 TB.

The database was created in oracle 8i and all the teblespace were DMT(Dictionary Managed Tablespace) .Usually after up gradation all the tablespace are in DMT mode. Now my requirement is to convert all the tablespace into LMT (Locally Managed Tablespace) so that I can AVAIL ALL THE FEATURES OF LMT.

This database is a mission critical database and very less downtime can be allowed.

View 6 Replies View Related

Server Administration :: System TableSpace Is So Big?

Sep 26, 2011

Today I see my system tablespace is 98%, 480 MB used. Why the system tablespace is so big?

View 5 Replies View Related

Server Administration :: Tablespace On Certain Workstations

Mar 5, 2012

I am NOT a db admin but I do 'maintain' my application that runs with oracle 8i. I am now getting the error on certain workstations:

ORA-03232: unable to allocate an extent of 16 blocks from tablespace 3

I have access to DBA Studio and all the tables in the STORAGE tab show no more than 65% usage

View 7 Replies View Related

Server Administration :: Shrink Tablespace

Jun 7, 2010

I have a tablespace with 5GB size, data in the tablespace is 4.8GB. if i want to shrink the tablespace then much size could be shrinked.

View 4 Replies View Related

Server Administration :: Tablespace Creation

Jun 24, 2012

Problem Description: There are five range partitioned tables which are partitioned by date,designed by previous DBA.Four tables are in one tablespace and one more table is in another tablespace.For every three months there are two tablespaces are created autamatically in new names for future dates.There is no scheduled job for this.How they are created?.This is our question.

View 3 Replies View Related

Server Administration :: Max Size Of Tablespace

Jan 4, 2010

I have one tablespace PSINDEX with Maxsize of 6 GB. But when I query the tablespace its showing the BYTES is greater than MAXBYTES.

View 5 Replies View Related

Server Administration :: System Tablespace Had Been Used More?

May 4, 2012

In my database ,the size of the system tablespace is i found its free space is 477.875M.

--------------- -------
SYSTEM 477.875

In system tablespace ,the max size segment is IDL_UB1$,it is 167M

------------- -----
IDL_UB1$ 167M

QUESTION: what's the table IDL_UB1$ used for ?

View 4 Replies View Related

Server Administration :: Alter Tablespace

Jan 4, 2011

My tablespace contains two datafiles dfile1.dbf and dfile2.dbf on D drive of my filesystem. Now i copied these dbf files on pasted it on a location in E drive. Now i want my tablespace to use dbf files pasted in new location.

Will i have to Alter Tablespace Add Datafile with Reuse clause

View 2 Replies View Related

Server Administration :: RBS Tablespace Is Not Releasing Space

Aug 27, 2012

In My database rollback segment space is not releasing space even though, there is no transaction is using RBS. RBS tablespace size is around 70GB. Unfortunately still our environment is running in 9i due to application code

SQL> show parameter undo

------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 1800
undo_suppress_errors boolean FALSE

View 15 Replies View Related

Server Administration :: Locally Managed Tablespace

Feb 26, 2010

For a locally managed tabelspace, the dropped segment is changed to a Temp. Segment, to prevent bitmap from being updated.

View 2 Replies View Related

Server Administration :: How To Query A Tablespace Being Used By Which Object

Jul 6, 2013

My Oracle db have this tablespace named INDX which is 80% full. I tried to query on the table, user using it but there is no one using this tablespace

May I know if there is a query that i can used to find out which dba_object is consuming the 80% of this tablespace.

View 5 Replies View Related

Server Administration :: Move Datafile To Another Tablespace

Feb 16, 2012

Erroneously created datafile, re: "/path/../large_rbs_03.dbf" was created under the SYSTEM tablespace which is supposedly be in the LARGE_RBS tablespace.

How do I make the said datafile be under LARGE_RBS?

View 3 Replies View Related

Server Administration :: How To Undo Tablespace Size

Feb 7, 2011

As the undo segments are used in round robin fashion, Is it possible that with varying load (concurrent users, size and number of transactions), the size of Undo tablespace on a particular day is less than the Undo tablespace size few days back, by any chance?

As a basic understanding I know that Undo is preserved for read consistency and transaction, instance recovery So if there are lot of transaction on a database on 05 Feb and before that, but there aren't any transactions on 6,7,8,9, then on 10th Feb can we see the Undo tablespace size is less than that of 05 Feb?

In the following case when data belonging to table is not required for any queries, transactions, even then the undo size is not restored upon dropping the table.

As such for large operations and batch processes shall we keep undo tablespace with files as 'Autoextend' with 'Maxsize' as 'Unlimited'?

SQL> select b.tablespace_name, Total_Kbytes_Available/1024 Tot_Mbytes_Available,
Kbytes_alloc/1024 Mbytes_allocated, kbytes_free/1024 Mbytes_Free_from_allocated,
((Kbytes_alloc - kbytes_free)*100/ Total_Kbytes_Available) Pctused
2 from ( select sum(bytes)/1024 Kbytes_free,
3 tablespace_name
4 from sys.dba_free_space


View 12 Replies View Related

Server Administration :: Increasing Tablespace Size?

Mar 7, 2010

I want to increase the size of the tablespace but when i login as sysdba or admin user i can just see the 21 tables in the dba_tablespaces or user_tablespaces. I want to see the tablespaces related to the application.

View 8 Replies View Related

Server Administration :: Removing A Tablespace And Its Associated Datafiles?

Jul 12, 2010

Due to improper documentations of a certain project, I need to drop a DEFAULT tablespace of a newly created instance including it's associated datafiles by using this command:


The default tablespace name is QWER (qwer01.dbf) and I added 2 datafiles in it, re: OPD_SML01.dbf & EXYT_SML01.dbf.

Do I have to do it online or offline?

View 1 Replies View Related

Server Administration :: How To Resize Temp Tablespace

Jun 30, 2010

i got a error temp tablespace cannot be resized. How to Resize Temp Tablespace?

View 10 Replies View Related

Server Administration :: Creating Tablespace In Raw Partition?

Aug 17, 2012

i want to create tablespace in raw partion on windows.I have added a hard disk and added extended partition to it. Then created 4 logical partitions of 256 MB with diskpart.exe.I have assigned a drive letter to one of them as X:

However, I am having trouble in creating tablespace on it. I referred to [URL]... I get following error,

SQL*Plus: Release Production on Fri Aug 17 15:32:17 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: SYSTEM
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing opti

SQL> create tablespace exampletb datafile '\.X:accounting_1' size 100;
create tablespace exampletb datafile '\.X:accounting_1' size 100

ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
SQL> create tablespace exampletb datafile '\.X:accounting_1' size 502;
create tablespace exampletb datafile '\.X:accounting_1' size 502


View 1 Replies View Related

Server Administration :: TEMP Tablespace Estimation

Oct 12, 2011

We have a database running with Oracle on Solaris 5.10 [SUN SPARC v240] where the assigned TEMP tablespace size looks to be quite huge ( = 8GB!). There are not much SQL queries being run on the database, but what can I do/find/investigate to be sure of how much TEMP space is 'actually' required for the application to run OK?

Note. I have seen the previous DBA's have marked AUTOEXTEND = ON for the TEMP datafiles as well.

Separate query (but linked to TEMP tablespace):

Even if the TEMP datafiles are created with AUTOEXTEND = ON, sometimes I am seeing 'ORA-01652: unable to extend temp segment by 128'.

View 4 Replies View Related

Server Administration :: Reducing Tablespace Size?

Jan 24, 2012

we have a tablespace of size 900 GB where 90% of space is occupied by two tables having BLOB data and now i need to drop these two tables and then to recover the space, i need to resize the tablespace (datafiles).

View 3 Replies View Related

Server Administration :: System Tablespace Resize?

Dec 23, 2012

My system tablespace size is 800mb and it sends an alert saying that it has exceeded 80% of its usage.However its total max size is 30gb.

Its autoextensible is set to 'YES'.
2 Node RAC database.
File system is ASM

My question here is... is it advicable to resize a system tablespace to some size when autoextensible is set to 'YES'
or genrally can we resize a system tablespace when autoextensible is set to 'No' ?

SQL> col FILE_NAME format a40
SQL> /
FILE_NAME BYTES/1024/1024 AUT MAXBYTES/1024/1024
---------------------------------------- --------------- --- ------------------
+DATA/ge01114/datafile/system.556.2254454 800 YES 30000.9844

View 4 Replies View Related

Server Administration :: Why Can't SYSTEM Tablespace Extend

Mar 19, 2010

The SYSTEM tablespace refuses to autoextend (3 segments in SYSTEM tablespace unable to extend). It sits on its current size of 559MB (auto set to 2GB, increment 10M).

I guess there are no free extends that are that big as 10M.

View 5 Replies View Related

Server Administration :: Index Tablespace Is Not Growing?

Apr 26, 2012

In our production database server Index tablespace is not growing now... On average index tablespace increases 200 MB to 250 MB per day. Last two weeks index tablespace does not increase..

I am giving you all a statistics of our production db Tablespace usage..

Date Globusdataxml(MB)Globusindexxml(MB)
23-4-2012 26321025 1581.94
24-4-2012 24633.31 1584.25
25-4-2012 23410.88 1576.25
26-4-2012 22186.19 1573.25

View 9 Replies View Related

Server Administration :: Adding Datafiles To Tablespace

Dec 20, 2011

Can we add a datafile mapped to a shared network drive in windows server.

View 1 Replies View Related

Server Administration :: Temporary Tablespace Not Seen On User_tablespaces

Oct 8, 2012

I have a requirement to get create a separate temporary tablespace for application user, the tablespace name is OPC_TEMP on Oracle 11g. During the application installation we are expecting this tablespace to show up under the user_tablespaces but it doesn't. When I tried to check with the DBA, they mentioned that it is possible for this tablespace to be listed under the user_tablespaces because to have this, the quota has to be assigned to the tablespace and it is not possible to assign quota to temporary tablespace on oracle 10.2 and above due to certain limitation (oracle metalink ID 331657.1).

View 10 Replies View Related

Server Administration :: Unable To Extract Ddl Of Tablespace

Feb 24, 2012

[code]SQL> set heading off
SQL> set pagesize 0
SQL> set long 1000000
SQL> set feedback off
SQL> select
2 dbms_metadata.get_ddl('TABLESPACE','TBLS_DATA')
3 from
4 dual
5 /
ORA-31603: object "TBLS_DATA" of type TABLESPACE not found in schema
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2806
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

When i try to extract ddl of tablespace from a schema JACK, i get error.This tablespace TBLS_DATA does exist in the database.

View 4 Replies View Related

Server Administration :: (defaulted / Pointed) To Right Tablespace?

Jul 8, 2010

As I queried our newly created db and checked if the tables I created were supposed to be on the default tablespace (LEDGER)using this SQL command:

SQL> select owner, table_name, tablespace_name from dba_tables
2 where owner = 'ELEDGER01';
-------------------- --------------------- ---------------

how would I point those 2 tables for it's supposed or right tablespace (from USERS to LEDGER)..

View 6 Replies View Related

Copyrights 2005-15, All rights reserved