Server Administration :: Taking Partitions Offline In Tables
Feb 4, 2012
In an attempt to take older data off line and allow database refreshes to be faster, tablespaces associated with partitioned table data for a given time period was taken off line, leaving only tablespaces that relate to the current time period online. In effect, tablespaces related to 2010 and earlier were taken offline from a table.
1. Without giving a filter on the partition key (the business date) to scan for data greater than the dates in the off lined tablespace partition, we get a ORA-376/ORA-1110 error (data file cannot be read at this time).
2. Materialized views using fast refresh or refresh on commit, will also not work because of the partitions being off line.
Queries directly querying the tables are manageable from an application point of view.But the materialized views failing to aggregate is a bigger problem.
how we can manage this situation? I know that I can move the partitions to a different table in a tablespace to be taken off line. But if possible, we wanted to solve this without doing a move partition.
View 2 Replies
ADVERTISEMENT
Nov 24, 2012
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. when I take a tablespace offline, which means I cannot read or write and the tablespace is currently unavailable for users. I am still able to create a table on the "Raja_TBS" while it is offline.
View 39 Replies
View Related
Jan 4, 2012
I have a partitioned table - 128 partitions.
If I am not using data of many partitions in any way, will it affect my performance if I am firing select query that uses other/active partitions data.
How can I check when that partitioned was last accessed, also can I brought those inactive partitions offlie? If we can, what will be the advantages or disadvantages of that?
View 23 Replies
View Related
Oct 31, 2011
I want to drop a datafile in my test db which is in no archive mode,at first, i want to offline the datafile,but it failed,is there any way to do it?
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3237
[code].......
View 8 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
Jul 26, 2010
i'm facing a problem while i'm inserting millions of record from table to table that undo tablespace reach 100% full and execution aborted. , how can free the undo tablespace ??? many of extendes are offline. will it flush automatically ??? or what i should do
View 4 Replies
View Related
Jun 3, 2010
I am studying Oracle® Database VLDB and Partitioning Guide 11g Release 2 (11.2),
ORA-14767: Cannot specify this interval with existing high boundsby giving an example other than given in the above mentioned document.
the example given in the document is as follows:
To increase the interval for date ranges, then you need to ensure that you are at a relevant boundary for the new interval. For example, if the highest interval partition boundary in your daily interval partitioned table transactions is January 30, 2007 and you want to change to a monthly partition interval, then the following statement results in an error:
CREATE TABLE transactions
( id NUMBER
, transaction_date DATE
, value NUMBER)
PARTITION BY RANGE (transaction_date)
[code]/...
View 3 Replies
View Related
Jul 17, 2013
11.2.0.3 This is for a build. We are still in development. No risk of data loss. As part of the build, I drop the user,re-create it, re-create the objects. Allows us to test the build all the way through. Its our process. This user has some tables with several 1000 partitions. I ran a 10046 trace and oracle is using pl/sql to do loops to do DML against the data dictionary. Anyway to speed this up? I am going to turn off the recyclebin during the build and turn it back on. anything else I can do? Right now I just issue 'drop user cascade'. Part of is the weak hardware we have in the development/environment. Takes about 20 minutes just to run through this part of the script (the script has alot more pieces than this) and we do fairly frequent builds. I can't change the build process. My only option is to try to make this run a little faster.
View 3 Replies
View Related
Feb 6, 2013
What is the best possible method to take backup of 50 tables with data ? My DB version is 11gR2
View 14 Replies
View Related
Dec 7, 2012
i have 20 tables in schema and wanted to take count using store procedure.
how to create a sp for taking count of multiple tables
View 5 Replies
View Related
Jul 1, 2010
Initially 2008 to 2010 and 2009 to 2011 partitions are created, After that two more new partitions are created 2008 to 2009 and 2009 to 2010.
How can we move data from old partitions to corresponding new partitions.
View 9 Replies
View Related
Jul 31, 2012
I am exploring the options for file based databases for offline applications where there are no overheads of database installations. We have a system which has Oracle database with more than 500 tables. We need to write a lightweight application (offline) in .net which will run from USB drive and write data to USB drive only.
Existing system has a data layer which writes data to Oracle 10g Enterprise Edition database. The offline application will have more or less similar data entry forms and storage structure. In order to reuse data layer of existing system (stored procedures) can the Oracle Lite be used as a database which can run from USB drive?
I understand Oracle Lite has a suport for synchronization with the server (our main database in this case) by which data can be synchronized with minimum overheads. How the Oracle Lite is licenced if I have 300 odd users of this offline applications. I need to use only database part of Oracle Lite and not mobile server.
View 6 Replies
View Related
Dec 17, 2010
I have installed the Oracle 10g software and created Database my own in RHEL 4. i got the emp tables when i run @?/rdbms/admin/utlsampl.sql with main user system. After i created another user test and i tried to get the tables. but im getting error like table not exist.
View 11 Replies
View Related
Sep 2, 2010
I want to find out the columns on which primary constraints are enabled.Which view must I use to check this.
View 2 Replies
View Related
Mar 23, 2013
I have problem with moving old DB to the new (the same DB 10.2.0 in Win 2003, first in 32 bit, second in 64 bit). I want move DB from 32 to 64 bit. Problem is that all objects in old DB were created in SYSTEM schema by SYS. I can't export that objets (with data) because impdp nor imp don't touch this objects (tables with indexes). I can't use export import procedure. I'm looking for another method to transfer data, which will be the best and the fastest? Maybe files copy on OS? I suppose it will be problems with configuration files, database have other tablespaces.
View 13 Replies
View Related
Jun 14, 2001
I am trying to describe all the tables in a database.We use desc or describe tablename; to describe a table, but what is the command to describe all the tables in a database (i don't need the system tables)
Once i log into sqlplus as a say ABC (SID or HostString) as a user then if i do a desc table name i get column name, data type and null not null etc but i i need that for all the tables in that ABC database..
View 8 Replies
View Related
Dec 13, 2010
I have installed oracle 10g software and created Database on RHEL 4. when i run the run.sql its successively done but im not getting tables. whats the prob.
View 17 Replies
View Related
Aug 30, 2011
create a procedure or cursor to allocate extents to all tables with zero rows for all the user in the database.I have used the below query to check table with zero rows and no extents allocated.
select onwer,table_name,initial_extent
from dba_tables where initial_extent is null order by owner;
I generated the query to allocate extents by using concatenation in the above query.
select 'ALTER TABLE '||table_name|| ' ALLOCATE EXTENT; '
from dba_tables where initial_extent is null order by owner;
now I want the extent allocation for such table auutomatically for aal the tables with zero rows.
View 17 Replies
View Related
Aug 6, 2010
would want to check which tables located in which datafiles.
Tried with this command:
select B.FILE_NAME from dba_segments a, dba_data_files b
where a.header_file=b.file_ID and segment_name='TABLE_NAME'
Seems like it only shows 1 datafile. Some tables are large and i suppose it's resided in more than 1 datafile.
View 1 Replies
View Related
Jan 18, 2013
There is a readonly user on our reporting server. Developers want to use global temporary tables with this user. I don't want the user to have permissions other than readonly.I can grant the user CREATE TABLE privilege and did not grant quota on any permanent tablespace, therefore user would not be unable to create permanent tables but still should be able to create global temporary tables.
Question is would a user with such permissions still be able to utilize temp tables as part of a scheduled job?
View 2 Replies
View Related
May 26, 2011
We want to find out difference of data for some tables between current day & previous day. We can use query with minus operation but it will take lot of time since table size is in range from 200 to 500 GB. We have to do this exercise every day.
View 5 Replies
View Related
Sep 30, 2011
Is it possible to create trigger on the various tables and views exists (i.e. dynamic performance views) in data dictionary, when ever any DML operations performs by Oracle it self?
View 6 Replies
View Related
May 13, 2013
that are my biggest tables
Users use front end (called ESS Console) and when they try to open one of those tables they wait very long (really bad performance). Sometimes the GUI even hanging without displaying results.
Does Partitioned Tables feature works for better performance?
View 3 Replies
View Related
Jan 11, 2012
my user is trying to drop columns, but she gets below error:
SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables
39726. 00000 - "unsupported add/drop column operation on compressed tables"
i just checked whether table is compressed or not, it is not compressed it seems:
select owner, table_name,COMPRESSION,COMPRESS_FOR from dba_tables
2 where owner = 'EQUIPMENT' AND TABLE_NAME = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
EQUIPMENT ETHRNT_VRTL_CNXN_HRLY_AGG_SWP DISABLED
1 row selected. i am able to set one column as UNUSED. and then i am able to see the count accordingly from below view:
select * from DBA_UNUSED_COL_TABS
where owner ='EQUIPMENT' and table_name = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
but not able to drop the unused columns. if i tried to drop a column directly from the table, that also giving above error.
View 7 Replies
View Related
Aug 16, 2012
We are using Oracle 10g and have 10 tablespaces defined for our Database which have 108 tables. Size of 108 tables is around 251 MB as seen during importing the dump. While creating these 10 tablespaces I used below parameters for allocation of space
SIZE 1M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 1M;
which set the initial space for 10 tablespaces to around 1032Kb each. Now my Question is after importing the dump , how the disk space for 10 tablespaces increases to 398 MB in total ?
Is there any relation of Tablespace disk space and Actual Data present in the tables ?
View 18 Replies
View Related
Sep 18, 2012
I am new to DBA activities.I am working on 11.2.0.1.0 version in Oracle.
My database keeps on growing so as a security measure, I am asked to take backup of the database periodically without shutting down this database. Since this task has to be done periodically, I have to schedule the job for this task.? I know how to take export of the database.. but not sure about the periodical backups... Now sure how to proceed on the activity,
View 4 Replies
View Related
Sep 18, 2012
I am working on 11.2.0.1.0 version in Oracle.
My database keeps on growing so as a security measure, I am asked to take backup of the databse periodically without shuttingdown this databse. Since this task has to be done periodically, I have to schedule the job for this task.? I know how to take export of the database.. but not sure about the periodical backups..
View 1 Replies
View Related
Aug 23, 2012
Expdp directory=xxx.dmp dumpfile=aaa.dmp logfile=xxx.log FULL=Y
: :: : : :: : : : ;
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 24.87 MB
Processing object type SCHEMA_EXPORT/USER
[code]...
then my export hangs..... checked in alert log nothing found.and then killed the job and reran again but same....checked the status and it's saying EXECUTING.
View 15 Replies
View Related
Sep 13, 2012
I am try to import 4G dump in Oracle 11R2 version, in that we have around 9000+ Package Body which is taking huge time than other objects (about 8 to 12 hrs) and also it is expecting lots of system space (roughly about 10GB).
I have tried both parallel and non-parallel.how to improve speed of the package body import.
Details about the Schema & Import No. of objects in Schema
SQL> select object_type,count(1) from user_objects GROUP BY ROLLUP( object_type);
OBJECT_TYPE COUNT(1)
------------------- ----------
FUNCTION 248
INDEX 5161
JAVA CLASS 471
JAVA RESOURCE 1
JAVA SOURCE 16
LIBRARY 1
ORA-00933: SQL command not properly ended
View 3 Replies
View Related
Mar 30, 2007
I'm taking export dump using expdp of some schema's of total size is 300GB. This is the par file:
DIRECTORY=expdp
FILESIZE=32212254720
DUMPFILE=expdp_schema01.dmp,expdp_schema02.dmp,expdp_schema03.dmp,expdp_schema04.dmp,expdp_schema05.dmp,expdp_schema06.dmp,expdp_sche ma07.dmp,expdp_schema08.dmp,expdp_schema09.dmp,expdp_schema10.dmp,expdp_schema11.dmp,expdp_schema12.dmp,expdp_schema13.d
[code]....
here one biggest schema size is 250GB and the total size of all the schema's is 300GB. The file where am taking the dump has 350GB space but even then the expdp failed saying
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
why it failed and how to restart it and make sure it runs successfully without error.
View 4 Replies
View Related
