Server Administration :: Allocate Extents To Tables With Zero Rows
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.
We are migrating from a 9i db to 11g and we've been testing our apps on a similar (but not exact) machine as our production box.
Normally when we take a full export of the production data (on 9i) and import it into another 9i DB, the tables and indexes are created with the initial size large enough to hold the entire table. We also do our export with the compress extents param set to 'Y'.
However, we've noticed that when we import our data into the 11g DB, that tables are being created with multiple extents...sometimes up to 10 or 15. This seems to happen even with tables that don't even have extents on db that the export was taken from.
There ARE some differences in our 11g DB that i imagine might be the culprit, i've just been unable to narrow one of them down.
the differences i know of are:
a) the target DB has locally managed tablespaces while the source 9i DB had dictionary managed tablespaces b) the block size is larger on the target 11g DB. 8192 vs 2048 c) the nchar character set on the source DB is AL16UTF16 and the target is UTF8 (we actually only have an nchar column in one of our tables...and also, the UTF8 setting was actually a mistake that we're correcting this weekend with a fresh DB and fresh import)
What would cause the import to produce all these extra tablespaces?
When we are using pga_aggregate_target and sort area is automatically managed by Oracle. Why oracle is not allowing for auto allocating extents in TEMP and still extents of uniform size of 1 MB is used.
I have installed Oracle 10g on my PC and create a Database on it. I wanted to connect it with PL Sql Developer with the same PC but it`s not connecting. It gives the following error "ORA-01019: unable to allocate memory in the user " And some time blank error message box.
I have a package when it runs, it terminates with "unable to allocate 4120 bytes of shared memory"error at a particular INSERT statement. So everytime we had this error, we had to flush the shared pool area, after which it would run without issues(for 3-4 weeks).
It had a dynamic subpartition clause framed, which made it to repharse the query every time.So to reduce this, we removed the subpartition clause(made it to static query), which did benefit of not getting shared pool error.
I would like to find the current shared pool memory usage of my program.Is there a way I could find the live consumption of shared pool area of my program(pkg).
I am trying to insert rows in two tables using sql loader.
I have two tables in database as
SQL> desc name Name Null? Type ---------------------- -------- ------------ ID NUMBER NAME VARCHAR2(20) BD DATE
SQL> desc name3 Name Null? Type --------------------- ----------- ------------- ID NUMBER NAME VARCHAR2(20) BD DATE
I created controlfiles as
[oracle@DBTEST sqldri]$ cat datafile.ctl options (direct=true) load data INFILE * into table name truncate when id='1'
[code]....
when i run sql loader as
[oracle@DBTEST sqldri]$ sqlldr hr/hr control=/u01/sqldri/datafile.ctl SQL*Loader: Release 10.2.0.1.0 - Production on Tue Aug 7 23:30:07 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. Load completed - logical record count 2.
no rows is inserted..the log file contain entries as
[oracle@DBTEST sqldri]$ cat datafile.log SQL*Loader: Release 10.2.0.1.0 - Production on Tue Aug 7 23:30:07 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. Control File: /u01/sqldri/datafile.ctl Data File: /u01/sqldri/datafile.ctl Bad File: /u01/sqldri/datafile.bad
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.
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.
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..
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.
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.
The table tb_hxl_hash have two partitions,when i exchange the second partition,it raise error,why?
SQL> Alter Table tb_hxl_hash 2 Exchange Partition P_HS1 With Table tb_hxl_test;
Table altered.
SQL> Alter Table tb_hxl_hash 2 Exchange Partition P_HS2 With Table tb_hxl_test; Exchange Partition P_HS2 With Table tb_hxl_test * ERROR at line 2:ORA-14099: all rows in table do not qualify for specified partition
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?
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.
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?
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?
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 ?
settings I can make for Initial and Next extents? I am working at a client site where they have an in-house E-Business Suite database that is running very fast. In most cases the initial extent on the big tables is set to something like 64Mb with the next extent set to figures varying between 25Mb and 50Mb. What this means is that we don't have many extents.
They have recently outsourced their database to Oracle on Demand and are getting poor performance. Over at OOD the initial extents remain as set in the local database - taken from a clone. However, all of the next extents are set small to nimbers like 128Kb. Some of the big Oracle EBS tables have more than 128Kb in a single record and we are seeing many 1000s of extents already.
Could the large number of extents and the small size of the next extent be the cause of a substantial decrese in performance? I'm looking for some justification to go back to OOD with a view to asking them to reinstate the settings as they were in the client database before it was migrated.
I am looking to create a DDL-only clone of a 2TB database. The purpose of this clone is to provide developers a preprod-like environment where they can test the syntax of their code changes before deploying to production. The database will reside on a seperate, small server.
As I only need the structure of the objects and not the data, my first port of call was EXP using rows=n. However, when I IMP to an indexfile I see that the initial extents have been defined on the tables (in some cases 1GB!). As stated my clone environment is small, so I would be looking to have these set at 1mb. how I can achieve this? I will want to be able to automate the cloning of the database as much as possible. Perhaps there's some other way other than EXP/IMP?
I am using the SQL-Developer to access and manipulate a database. I am not very sure about the format of the database (I'm new to databases), but I had to setup the TNS-folder.
Anyway, I guess the problem is the same for any database.
I am having a table with the BOM (bill of material) positions of certain articles and I want to change the BOM quantities of some of the articles. What happens is that I can only change some of the rows. For other rows I get the message like (it is in German, so I try to translate it):
"data was commited in another/the same session already. row cannot be updated"
This error message looks like there is somebody else locked on the database and manipulating it, correct? Is that possible to see somewhere which processes/people are currently accessing to the database?
I saw that there is one process/another database, which is having the authorization to access to the database. But where can I check if this process is accessing to the database?
BTW: I used to do this process before, and it worked. I had been able to manipulate arbitrary entries on the database. I guess that the process or the person, mentioned above, hasn't been accessing to the database at that time.
I have the task that I have to determine the number of parts that need to be produced based on the number of products sold for the day (each product consists of many parts).
I am using SQL 11g Express.
The report would look something like this:
{OrderDate PartID PartDesc NumOfParts(Total for that day) 10-24-2011 2001 12" X 12" Solid Shelf 108 10-24-2011 2003 12" X 24" Solid Shelf 32 10-24-2011 3001 96" Side Panel 50
[code].......
My issue is, I can't get the equation right to produce the total number of parts. I think I need to multiply ProductPart.NumOfParts by SUM(CustOrder.Qty) Group by CustOrder.SKU.
Below I have what the calculations should look like
