Server Administration :: Tablespace Size Is Same As Before Deletion Of User?

Oct 1, 2012

I have deleted a user with CASCADE option.

After deletion, the size of tablespace should be reduced.

But the tablespace size is same as before deletion of user.

Currently , that USER is deleted and not present in the database . Still space is not released after user deletion.

View 4 Replies


ADVERTISEMENT

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 :: 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

[code]....

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 :: 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 :: Reduce The System Tablespace Size?

Aug 6, 2011

how to reduce the system tablespace size

my system01.dbf size is 6gb

i want reduce from 6gb to 2gb

View 3 Replies View Related

Server Administration :: Tablespace Size Required To Be Increased?

Nov 19, 2011

I found some of the tablesapces whose used% exceeded 80%.Based on this output i thought of increasing the datafile.But there are many tablespaces of DATA1 whose autoextensible column some are set to NO, and some are set to YES. I believe we dont need to bother about the tablespace whose Autoextensible column set to YES as Oracle will automatically manage until the Maxsize of it. But some tablesapce of DATA1 are set to NO. Should i recommend to add size for this DATA1 tablespace whose autoextensible are set to YES/NO or leave it as it is.

Please see the column below.

Quote:
FILE_IDTABLESPACE_NAMESIZE(M)USED(M)FREE(M)MAX(M)AUTOEXTENSIBLE% USED
59DATA13276732741.1325.7532767YES100
60DATA13276732741.6925.1932767YES100
61DATA1327673274026.8832767YES100
76DATA13270032193.31506.560NO98
77DATA13270030858.51841.380NO94
78DATA13270029476.563223.310NO90

View 25 Replies View Related

Server Administration :: Freeing Tablespace - Returning To Initial Size?

May 9, 2010

Here is my problem : I create a tablespace named ts_photos.I create then a table on this tablespace.Then I insert some blobs records.The space grows up to 2 Gb.

I perform a truncate statment. Then the space grows up to 4Gb.I re-insert the same records and the size stays in 4Gb.The database in in archivelog mode. How can I return to the initial size (2Gb of effective data) : should I drop the tablespace and recreate it?

View 3 Replies View Related

Server Administration :: How To Free Up Tablespace Size When It Reaches Max Limit

Feb 15, 2011

How to free up the tablespace size when it reaches its threshold (Max limit)?

Say for example USERS & SYSAUX tablespace reaches above 90%.

View 14 Replies View Related

Server Administration :: Temporary Tablespace Size Suddenly Increase From 1gb To 5 - 6gb

Jul 8, 2010

I am oracle DBA (trainee) new in this field.

Some time my temporary tablespace size suddenly increase from 1gb to 5-6gb why this happened ? I want only reason why it suddenly increase?

View 6 Replies View Related

Server Administration :: ORA-04030 - Split From Tablespace With Different Block Size Inside Same Database

Nov 26, 2011

i written this code i m facing ORA-04030: out of process memory when trying to allocate 16408 bytes error

/* Formatted on 2011/11/26 11:52 (Formatter Plus v4.8. */
DECLARE
row_id varchar2(50);
v_batch_id temp.batch_id%TYPE;
v_slab_id temp.slab_id%TYPE;
flag NUMBER (2);
num varchar2(50) := &row_id;

[code].....

View 1 Replies View Related

Server Administration :: Minimum Datafile Size Required For Creating Non-system Tablespace?

May 21, 2007

what is the Minimum datafile size required for creating a non-system tablespace?

I am trying to create a tablespace by giving the syntax like,

SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 72k;
create tablespace t1
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required

SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 73k;

Tablespace created.

The blocksize for my database is 4096, as i have heard that the minimum size of the datafile is decided by blocksize, but i want to know that how it is calculated as by giving the above syntax the other values will be default. I am trying the syntax in oracle 9.2.0.1.0 version.

View 13 Replies View Related

Server Administration :: XE Limitations - How To Calculate Size Of User Data

Apr 15, 2011

I have read following statement from a link [URL]...

Oracle Database XE can be installed on any size host machine with any number of CPUs (one database per machine), but XE will store up to 4GB of user data, use up to 1GB of memory, and use one CPU on the host machine.

calculation of this 4GB size. how can we calculate this size?

by simply going to DBF file and seeing their size?
or
by exporting dump and seeing the size of that dump?

View 3 Replies View Related

Server Administration :: User Dump / Trace Files With Huge Size In MBs

Mar 22, 2012

I am facing problem in user_dump_dest directory...I have noticed that there are a lot of trace files with huge size in MBs.I clean it and after 4 days there are 40G of size..

View 1 Replies View Related

Deletion Of Contents Of Tablespace

Jun 15, 2012

How can we clean a tablespace(delete all the contents) without dropping the tablespace.

View 3 Replies View Related

Server Administration :: How To Reduce Size Of TEMP DBF File Size

Apr 13, 2011

I am using oracle 8.1.5 database and my temp01.dbf file size is increased upto 19.8 GB now i want reduce its size .

View 13 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 up.now 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 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - 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
GNU/Linux

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 :: Reorganize A Table And Index After The Deletion Of Records From Table?

Feb 7, 2012

We deleted millions of records from a table.

1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.

2.Will re org table and index improve the database performance ?

View 7 Replies View Related

Server Administration :: Externally Authenticated User Login Syntax As Proxy User?

Nov 15, 2010

I created an externally authenticated user in database. And can login without password with below syntax.

SQL> connect / @TESTDB
Connected.
SQL> show user;
USER is "SCOTT"

This scott user has a proxy permission to another DBuser PROXY_USER. Previously I used to login using below syntax.

connect scott[proxy_user]/password_for_scott@TESTDB

So now, what syntax should be used for this "ExternallY Authenticated" user to login as a proxy user?

View 10 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 10.2.0.4.The 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 :: Creating A View In User From Another User?

Nov 9, 2011

I have a strange problem when creating a view in user from another user

I have a user called "Cash_tst"

its syntax creation is

-- Create the user
create user CASH_TST
identified by ""
default tablespace CASH
temporary tablespace TEMP
profile DEFAULT
quota unlimited on cash;
-- Grant/Revoke object privileges
grant connect to CASH_TST;
grant dba to CASH_TST;
grant resource to CASH_TST;

-- Grant/Revoke system privileges
grant create any view to CASH_TST;
grant unlimited tablespace to CASH_TST;

I want to create a view

CREATE VIEW TAMER
AS SELECT *
FROM [b]AROFL[/b].RA_CUSTOMER_TRX_LINES_ALL_BEFO

"AROFL" is another user on the same database
when try to create the view "tamer" i got message of
"insufficent privilege"
although i granted "create any view" to the user "cash_tst"

View 12 Replies View Related

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 View Related

Server Administration :: How To Know DB Size Increase Per Day

Jan 31, 2011

How to know DB size increase per hour or day on the Oracle?

View 3 Replies View Related

Server Administration :: Segment Size?

Dec 28, 2011

I am working to understand the space allocation of table with the value we provided with the data type. For that I have created a table with varchar2 and length 50. Size of table created is of 65536 Bytes. This is when we don't have any insertion in the table. Later when we insert some rows, total size if the segment still remain same that is 65536 bytes.

Now again when I created table with varchar2 and length this time is 500 but still it is created with same size that is 65536. So can you just explain, on what values segment size depends on and how the length effect the size & space allocation.

db_block_size is 8192.

View 2 Replies View Related

Server Administration :: Unable To Increase Size Of Sga

Feb 2, 2012

I am trying to increase the size of sga or you can say that i want to make my sga in automatic memory management...Following is the steps i am trying

SQL> show parameter sga_max_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 96M
SQL>

after that i am trying to increase the size

SQL> alter system set sga_max_size = 200m;
alter system set sga_max_size = 200m
*
ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified

View 1 Replies View Related

Server Administration :: How To Shrink Table Size

Jan 4, 2012

I have a table: desc STG_XML

Name Null Type
------------------------------ -------- ------------------------
ENTITY_ID NOT NULL VARCHAR2(100 CHAR)
ENTITY_TYPE_ID NOT NULL NUMBER
SOURCE_ID NOT NULL VARCHAR2(512 CHAR)
XML_SCHEMA_ID NOT NULL NUMBER
JOB_ID NOT NULL NUMBER
FINGERPRINT NOT NULL VARCHAR2(100 CHAR)
ENTITY_XML_DATA CLOB()
ARCHIVED NUMBER(1)
CREATION_DATE TIMESTAMP(6)
MODIFICATION_DATE TIMESTAMP(6)
ARCHIVING_DATE TIMESTAMP(6)
CREATED_BY VARCHAR2(50 CHAR)
MODIFIED_BY VARCHAR2(50 CHAR)

The problem is that the data of the table are 40GB while on the DB the table holds 400GB! How can I shrink and reuse that space except from drop/recreate and drop/import?

The table has no initial data, so that I can play with the INITIAL parameter. Data are inserted, updated and deleted all the time. I have run DBMS_ADVISOR which recommended to SHRINK table. I have performed the shrink :

alter table STG_XML shrink space COMPACT;

but I haven't gained any space.

View 12 Replies View Related

Server Administration :: Size Of The Oracle Database 11g?

Apr 21, 2010

how can we find the size of the oracle database 11g.

View 2 Replies View Related

Server Administration :: ARC File Size Too Small?

Oct 2, 2012

I noticed my DB is generating a lot of "small" .arc files and I am usure why. As you can see from the v$log query my log file size is set to 50MB. But yet BLOCKS*BLOCK_SIZE never adds up to 50MB.

Is there anything else I can look into to see how to make the .arc files larger?

SQL> select group#, thread#, bytes from v$log;
GROUP# THREAD# BYTES
---------- ---------- ----------
1 1 52428800
2 1 52428800
3 2 52428800
4 2 52428800

select blocks, block_size, blocks*block_size from v$archived_log where sequence# between 63876 and 72851 and thread# = 1

BLOCKS BLOCK_SIZE BLOCKS*BLOCK_SIZE
---------- ---------- -----------------
28 512 14336
28 512 14336
28 512 14336
55 512 28160

[code]...

View 18 Replies View Related

Server Administration :: Non Standard Block Size

Aug 15, 2011

I am using oracle 10g with sga_max_size =4GB and db block size 16k. Now i am creating a tablespace with block size 32 kb , whats value i select for the parameter db_32k_cache_size.

Is there any standard way to calculate the value of this parameter.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved