I have a tablespace PLUS_IX with 5 datafiles PLUS_IX_01, etc.
PLUS_IX_04 is 85% full, up from 78% last month
PLUS_IX_05 is 22% full.
PLUS_IX_01 to _03 are 73% full.
Should I be concerned that PLUS_IX_04 is getting full? If so, what actions are recommended?
ALTER INDEX ..... REBUILD has been run on all the indices.
I've got table at Oracle test server table1 with columns ID, BTYPE, MYDESCRIPTION. Rows of this table have been inserted manually. Now my need is to write script for creating table (structure + data) for working server. I think about writing something like
CREATE TABLE table 1
AS
SELECT 1 AS ID, 'TYPE1' AS BTYPE, 'SOME TEXT' AS MYDESCRIPTION
UNION ALL
SELECT 2 AS ID, 'TYPE2' AS BTYPE, 'SOME TEXT 2' AS MYDESCRIPTION
But rows are too many to type... Any way of generating script for creating table at working server using existing table at test server? The problem is I don't have an access to working server.
I`m new with Oracle Forms. I have the following trigger WHEN-BUTTON-PRESSED :
DECLARE
BEGIN
IF :SEARCH.SBOX is not null then -- (search text item)
IF :SEARCH.LIST45 = 'ERPLINK' THEN --(a drop down list)
[code]...
What do I want is the following thing :I have a text box :SEARCH.SBOX in which I write a number.When I press the button which will execute that trigger above, I want that the value from :SEARCH.SBOX, after the form will go to enter-query mode tofill up another text_item :CUSTOMERVIEW.ERPLINK with same value and execute_query(this text item is in master-detail form).
We have a master detail form block A (master) contains an item, this item is in the record group's where-clause used to fill a list item on block B (detail block) (database item) we fille the list item in a pre-record trigger on the master block this works fine but when we navigate through the records in block a we intermittently get the following error : FRM-41337 : cannot populate the list from record group;
how should we filll our list item, in which trigger (when-new-record-instance of master block doesn't work correctly, the list item only gets filled after clicking it twice)
what is the reason for filling the folder "Oracle Jar Cache" with some files? Why should we clear the cache ? My clients are also facing same issue. When they use the application continuously(in forms9i) after some time, application getting hanged. When we follow these steps its working fine:
- close all IE
- and clear the cache ,
- then re-start machine
I have created a table like below-
PROMPT CREATE TABLE tst_fetch_vendor_data
CREATE TABLE tst_fetch_vendor_data (
vendor_data_seq_no NUMBER NOT NULL,
study_seq_no NUMBER NOT NULL,
vendor_record_seq_no NUMBER NOT NULL,
control_column_seq_no NUMBER NOT NULL,
resolved_value VARCHAR2(4000) NULL,
original_value VARCHAR2(4000) NULL,
transaction_user VARCHAR2(30) NOT NULL,
[code]....
Its just a temporary table, in which data comes and goes. I am using this in middle of a process.I am using it in a process like below--
--EXECUTE IMMEDIATE 'TRUNCATE TABLE TST_FETCH_VENDOR_DATA DROP STORAGE';
insert /*+ append */ into tst_fetch_vendor_data
(select * from vendor_data vd
where vd.control_column_seq_no in
(select control_column_seq_no from temp_control_column));
dbms_stats.gather_table_stats('EPDSYSREP','TST_FETCH_VENDOR_DATA',ESTIMATE_PERCENT=>100,
METHOD_OPT=>'for all indexed columns size auto',CASCADE=>True);
code to use that table..This table can contain data from 0 to 108000000 records.Now my questions are-
1. How much should I select sampling size (currently its 100%)Can I use dbms_stats.auto_sample_size, what will be the effect?
2. dbms_stats is good approach or should I use dynamic sampling.
3. what about the approach using CTAS instead of inserting data through insert.
4. What about pl/sql table with index or with clause query.
5. Do I need to rebuild index after inserting data into table.
I read this article:
[URL]......
Does it work with copying datafiles from 10g2 to 11g2? I want to move one huge tablespace (which contains one table) from 10g2 to 11g2, what is the best method to do that?
Basically , we have a large index tablespace , whose data files already spans multiple mount points , and has been extended with a new data file on a wrong mount point by mistake , Can we relocate this last datafile extension , to a different mount point while the DB is online ? Is there any special consideration for index datafiles (If such classification is correct ) or is it just like any other Oracle non-system datafile ?
What will be the effect on the normal data tables which are using this index if they are being accessed during the exercise? This is a 2 node Oracle 9i 9.2.0.8.0 RAC on Veritas Cluster .
How to move the datafile from e-drive to d-drive in oracle 11g?
View 7 Replies View RelatedI am looking for a way to monitor the creation of new datafiles / tempfiles.Let's say I am creating a new datafile of 8GB in size, and it takes a few minutes to create.
I have looked at v$session_longops, and several other system views, but cannot find any that indicate how the file creation is progressing.
I want to be able to display something simple, such as "72% complete".
i want to delete one datafile from tablespace witch contain 2 datafiles
i did
ALTER DATABASE DATAFILE 'ton_fichier' OFFLINE DROP;
and delete the file physiquly but i have already my datafile witch i have delete in my tablespace
11.2.0.2 on Solaris..I have such a large post on a very basic space calculation.
We have several tablespaces starting with WLMCS in our DB..I just wanted to calculate the total space consumed in the disk by all these tablespace combined .When I queried DBA_DATA_FILES.MAXBYTES and DBA_DATA_FILES.USER_BYTES , I've noticed that ,
When AUTOEXTEND is NO: MAXBYTES is 0 for these datafiles . But USER_BYTES won't be 0 for these files
When AUTOEXTEND is YES: MAXBYTES will be a non-zero value for these datafiles . USER_BYTES won't be 0 either for these files-- Not including datafile names for better readability.
SYS > select tablespace_name, maxbytes/1024/1024, user_bytes/1024/1024, autoextensible from dba_data_files where tablespace_name like 'WLMCS%';
TABLESPACE_NAME MAXBYTES/1024/1024 USER_BYTES/1024/1024 AUT
------------------------- ------------------ -------------------- ---
WLMCS_DATA 32767.9844 32766.9375 YES
WLMCS_DATA 32767.9844 31615.875 YES
WLMCS_DATA 32767.9844 16419 YES
WLMCS_OAT_DATA 32767.9844 32766.9375 YES
[code]....
11 rows selected.To calculate the space consumed , I made 2 assumptions.Are the below 2 assumptions right?
Assumption 1. Whenever MAXBYTES = 0 , USER_BYTES should be considered for the space calculation.
Assumption 2. Whenever you have non-zero values for both MAXBYTES and USER_BYTES , MAXBYTES should be considered for the space calcuation.I did the calculation (adding up of) based on the above assumptions. Is this calcualtion Correct ?
-- Not including datafile names for better readability.
TABLESPACE_NAME MAXBYTES/1024/1024 USER_BYTES/1024/1024
------------------------- ------------------ --------------------
WLMCS_DATA 32767.9844 32766.9375
WLMCS_DATA 32767.9844 31615.875
WLMCS_DATA 32767.9844 16419
WLMCS_OAT_DATA 32767.9844 32766.9375
[code]....
Due to improper documentations of a certain project, I need to drop a DEFAULT tablespace of a newly created instance including it's associated datafiles by using this command:
"DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES;"
The default tablespace name is QWER (qwer01.dbf) and I added 2 datafiles in it, re: OPD_SML01.dbf & EXYT_SML01.dbf.
Do I have to do it online or offline?
Can we restore a database if we have only the datafiles available? The database has been dropped but we have just the datafiles with NO system, sysaux,users and undo datafiles that were saved after taking the database offline and then dropping the database. I know this is strange but if possible, I want to see if we can retrieve some of the data from those datafiles to another database. BTW, There is no backup available for this database except the dbf files on OS.
View 3 Replies View RelatedCan we add a datafile mapped to a shared network drive in windows server.
View 1 Replies View Relatedwhen i install oraclexe on my system..when i want to connect the oraclexe then it give error
D:oraclexeapporacleproduct10.2.0serverBIN>net start OracleXETNSListener 'net' is not recognized as an internal or external command, operable program or batch file.
D:oraclexeapporacleproduct10.2.0serverBIN>net start OracleServiceXE 'net' is not recognized as an internal or external command, operable program or batch file.
D:oraclexeapporacleproduct10.2.0serverBIN>c
when i check this path D:oraclexeoradataXE then i check datafiles not generated in this folder
How to distribute the datafiles among various disks of a server, so that I/O operations are improved.
I mean is there any area we need to focus while distributing the disk like:
1)Particular types of datafiles should be placed in one disk.
2)Temp/Undo/Rollback tablespaces in another disk.
3)Indexes in another disk
Just noticed MR(media recovery lock ) in our database,further check showd all datafiles had this lock since our database was shutdown abortly but was startup gracefully.
View 2 Replies View RelatedWhat is best practice to change small disk D:? I am beginner with Oracle. 10g on W2008. 5 datafiles (all indexes,second data file, 2 undotabs)*.dbf (34;30;1;34;12 GB) is on D:. Part of tablespaces (1 data, 1 undo)has files on c:.
I.
1.Shutdown 2008 server.
2.Copy D: image with GHOST to USB, network.
3.Connect new D, create RAID.
4.Restore image to D.
5.Start 2008 server.
II.
1.Stop application.
2.CONNECT AS SYSDBA
3.SHUTDOWN NORMAL or (IMMEDIATE)?
4.Copy files *.dbf at OS level from d: to ... USB disk, network.
5.Shutdown 2008 server.
6.Change disks, create RAID in BIOS.
7.Start W2008.
Is Oracle at this moment in SHUTDOWN mode?
8.Copy back *.dbf to new D: (with directory structure).
9.STARTUP Oracle.
I need a PL/SQL stored procedure which will accept a datafile name as parameter and dynamically create and execute "alter tablespace" command to add this passed datafile dynamically.
View 6 Replies View Relatedi encoutered lately on such statement:
Table D-7 shows a hierarchical file mapping of a sample OFA-compliant installation with two Oracle home directories and two databases. The database files are distributed across three mount points, /u02, /u03, and /u04. URL....
why three mount points (/u02, /u03, and /u04) for datafiles? don' understant this. isn't it better to store all datafiles in one mountpoint?
I am using 11.2.0.1 database and I have all my datafiles,control files,redo logs in /home/oracle/oradata and i would like to completely move all the files to /data/oradata folder.
What is the best method? (without losing old backup's , without reset log , no new incarnation,no new DBID) can it be done online /offline?
Is it recreating control file with new location and open database without resetlog the best method?
I'm testing out various RMAN restore/recovery scenarios for 10g R1. Took a full level-0 backup of one of our larger databases (16 tb) and I'm trying to restore the whole thing on another server. While the backup was running (for about a day & a half), a few datafiles were added. The backup completed fine, including "backup current controlfile;". Now my restore is failing with "RMAN-06023: no backup or copy of datafile xxxx found to restore", with reference to the 4 files that were added while the backup was in progress.
I guess if the datafiles were added after the backup had finished, we'd be fine because they'd be created during recovery with our archive logs (we have all of those).
I found some suggestions for first doing a "restore controlfile;" -- did that and it worked fine -- then mounting the database and creating the datafiles manually. I added that to my RMAN restore script and tried it, like so:
run {
allocate channel d4 type disk format '/BK01/dbX/stream1/df_%t_s%s_s%p' ;
sql "alter database create datafile ''/uXX/oradata/dbX/datafile_58.dbf''";
restore database ;
}
The datafiles were created successfully, and the restore began .. but then it failed with this:
creating datafile fno=4216 name=/uXX/oradata/dbX/datafile_58.dbf
...
RMAN-03002: failure of restore command at 06/20/2013 17:27:28
ORA-01119: error in creating database file '/uXX/oradata/dbX/datafile_58.dbf
ORA-27038: created file already exists
So RMAN seems to be telling me that if these 4 datafiles don't exist in the backupset, then RMAN won't automatically create them. But if I manually create them first, then RMAN will try to restore them and complain that they already exist. And for that matter, I would have expected that RMAN would be smart enough to detect that those 4 files were added while the backup was active, and just include them in the backup. Maybe there's something I needed to do to tell RMAN to do that?
This is just a test, so of course I can redo the backup from scratch and try again, but we have some pretty large databases which cannot be shut down for cold backups, so it's entirely possible this scenario could show up in a real disaster recovery. RMAN does such a good job of managing things.
I have checked the space of my tablespaces/datafiles in my database. I have 8 GB space left in my database server. I cant add more hard-disk as there is no slot left. We r planning to buy a new server with latest config.
My question is, how can we know upto what size our database can increase and when a datafile need to be added in advance. Sometimes even though datafiles have space left,it shows errors abt extents cannot be extended. We have coalesce the tablespaces and added a new datafile.
I am facing an issue while rebuilding a DR (Physical Standby) site using RMAN.Rman is failing to restore all the datafiles to the stanby site.
There are 403 datafiles that should be restored among which after restoring 50-60 datafiles the channel is abruptly getting terminated.Please note this exercise is not new I have done this many times (once a year after a DR testing) and the same script is being used.The rcv file is as follows
/u03/oracle/admin/mpr/scripts/dataguard/configure_dr.rcv
connect auxiliary sys/XXXX@YYY
run {
duplicate target database for standby nofilenamecheck dorecover;
}
exit
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
configure_dr.rcv: END
[code]....
The trace file says "krso_proc_add: Detected dead process 13828326; subsuming V$MANAGED_STANDBY slot
INFO: krsr_rfs_atc: RFS processes are temporarily disallowed"
I have also started RMAN debug tracing and the trace is also scanned as it is huge.I have seen the following in the trace error 3015 signalled during compilation..I do not find any specific oracle error to check on .
The only change that happened is that the Primary production server has been moved to a new datacentre and after its move all the connections seems to be working fine.
We have two tablespace xyz_ts & pqr_ts having 167 datafiles . We have to reduce this number of files. These tablespace having 580 GB size .
View 1 Replies View Related 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.
what's the difference between having BIG datafile (32GB) vs many small datafiles (4GB) replacing a Big one.
View 3 Replies View RelatedWe have lost some datafiles and some archivelogs. We have backup of whole database taken on Dec 20 and archivelogs until Dec 23.So we dont have 1 day worth of archivelogs which can lead to incomplete recovery.
Now question is do we need to restore all datafiles or only lost datafiles for incomplete recovery since we dont have all archivelogs ? lost datafiles belong to different tablespaces.
Datafiles which are still intact are with current SCN and we restore lost datafiles only and recover unitl our last available archivelog it will have different SCN. Will we be able to open database or we need to resotre whole database for this scenario?
usually i create oracle databases using the create database command, however im trying this time to use the dbca -silent -createDatabase -templateName General_Purpose.dbc - i have edited the General_Purpose.dbc to have the control files. datafiles etc to go to a different location other then the $ORACLE_BASE and no luck so far, whatever i do even changing the $oracle_base just dumps all into a location, how can i force it / change it - this is Oracle 11GR2 on linux
see sample of my General_Purpose.dbc
<initParam name="control_files" value="/u01/oradata/control01.ctl, /u02/oradata/control02.ctl"/>
<initParam name="diagnostic_dest" value="/backup/oracle/oradata"/>
[Code].....