Server Administration :: Renaming Datafile - Change Name In OS Level Also

Nov 3, 2012

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.

View 19 Replies


ADVERTISEMENT

Server Administration :: Trying To Change Tablespace And Datafile Properties On Fly

Aug 21, 2011

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?

View 7 Replies View Related

Server Administration :: Renaming A DB?

Mar 10, 2011

I would like to try to rename a newly created dB. Referring to the topic posted on your website[URL]... had some clarification before executing the dbrename.sql mentioned there:

After modifying the the init.ora and tnsnames.ora, do I have to rename all OLD, existing directory path (for controlfiles, datafiles, etc.) with the new-- meaning I don't have to create another directory for it's 'NEW' name?

View 8 Replies View Related

Server Administration :: Renaming Listener Log_file On 11gr2?

Nov 9, 2012

LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 09-NOV-2012 19:55:06
Uptime 0 days 0 hr. 24 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:oracleproduct11.2.0dbhome_1
etworkadminlistener.ora
Listener Log File c:oraclediag nslsnrTESTQ51listeneralertlog.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NDCORCKSDQ51.global.loc)(PORT=1521)))
Services Summary...
Service "testdb" has 1 instance(s).
Instance "ksdtest", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully

i am trying to rename the logfile
"c:oraclediag nslsnrTESTQ51listeneralertlog.xml" to "c:oraclediag nslsnrTESTQ51listeneralert estdb.log"

so far i tried

LSNRCTL> set log_file testdb.log
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-01251: Cannot set trace/log directory under ADR
LSNRCTL>

View 3 Replies View Related

Server Administration :: Automated Shell Script For Renaming Oracle DB 10gR2

Nov 8, 2011

Provide automated shell script for renaming oracle DB 10gR2. Now it is a daily manual job for us.

View 4 Replies View Related

Server Administration :: How To Drop Datafile

May 25, 2012

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

View 5 Replies View Related

Server Administration :: How To Drop A Datafile

Dec 31, 2011

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.

View 5 Replies View Related

Server Administration :: Duplicate Datafile Name

Feb 29, 2012

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 Related

Server Administration :: Resize Datafile

Mar 21, 2011

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

View 5 Replies View Related

Accidentally Deleted Empty Datafile At OS Level / Have No Backup Of It

May 2, 2013

Oracle 11G...After creating a new datafile, it was accidentally deleted at OS level (AIX).Because it was new there are no backups of it.Now tablespace thinks there are 3 datafiles in it, but one of them is missing.

Is it possible to recreate an empty data file on-the-fly?It has let me set the datafile to offline, but wouldnt let me do anything else.

Now my backups are failing as it recognises :"the status of database file is RECOVER".

View 14 Replies View Related

Server Administration :: Move Datafile To Another Tablespace

Feb 16, 2012

Erroneously 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?

View 3 Replies View Related

Server Administration :: ORA-01157 / Cannot Identify Datafile 1

Jun 28, 2010

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 Related

Server Administration :: When Did A Datafile Auto-extend

Dec 21, 2010

Is 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 Related

Server Administration :: How To Find When The Datafile Was Resized

Jan 17, 2012

We 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 Related

Server Administration :: Move File System Datafile To ASM

Oct 23, 2012

I 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?

View 4 Replies View Related

Server Administration :: How To Offline Datafile In No Archive Mode

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

Server Administration :: Big Datafile Vs Multiple Small Datafiles

Jun 17, 2010

what's the difference between having BIG datafile (32GB) vs many small datafiles (4GB) replacing a Big one.

View 3 Replies View Related

Server Administration :: Find Out Used Space Of A Specific Datafile

Mar 21, 2012

post some script which will find out the used space of a specific datafile.

View 5 Replies View Related

Server Administration :: Unable To Calculate Datafile Shrink Possible Size

Jul 28, 2011

I 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 ?

View 1 Replies View Related

Server Administration :: Space Management - Unable To Resize Datafile?

Feb 13, 2013

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 .

View 5 Replies View Related

Server Administration :: How To Identify Object Residing In Which Datafile In Tablespace

Mar 6, 2011

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?

View 2 Replies View Related

Server Administration :: Alter Database Datafile File_id Offline?

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

Server Administration :: Remove Datafile From Operating System After Dropping Its Tablespace

Jul 26, 2012

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?

View 2 Replies View Related

Server Administration :: How To Get Hardware Information From Database Level

Nov 22, 2010

How Can We Get Hardware Information In Oracle Database 8i for example harddisk size,volume,processor etc. i want database run only specified harddisk

View 1 Replies View Related

Server Administration :: Displaying Session Level Parameters

Aug 9, 2013

A way to show session parameters? Such as

CONSTRAINTS
USE_STORED_OUTLINES
ROW ARCHIVE VISIBILITY
CURRENT_SCHEMA

I can detect the CURRENT_SCHEMA with a query against the user env context, but I can't find any of the others there. Could there be an issue with these values being stored in PGA, and therefore not visible though any regular views? I did find an article that showed a query against an x$ data structure which showed something for different settings of CONSTRAINTS, but I can't find it again.

View 4 Replies View Related

Server Administration :: Minimum Datafile Size Required For Creating Non-system Tablespace?

May 21, 2007

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.

View 13 Replies View Related

How To Change Datafile Path

May 13, 2013

I need to change data file path, and i got some document also but one thing confusing me, we need to offline the Tablespace, so my concern is all the table space like system, sys, user, temp etc. need to offline and then alter the database?

View 9 Replies View Related

Server Administration :: ORA-00604 / Error Occurred At Recursive SQL Level 1

Oct 7, 2010

When trying deploy an mapping, the database link get this error:

ORA-06550: PACKAGE BODY, line 0, column 0:
ORA-04052: error occurred when looking up remote object db1.someC@db1@LOC_db
ORA-00604: error occurred at recursive SQL level 1
ORA-12170: TNS:Connect timeout occurred

I can use tnsping towards the db. I can use

elect * from v$instance@db1@loc_db

View 1 Replies View Related

Change Default Datafile Permission (640) To (644)

Mar 15, 2013

How datafile is created with 640 file permission by oracle software.(I know for security reasons it is created with 640 permission)

example:
-rw-r----- 1 orasd dba 104865792 Mar 15 01:17 users01.dbf

I want to change the datafile permission as below. how to change the default value of the datafiles permission from (640) to (644) internally while creating the datafile from oracle side.

example:
-rw-r--r-- 1 orasd dba 104865792 Mar 15 01:17 users01.dbf

View 7 Replies View Related

Server Administration :: How To Change The Character Set

Feb 24, 2012

what's your method,when you want to change the character set ?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved