Server Administration :: How To Alter / Change Size Of Tablespaces
Feb 26, 2013
1-how can i alter/change the size of tablespaces?.
2-is any changing in tablespace size will effect the over all performance?
Tablespace ; Size (MB); Free (MB); % Free; % Used
------------------------------;----------;----------;----------;----------
USERS ; 5; 4; 80; 20
SYSAUX ; 600; 140.875; 23; 77
UNDOTBS1 ; 640; 114.125; 18; 82
SYSTEM ; 700; 28.3125; 4; 96
TEMP ; 64; 0; 0; 100
View 4 Replies
ADVERTISEMENT
Jun 8, 2012
We have one primary oracle database 10.2 and standby by database with no data guard. Initially we have 2 redo log group in primary and standby database.
We have recently add 2 more redo log and increase the size of log member from 50m to 200m in primary database. We don't have any problem in primary database.but in standby database we face a problem because we cannot open it. It always in mount stage in which . How we change the size of current redo log because we can't run. Alter system switch logfile command in mount stage.
View 3 Replies
View Related
Jul 22, 2011
I have deleted lots of data from existing tables in one tablespace. shrink or coalesce the size of the tablespace.
View 7 Replies
View Related
Jun 9, 2011
I just want to know what are precautionary measures if tablespaces in a database is in autoextend mode. I'm wondering if these tablespaces reached its maximum sizes.
In our case, we are administering a database (turned over by our outsourcer after a 2-year maintenance) with SAP interface, and we noticed that most of it's tablespaces were created with initial size of 2Gb up to a maximum size of 10Gb, all were 'autoextensible'.
View 1 Replies
View Related
Oct 31, 2011
How to find out the tablespaces specific to application (i.e. not related to oracle like system, sysaux,temp,undo etc..)
View 1 Replies
View Related
Jan 6, 2011
We are creating non standard blocking size for 16k and so accordingly have to set the parameter db_16k_cache_size.
if have any thumb rule for setting the value for this parameter.
View 5 Replies
View Related
Nov 7, 2011
Currently all my tablesapces are "UNIFORM" and I am looking into going to "AUTOALLOCATE" so space will not be some much of an issue.
I am using the following code and here is the output
sqlplus -s / <<-EOT
set pages 50
set line 100
set verify off
set feed off
set trimspool on
[code]........
Have a script that can emulate my above output and also deal with tablespaces that can autotallocate?
View 4 Replies
View Related
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
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
Dec 9, 2010
I try to alter the SGA it give me error The Steps which I do show sga
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
[code]...
View 5 Replies
View Related
Nov 7, 2010
I do below alter command alter tablespace RCA_MB_IDX add datafile '/gxsdb/database oradata5/RCA/RCA_REF_MB_IDX_01.dbf ' size 1024M
But there is extra space in the file location '/gxsdb/database/oradata5 RCA/RCA_REF_MB_IDX_01.dbfX' I mark X for that space ...
The command is commit .
But there is problem when do backup ...as the file cannot be find since there is extra space after ".dbf" How can I fall back ?
View 4 Replies
View Related
Dec 29, 2011
when i make a datafile offline,can i add the following option: normal or immediate?
such as like here:
SQL> ALTER DATABASE DATAFILE 5 OFFLINE NORMAL;
OR
SQL> ALTER DATABASE DATAFILE 5 ONLINE IMMEDIATE;
when i try a test ,it occurs an error:ORA-00933.
View 4 Replies
View Related
Aug 9, 2010
Requirement: I need to create a Function to allow users to change their own password when they are logging in to an application. Also, I would prefer to not use the ALTER command.
View 12 Replies
View Related
Jan 8, 2013
I can use trigger to monitor ALTER TABLESPACE ddl statement against a particular tablespace in a schema.
CREATE OR REPLACE TRIGGER HOT_MAIL_DDL_CHANGE
AFTER ALTER TABLESPACE <tbs_name>
ON SHANNURA.SCHEMA
BEGIN
INSERT INTO HOT_MAIL_DDL_AUDIT_LOG VALUES
(SYSDATE,
SYS_CONTEXT('USERENV', 'SESSION_USER'),
ORA_SYSEVENT,
ORA_DICT_OBJ_TYPE,
ORA_DICT_OBJ_NAME
);
END;
/
I think, line no.2 isn't a valid statement. Or can I use AUDIT instead - in fact, I want to monitor/audit only ALTER TABLESPACE ddl statement and that too for a particular tablespace only.
View 3 Replies
View Related
Sep 1, 2012
SQL> ALTER SESSION SET CURRENT_SCHEMA = CLA_T3;
Session altered.
SQL> select sys_context('USERENV','SESSION_USER') current_user,
2 sys_context('USERENV','SESSION_SCHEMA') current_schema
3 from dual
4 ;
CURRENT_USER
--------------------------------------------------------------------------------
CURRENT_SCHEMA
--------------------------------------------------------------------------------
CSR_ETL
CLA_T3
SQL> set linesize 300;
SQL> /
CURRENT_USER
----------------------------------------------------------------------------------------------------
CURRENT_SCHEMA
----------------------------------------------------------------------------------------------------
CSR_ETL
CLA_T3
SQL> create table cla_t3.test (r number, b char(2));
create table cla_t3.test (r number, b char(2))
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> create table test (r number, b char(2));
create table test (r number, b char(2))
*
ERROR at line 1:
ORA-01031: insufficient privileges
After Setting current schema to 'CLA_T3', I am unable to create table in cla_t3 schema.
View 5 Replies
View Related
Jan 31, 2011
How to know DB size increase per hour or day on the Oracle?
View 3 Replies
View Related
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
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
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
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
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
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
Apr 21, 2010
how can we find the size of the oracle database 11g.
View 2 Replies
View Related
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
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
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
Oct 2, 2012
One of our solaris machines is running Oracle 8.0.3
A table reached the 2 Gb size and oracle failed due to the operating system file size limitation.
The information in the table is not relevant and can be deleted, but the table contains a lot of indexes.
I would like to know the best procedure to delete the information and reduce the size of the file.
View 3 Replies
View Related
Dec 5, 2012
My listener.trc file has grown to a size of 56G and is contineously growing with the following message:
9:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:670] naeshow: [05-DEC-2012
[code]...
View 1 Replies
View Related
Jan 31, 2013
I have Oracle 11gR2 running on windows xp machine. Windows xp has total size of 150 GB and free space of 95 GB.
I checked the size of the database that I created. It showed the total size of the database as 2 GB and used space as 2 GB. If I want to increase the total size of the database to 50 GB, what should i do? Now which is the disk space size? Windows or Oracle?
View 4 Replies
View Related
Mar 27, 2011
I want to know what is the size of each granule for oracle 10g. I read it from the following link
[URL].........
There it is described that
Quote:
The memory for dynamic components in the SGA is allocated in the unit of granules. Granule size is determined by total SGA size. Generally speaking, on most platforms, if the total SGA size is equal to or less than 1 GB, then granule size is 4 MB. For SGAs larger than 1 GB, granule size is 16 MB. Some platform dependencies may arise. For example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 1 GB. Consult your operating system specific documentation for more details.
Now My query about full list of granule size for different platform like windows 64 bit, unix etc.
View 12 Replies
View Related
