i got a table and it had 5000 rows of data...ive deleted around 2000 to decrease the db size but i have no success. My harddrive is still showing the same size with no increase in mb.
I've looked at shrink etc methods but some are not compatible with 8i.
I take it the db is still reserving that those deleted rows thinking it may be used again which is the reason for no increase in space.
We have Oracle 10G database over Unix platform, Customer want to reduce the size of database as much as possible and the ami of customer to move the storage area of this database to other one. so we resize some datafiles and get lots of free space at mount point but while checking the utilzation of table is showing some what different as other. Below O/P:
% % MaxPoss Max Tablespace Name KBytes Used Free Used Largest Kbytes Used ------------------------------ --------------- --------------- --------------- ------ --------------- --------------- ------ *a DATA 45,875,200 8,740,992 37,134,208 19.1 1,728,512 100,663,248 45.6 *a HIGH_S_DATA 21,504,000 1,331,520 20,172,480 6.2 3,048,704 0 .0 *a HIGH_S_IND 15,360,000 853,568 14,506,432 5.6 1,661,504 0 .0
[Code]....
above all o/p is different, no able to understand it. is there any way to reset the HWM at Datafile level and how we reset the HWM of those tables having Materlized view?
I am getting the following error when i try to re size the data file.But the data file is having lot of free space.
ORA-03297: file contains used data beyond requested RESIZE value
Presently table space size is 220GB with 8 data files.As the process of the performance tuning we moved data to different table spaces.Now used space of the tablespace is 90GB.So I am trying to resize the datafile but it throw an error.
how can I reduce the size of ------------- when table is null. I m in sqlplus I typeSelect A,B,C,D,F,G,H from SOMEHERE where B='GOAT1';
if A is 10 char long B is 50 c is 10 d is 30 e is 10 f is 50
if any of those don't have data it still outputs ----------------------------- (50) for B and tht covers the whole screenhow can I make is to show less if it null
DB: 11.2.0.3 & OS: RHEL5 Easy Connect Naming method enhances the host naming method by allowing for a port and service specification. My question is does Oracle 11g support Host naming method?
The problem is that this connection errors when I try to connect using my SQuirreL client. My developers want to connecting using the SID or the SERVICE_NAME and by using the "New Method" syntax, not the "Old Method" syntax. According to the documentation the "New Method" syntax works with the SERVICE_NAME or the SID. The "Old Method" only works with the SID.
# Host name is vmwwin7634.na.SAS.com # SID is sting2 # SERVICE_NAME is sting2.na.sas.com
PROBLEM using SID errors ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2 THIS WORKS using SERVICE_NAME ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com
I am using SQuirreL Client version 3.4.0
# Here is the tnsnames.ora entry on the server. STING2 = (DESCRIPTION =
[code]...
# In this documentation it says, "On new syntax SERVICE may be a oracle service name or a SID."You can find this on the orafax wiki under JDBC#Thin_driver.
# I was able to connect with the "old method" using the SID only, SERVICE_NAME errors jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2.na.sas.com (SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.") jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2 (this works)
# "New Method" jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com (this works) jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2 (this errors) (SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.")
i have data about 3 gb but my files in e:oraclexeapporacleoradataXE has grown to 16 gb
1)my e: drive has less space ,pls tel how can i fee some space and give it back to OS
2)is there any other place where i can free up some space from oracle and give it back to OS.
----------------------- windows (2008) --- Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production PL/SQL Release 11.2.0.2.0 - Production "CORE 11.2.0.2.0 Production" TNS for 32-bit Windows: Version 11.2.0.2.0 - Production (2008) NLSRTL Version 11.2.0.2.0 - Production
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.
We have a requirement to update one column as given in the below mentioned pl/sql block.The challenge is to update the a large volume of data every day.Generally this kind of one to one update will takes long time (45 min approxmately) We need a solution that will be able to update 10 million records in few minutes.
begin for i in (select t3.event_id, t1.header_id from table_1 t1, table_2 t2, table_3 t3 where t1.header_id=t2.header_id and t2.entity_id = t3.entity_id) loop update table_1 set event_id = i.event_id where header_id =i.header_id; end loop; commit; end;
1) CREATE TABLE mutu (id NUMBER, text VARCHAR2(20));
2) INSERT INTO mutu SELECT rownum , 'mutu' FROM dual CONNECT BY LEVEL <= 10000;
3) Insert Duplicate row. INSERT INTO mutu VALUES (42, 'DUPLICATE');
4) COMMIT;
5)Create a NON-UNIQUE INDEX's CONSTRAINT as below. ALTER TABLE mutu ADD CONSTRAINT mutu_pk PRIMARY KEY(id) USING INDEX(CREATE INDEX mutu_pk ON mutu(id)) ENABLE NOVALIDATE;
6) Insert Duplicate row. INSERT INTO mutu VALUES (42, 'DUPLICATE');
7) ERROR at line 1: ORA-00001: unique constraint (SYS.ZIGGY_PK) violated
Why it shows the error. Even though I had created a NON-UNIQUE INDEX's Constraint only.
is there any way to reduce the dblink timeout, i need to test if the remote server is up by testing the dblink throw 'select 1 from dual@link_name' but it take too long time 5-6 min., i need to make it 5 sec after that the exception tns timeout appear. I try with some sqlnet.ora parameters like INBOUND_ TIMEOUT but not work for me, also i try using tnsping but it also take long time when the remote server is down.
how to reduce quantity from one table due to other table for example .i have a purchase a shampoo in 20 quantity and then sale this shampo 2 quantity...so i want now there should be 18 quantity of shampoo in purchase table automatically
Purchase tableSale tabel Product IdSale id Product Nameproduct name QuantityQuantity
I have 2 tests Oracle instances (a 10g and a 11g) in which somebody added many users (almost 50k), every user having a TEST table with no rows in its schema.
I have to drop these users without recreating the instances, but DROP USER username CASCADE is taking almost 1 minute for each user. This will take me to almost a month.
How to find out the status of the import job(how much work is done, ETA), i have started the job few days back it still running on the server. i am using import not datapump, because i got the dump which took with export. DB version is 11.2.0.2.0
for datapump i know i can use the following queries, but these are not giving any output l think these are only for datapump
select sid, serial#, sofar, totalwork from v$session_longops; select sid, serial# from v$session s, dba_datapump_sessions d where s.saddr = d.saddr;