Today i am facing an error when going to rezise the datafile its fixed size is 19000M abut after truncating all tables it is 112M, but when i am going to resize its datafile to 500M its get me an error ora-03297 file contain used data beyond requested size values.
I have done the same before a week without any error.But this time i got the error
I have one generic question about space management. I have one table with size of 1TB. This table stored in ORC1 tablespace. This tablespace contains 70 datafiles.
Since it's 10.2.0.4 database. I have dropped this table by using purge
drop table <<table_name>> purge;
Once table drop was completed. When I check the tablespace space it was 100% free but due to HWM was unable to resize the datafile from current size to small size. What was the reason behind this. Is there any process needs to follow when dropping big tables ? like instead of dropping the tables do I need to truncate first & then drop .
I've been ASM quite a while. I've two diskgroups extdg and nordg which are of external and normal redundancy respectively. Total diskgroup size is 9G, usuable space came down to 3mb when i tried to resize it to itz maximum. the value under usable column shows the same.
FYI... i've totally three disks in nordg, two in one failgroup and one in another.The disk sized equally 3g each.
Quote:
NAME TOTAL_MB FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- --------------
EXTDG 2047 1995 1995
NORDG 9201 3073 3
The command i used to resize all disk to their max is Quote:SQL> alter diskgroup nordg resize all size 3067M;
i got a error temp tablespace cannot be resized. How to Resize Temp Tablespace?
View 10 Replies View RelatedMy system tablespace size is 800mb and it sends an alert saying that it has exceeded 80% of its usage.However its total max size is 30gb.
FYI
Its autoextensible is set to 'YES'.
2 Node RAC database.
File system is ASM
My question here is... is it advicable to resize a system tablespace to some size when autoextensible is set to 'YES'
or genrally can we resize a system tablespace when autoextensible is set to 'No' ?
SQL> col FILE_NAME format a40
SQL> /
FILE_NAME BYTES/1024/1024 AUT MAXBYTES/1024/1024
---------------------------------------- --------------- --- ------------------
+DATA/ge01114/datafile/system.556.2254454 800 YES 30000.9844
I can not drop datafile in a tablespace, how can i do?
SQL> Alter Database Datafile '/u02/app/oracle/oradata/oracl/hxl06.dbf'
Offline 2
3 /
Database altered.
SQL> Alter Tablespace tps_hxl
Drop Datafile '/u02/app/oracle/oradata/oracl/hxl06.dbf'; 2
Alter Tablespace tps_hxl
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace
How to drop a datafile of tablespace.
SQL> alter database datafile '/u01/app/oracle/oradata/oracl/hxl01.dbf' offline drop;
Database altered.The command success,but the dic view show the datafile also.
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
--------------------------------------------- ------------------------------
/u01/app/oracle/oradata/oracl/users01.dbf USERS
/u01/app/oracle/oradata/oracl/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/oracl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/oracl/system01.dbf SYSTEM
/u01/app/oracle/oradata/oracl/hxl02.dbf TPS_TEST
/u01/app/oracle/oradata/oracl/hxl01.dbf TPS_TEST
6 rows selected.
I have oracle 10g running on Solaris with file system and some one created database files with same name but in different directories for example data01.dbf in two different directories, say /u01/oradata/data01.dbf and /u02/oradata/data01.dbf. Now, I want to find out the duplicate datafiles (data01.dbf in this case) sitting in different directories, is there anyway to find this out?
View 11 Replies View RelatedErroneously created datafile, re: "/path/../large_rbs_03.dbf" was created under the SYSTEM tablespace which is supposedly be in the LARGE_RBS tablespace.
How do I make the said datafile be under LARGE_RBS?
I have a database of branch A whose files are located in E: Drive. I want to download it in branch B.I placed all the files of Branch A in D: Drive of Branch B. When i start the database i was getting controlfle error. I made required corrections in initorcl.ora. Now when i start the Database,its mounted, but I am getting ORA-01157: Cannot identify datafile 1. I tried to rename the file, but the "alter database rename file1 to file2;" option is not working.
View 3 Replies View RelatedIs there a way to find out when a datafile for an undo tablespace with autoextend enabled actually extended? I've done a few tests, and nothing is written to the alert log or any trace file that I've found. I can't find any V$ or DBA view that will give me the history of a file's size.
View 5 Replies View RelatedWe need to find when any datafile was resized ( if at all)in a tablespace. Actually, by noting the created date from v$datafile , we used to know the data growth in a tablespace. Now as the number of datafiles have increased, we want to resize them. This diagnostinc have to be done without changing/adding anything in DB.
View 12 Replies View RelatedI mistakenly added a datafile to a tablespace which is asm, however the datafile was created in a default location and not the asm location:
alter tablespace pdaiidata1 add datafile '<filename>' size 2048M;
What I should have done:
alter tablepsace <tablespace_name> add datafile '+DATA1' size 2048M;
Is there any way to move this filesystem datafile into the asm tablespace? In previous Oracle versions, I've taken a tablespace offline, moved a datafile, renamed it, then brought the tablespace back online. Can I do something similar here in this situation?
This facility has one last 10g database and a very problematic tablespace and last datafile associated with it. The tablespace was set up with INITIAL_ EXTENT of 131,072 (128K) instead of the more 'normal' 4,194,304 (4M) and NEXT_EXTENT of 262,144 (256K) instead of 4,194,304 (4M).
More worryingly, the datafile has INCREMENT_BY set to 1 (8K) instead of 1,280 (10M) or 2,048 (16M).Has anyone ever updated sys.ts$.dflinit and sys.ts$. dflincr to modify the INITIAL_EXTENT and NEXT_EXTENT, and sys.file$.inc to modify the INCREMENT_BY?
While renaming the datafile.
1) ALTER TABLESPACE .... OFFLINE;
2) CHANGING THE DATAFILE NAME IN OS LEVEL;
3) ALTER TABLESPACE .... RENAME DATAFILE 'FILE_OLD' TO 'FILE_NEW';
4) ALTER TABLESPACE .... ONLINE;
In the above steps, I HAD FORGOTTON TO FOLLOW THE 2ND STEP.
At the step of 3rd, i got error message, now i am not able to change the name in OS level Also.
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].......
what's the difference between having BIG datafile (32GB) vs many small datafiles (4GB) replacing a Big one.
View 3 Replies View Relatedpost some script which will find out the used space of a specific datafile.
View 5 Replies View RelatedI need to resize my datafile as i have allocated more space and need to reduce ( i.e.data load completed now). my tablespace is having 11.74 gb free space now. it has 3 datafile.
TABLESPACE TOTAL USED FREE PCT_FREE LARGEST FRAGMENTS
------------------------ ---------- ---------- ---------- ---------- ---------- ----------
CFC_DATA 150528 138780.6 11747.4 7.80412946 1251 992
TABLESPACE_NAME FILE_ID FILE_NAME Size(MB)
------------------ ---------- ------------------------------------------------------- ----------
CFC_DATA 71 +DATA/dedw/datafile/cfc_data.4074.731085435 65535.9688
CFC_DATA 334 +DATA/dedw/datafile/cfc_data.4473.757566557 20480
CFC_DATA 1710 +DATA/dedw/datafile/cfc_data.2012.728095695 64512I used below script to find out HWM in order to resize the datafile.
db_block_size is 16KB.
[code]....
in TOAD, we have an option, that is "Minimum size" button against each datafile.. Need the SQL which is running behind when we press this button from TOAD ?
I have create one table emp under scott schema, and this schemas default tablespace is USERS and this tablespace has 3 datafiles.
Now how do I identify the object EMP is residing on which datafile in USERS tablespace?
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.
I want to remove a tablespace with it's datafile.
I've ran: DROP TABLESPACE x_tbs INCLUDING CONTENTS;
I've read(should have read it prior to action) [URL]tm
and I understand now that I should have stated "AND DATAFILES", However, too late now. the tablespace is removed, but the datafile x_tbs is still present in a folder of my OS.
Is it safe to manually remove it by deleting it from the operating system?
what is the Minimum datafile size required for creating a non-system tablespace?
I am trying to create a tablespace by giving the syntax like,
SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 72k;
create tablespace t1
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 73k;
Tablespace created.
The blocksize for my database is 4096, as i have heard that the minimum size of the datafile is decided by blocksize, but i want to know that how it is calculated as by giving the above syntax the other values will be default. I am trying the syntax in oracle 9.2.0.1.0 version.
i have a tablespace which contains 121 datafile(max limit reached) as a dba what we have to do?
creating a new tablespace with a datafile and assign the users to the current tablespace which i created now.iif the above process is correct,after some time the tablespace which was filled up got freed up.now can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces
I have Tow Virtual Servers (Connect As Cluster) in the Same Computer The Files In the Drive C Different (of the Tow Servers) And Others Like (e,f,q) The Same Files
And There is Database of both Servers That Have Same Name Let say orcl and both database Have same Schema let Say sch
How I can Share The Data when i am insert to table. in the schema sch in the server A i can see Same Data That I have Inserted In the Schema sch in the server B.
1) can we use a CSV file as a Data file in any format (fixed, delimited...) of Sql loader. I tried, but not succeeded.
2) if not then tell me the reason for it....
3) Also tell me is there any restriction on using the file format for a datafile?
i have exp dump of size 1gb but when i tried to imp ,it showing error of space , it asking for space of 4gb. But i have 1gb on c: drive and 32gb on d: ,can i add datafile on d: locaion and what is max size i can assign to that datafile .
View 4 Replies View Relatedbelow query:
1) What are the terminators we can use and cant be used, except ','. eg - '|' - this shows error.
2) What are the file format can be used in DATAFILE other than txt file. Eg - excel, word... And how to terminate the values in that files...
When I am importing, I get these errors
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (XXXXXXXXXXXXXXXX) violated
Column 1 2
Column 2 OFFLINE
[code]....
I added a datafile in undo tablespace (its an ASM database). I doubt that since I added the datafile to undo tablespace, I am getting this error.
I have an excel table whose data i have to transfer in oracle.
I did the following steps:
1) converted excel data in CSV format.
2) Created a control file test.ctl
LOAD DATA
INFILE 'C:IDB Price List.csv'
BADFILE 'C:IDB Price List.bad'
DISCARDFILE 'C:IDB Price List.dsc'
INSERT INTO TABLE idb_price_list
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(group_no,type,description,amt,flag)
3) when i run the sql loader utility, i get an error "sql-524: partial record found at end of datafile"