Server Administration :: Managing Tablespaces

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


ADVERTISEMENT

Server Administration :: Managing Space In Drive E

Oct 27, 2010

In my drive E: I'll have a space issue soon because one file is taking all space. SYSTEM01. DBF size is around 25GB. I want to know if I delete some data in my database I'll gain space.

View 2 Replies View Related

Server Administration :: Tablespaces In Autoextend Settings

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

Server Administration :: Application Related Tablespaces

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

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

Server Administration :: Specifying Nonstandard Block Sizes For Tablespaces?

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

Server Administration :: Monitoring Auto Allocate Tablespaces

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

Server Utilities :: Hot Backup Of Tablespaces

May 11, 2011

Is the tablespace actually off-line when doing a user-managed hot backup? I know the data blocks are copied into a redo stream but I am not sure if that means it(tablespace) is actually on-line or off-line.

View 6 Replies View Related

Backup & Recovery :: Shift Data To New Server Using DBF Files Of Tablespaces In Use

Jul 1, 2011

I have a 10G Express system running. I Have 2 tablespaces in production. WHen taking backup, it terminates unsuccessfully saying system01.dbf is damaged. The application works fine and no data loss is found through the application interface.

So can I shift the data to a new server using the dbf files of the tablespaces in use?

View 9 Replies View Related

Server Utilities :: Data Pump Import - How To Identify Dump File Tablespaces

Jul 5, 2012

I'm rying to import schema's from a dump file that came from a different environment.

What I have is:

1. dump file
2. log file of the export

I'm trying to import the file(containing three schemas) with remap_schemas, and it fails, gives a lot of ORA-00959: tablespace 'string' does not exist.

Now, I've read in OTN:

[URL]

that what you need to do in that case is to use the REMAP_TABLESPACE option,to redirect the objects to a different tablespace.

I don't see a name of the tablespace I'm getting the error for in the export log.I don't know if I have more tablespaces I have to redirect with REMAP_TABLESPACE.

I don't want to perform this 3 times, have an error, by that find out what's the next tablespace needing redirection and only then starting over...

How can I know from the dump file and the log file,what is the tablespace names i need for the redirection to my names? Or its just that the tablespace giving me the error is the only one in the dump file?

View 3 Replies View Related

Managing Tablespace Alerts Without EM?

Dec 5, 2012

How to Managing Tablespace usage Alerts with out EM(Enterprise manager), in oracle 11g & 10g

I want to set threshold on tablespace usage, it is possible to set threshold on tablespace usage reached 80% full then send email notification with out EM use.

If it not possible,then any other option available to get automatic alert.

View 1 Replies View Related

Managing Listener And Log Files

Sep 20, 2012

I want to know the best practices about managing listener. Second how can i read log file of listener?

View 2 Replies View Related

Enterprise Manager :: Managing OEM Remotely

Aug 23, 2013

I want to manage my database remotely, through Internet. Is it possible to do that with OEM? Where can I find information related to this? Is there another monitoring software that allows me to perform this?

View 10 Replies View Related

Data Guard :: Managing Physical Standby

Sep 17, 2013

Today, I am learning dataguard 11.2.0.1. The docs said:A. Monitoring Standby DB Application of redo logs may be monitored by issuing the SQL command:

SQL> select client_dbid, process, sequence#, status from v$managed_standby;

In the primary database, the sequence# with the status “WRITING” would refer to the current redo log sequence#In the standby database, if the status of the process MRPO is “APPLYING LOG” and the status of the process LGWR is “IDLE”, then application of redo logs is up to dateI see that that MRPO at standby is "WAIT_FOR_GAP". How do I resolve this gap.

View 15 Replies View Related

Performance Tuning :: Managing Large Table?

Aug 26, 2011

I am working with an online application with the database in Oracle 10G. We have a table with 10 million rows and this table is subjected to grow in future also. Moreover we cannot archive some of these rows as these records are required for referencing.

We have all necessary indexes on the table but querying this table takes a lot of time especially when it is joined with other tables. some methods with which I can manage this table in a better way so that queries joining this table would execute faster..

SELECT
TAB1.C6,
TAB1.C8,
TAB1.C10,
TAB3.C4,

[code]....

View 7 Replies View Related

How To Migrate Two Tablespaces Into One

Apr 3, 2013

at my Oracle 11gR2 (11.2.0.3.0) Instance i have two tablespaces that i want to "bundle" into only one tablespace. Herre is the problem, that some of the tables in the two tbalespaces has the same title but some rows of the tables could be not the same.

Is it possible with a kind of migration assistent to migrate two tablespaces into one in that way, that theassistent only writes that rows into the new tablespace, that are not in the tablespace at the moment.

Another way i was thinking about is to have an insert statement coupled with a select statement. The select statement selects all the rows, that are not in the table where i want to migrate in and the insert statement put that selectet rows into the new table.

Is something of that possible?

View 10 Replies View Related

Dictionary Managed Tablespaces

Jul 7, 2010

I've got one database which was Initially upgraded from Oracle 8i to 10.2.04 running on windows. Most of the tabespaces are Dictionary managed. Do you think moving them to locally managed tablespace would give me better performance?

if Yes, what approach I should apply to move them to locally managed? I would like to do this with minimum/no downtime.

View 6 Replies View Related

Creating Schema / Tablespaces Oracle 10g

Apr 29, 2013

I'm supposed to create a database for an application. The server where this will be running previously had a database for a pretty similar app. I don't know much about Oracle so I reused the ZFS filesystems and left them how they were created (because honestly, I didn't knew why they were created that way, but I'm pretty sure it was for a good reason).

CODEapp/oradata_smart_ora1    858M  12.2G   858M  /oradata/SMART/ora1
app/oradata_smart_ora2   7.18M  18.0G  7.18M  /oradata/SMART/ora2
app/oradata_smart_ora3   7.18M  36.0G  7.18M  /oradata/SMART/ora3
app/oradata_smart_ora4   60.6K   400G  60.6K  /oradata/SMART/ora4
app/oradata_smart_redo1   400M  2.61G   400M  /oradata/SMART/redo1
app/oradata_smart_redo2   200M  2.80G   200M  /oradata/SMART/redo2
app/oradata_smart_redo3   200M  2.80G   200M  /oradata/SMART/redo3

Since I reused the filesystems I created my database and placed the controlfiles in the same places where the old database files were (/oradata/SMART/ora1,/oradata/SMART/ora2,/oradata/SMART/ora3). Thinking like MySQL works I created CODEapp/oradata_smart_ora4 60.6K 400G 60.6K /oradata/SMART/ora4 specifically to store the database there.

The databases startups and mounts no problem. Note that this server will manage with millions/billions records throughout its lifetime.

1. Now that my Database is created, whats the next step? Create the Schema or Tablespace?
2. Tablespace Questions: Tablespace datafile(s) is where actual data from tables is stored? how many are needed? Default or Temporary? How much space will I need for it? Autoextend?

View 8 Replies View Related

Physical Standby TEMP Tablespaces

Aug 7, 2013

I have a Physical Standby database on server#2 which has its UNDO and TEMP tablespaces in a mount point that is a bit small. I discovered yesterday that this mount point was completely filled. This is not an immediate problem, because even though the TEMP and UNDO tablespaces are large, there is almost no usage of them.

I logged into my Primary, and created a new temp tablespace (TEMP2), made it the database default, dropped the TEMP tablespace, and then re-created the TEMP tablespace in a different mount point (with a much higher capacity!). I then made the new TEMP as the database default temp tablespace again. I expected that these changes would be propagated over to the Physical Standby via log application, but they didn't come over.

I have standby_file_management set to AUTO, and there are no unapplied logs. Everything OTHER than the temp tablespaces seems to be in synch.

View 3 Replies View Related

Partitioned Table Number Of Tablespaces

Jan 27, 2011

I created table that is range partitioned:

CODEPARTITION t1p1 VALUES LESS THAN (TO_DATE('2011-11-01', 'YYYY-MM-DD'))
PARTITION t1p2 VALUES LESS THAN (TO_DATE('2011-11-02', 'YYYY-MM-DD'))
....
PARTITION t1p4 VALUES LESS THAN (MAXVALUE)

Every year partitions will be added for next 12 month. The table partition will be dropped every month (I have to have data from last six month so in July I could drop partition t1p1, in August - t1p2....). How many tablespaces should I create for this table and how place partitions in them to have data for last six month and use minimum space on disk?

I was thinking about one tablespace for whole table because space of each dropped partition will be reused, what do you think about that?

View 2 Replies View Related

Upgrade :: Possible To Transport Tablespaces Without Data

May 22, 2013

We are migrating from 10.2.0.5 to 11.2.0.3 (hp-ux to RHEL). Is it possible to transport tablespaces without data, with just table structures?

(Just like we do in export rows=n)

View 8 Replies View Related

Oracle Auto Extensible Tablespaces

Jan 7, 2011

I need to create a shell script to find the free space of an auto extensible tablespaces and send an alert when the free space is < 700MB. I tried checking with the dba_free_space, but I did not get the exact free space.

Tell me the logic to find the exact free space of autoextensible enabled tablespaces?

View 1 Replies View Related

Query Free Percentages Of Tablespaces

Jan 9, 2013

how can i check using query the free percentages of my tablespaces

View 10 Replies View Related

Real Application Clusters :: Possible To Have Three Undo Tablespaces For 2 Node

Oct 12, 2012

Version: 11.2.0.2/RHEL 5.4

I was under the impression that you need only one UNDO Tablespace for each instance. But , for our 2-node RAC, I can see three Undo tablespaces.

SQL> select tablespace_name,status from dba_tablespaces where CONTENTS = 'UNDO';

TABLESPACE_NAME                STATUS
------------------------------ ---------
UNDOTBS1                       ONLINE
UNDOTBS2                       ONLINE
UNDOTBS3                       ONLINE

SQL> select distinct inst_id from gv$session;

INST_ID
----------
1
2

$ olsnodes -n
thclst214   1
thclst215   2

View 2 Replies View Related

Export/Import/SQL Loader :: EXPDP Excluding Tablespaces

Sep 9, 2013

I am using EXPDP to export a schema (Oracle 11g R2), and I need to exclude all the tablespaces that the schema is using. I have seen exluding Oracle objects like functions, tables, packages, indexes...etc. But I have not seen excluding tablespaces.Iis it possible to exclude tablespaces while creating the export dump?

View 5 Replies View Related

Minimum Extent Error Creating Tables In Empty Tablespaces

Dec 30, 2010

I am trying to restore to a backup instance on a backup server. When I try to recreate the tables I keep getting ORA-01659: unable to allocate MINEXTENTS. The tablespaces and datafiles on both servers show as the same size in OEM.

I have dropped all tables and OEM shows tablespaces are empty. Then I run a script to recreate all tables. Most of the tables don't get created because their TS is full. After the script to recreate all tables runs, the main tablespaces are full, more full than on the production machine. I have also tried ALTER TABLESPACE xxx COALESCE; on each tablespace right after dropping all tables and before recreating them to reclaim free space. Why is it full? I've only dropped and created the tables, there shouldn't be any data in them yet.

ORA-01659: unable to allocate MINEXTENTS beyond 2 in tablespace PLUS_T...The backup instance was already there, all I did was drop the tables. Here's what I ran on prod to build a script to recreate the tables on backup server. Got it off Burleson somewhere.

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) ||'; '
FROM USER_TABLES u;

View 4 Replies View Related

SQL & PL/SQL :: Find Total Space Occupied On Disk By Tablespaces Of Database

Nov 3, 2010

I am trying to find the space occupied on disk by the tablespaces of the database that contain tables, some (and not all) of whose columns are encrypted. My query is like this:

select distinct a.tablespace_name, file_name, bytes /(1024*1024*1024) File_Size_In_GB
from dba_data_files a, dba_tables b,
(select distinct owner, table_name from DBA_ENCRYPTED_COLUMNS) c
where
a.tablespace_name = b.tablespace_name and
b.owner = c.owner and
b.table_name = c.table_name
order by a.tablespace_name;

The output of the query is as shown in the attached file:

TABLESPACE_NAMEFILE_NAMEFILE_SIZE_IN_GB
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0044.DBF29.296875
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0045.DBF29.296875
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0051.DBF29.296875
DMS_DATAN:ORACLEORADATASPOPRODDMS_DATA_0012.DBF19.53125
[code]...

Since the output (under the heading Total Size of the tablespace) is probably the sum of all the datafiles returned by the query and is obviously incorrect, I have not given the rest of it. I also tried the following:

select distinct a.tablespace_name, file_name, bytes /(1024*1024*1024) File_Size_In_GB,
sum (bytes/(1024*1024*1024))over (partition by a.tablespace_name order by file_name) "Total Size of the tablespace"
from dba_data_files a, dba_tables b,
(select distinct owner, table_name from DBA_ENCRYPTED_COLUMNS) c
where
a.tablespace_name = b.tablespace_name and
b.owner = c.owner and
b.table_name = c.table_name
order by a.tablespace_name ;
[code]...

Here, the fig. under the heading "Total Size of the tablespace" are probably the sum of all the records returned by the query if distinct is not used i.e all the data file sizes returned by the query.

tune my query and get the desired results? I think this can be achieved by group by with rollup, cube, order by and grouping functions, but am not sure how to proceed. I know that I can get the results by using Enterprise Mgr. Console in 2 mins., but would still like to get the results with the queries.

View 11 Replies View Related

Enterprise Manager :: List Of Tablespaces And What Tables And Indexes Are In Those Tablsespaces?

Oct 21, 2011

Whats the easiest way I can see a list of tablespaces and what tables and indexes are in those tablsespaces? Can this be done through OEM?

View 3 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 :: Monitoring Script Required For Oracle 9.2.0.7.0 On Windows 2000 Server

Apr 8, 2010

My manager is asking to create a SQL scrip, which will provide following information.

Our database is 9.2.0.7.0 on Windows 2000 Server

-How many records Insert / Update or Delete daily in my Oracle database?

- Check Archive log / Redo Log switching information?

-Check database growth ?

View 10 Replies View Related







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