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.
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 add a datafile mapped to a shared network drive in windows server.
View 1 Replies View RelatedJust 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 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 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 Relatedwhat'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?
I am looking for a script to find the tbs usage including datafiles. Tablespaces Datafile Size Used Free Used% Free% Max MB
Need all the data in MB and all the datafile space in every tablespace.I have searched internet,but couldn't find this type of format.
when 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
We a running oracle9i database and forms 6i application.
We have 4 gb ram size and sized the sga for 700mb pga for 400mb for past 4 months there is no problem.
Now from last week we encounter the server hang, and we do restart. How to check reason for hanging.what are all the check list to carried out.
How can I check whether the ASM is used in the environment? how I can check for it and if being used, how to connect to that instance later on.
View 5 Replies View Relatedyou have some 500 Db how will u check the alertlog file. do u do it manually or is their any tool to check the alertlog files. how to check it.
View 7 Replies View RelatedHow can i check a partition whether it has been compressed? just as flowing test,i can not get the information about partition P_L1 whether been compressed.
SQL> Select
2 aa.compression,
3 aa.partition_name
4 From dba_tab_partitions aa
5 Where aa.table_name = 'TB_HXL_LIST';
COMPRESS PARTITION_NAME
-------- ------------------------------
DISABLED P_L1
DISABLED P_L2
DISABLED P_L3
DISABLED P_L4
SQL> Alter Table tb_hxl_list compress;
Table altered.
SQL> Alter Table TB_HXL_LIST
2 Move Partition P_L1 compress;
Table altered.
SQL> Select
2 aa.compression,
3 aa.partition_name
4 From dba_tab_partitions aa
5 Where aa.table_name = 'TB_HXL_LIST';
COMPRESS PARTITION_NAME
-------- ------------------------------
ENABLED P_L1
ENABLED P_L2
ENABLED P_L3
ENABLED P_L4
How to check whether database is dedicated or shared ?
View 1 Replies View Relatedquick check on a hidden parameter? I need to know the default value and possible values for _serial_direct_read on various releases, I have only 11.2.0.3 available right now and I'ld like to know this for 11.2.x.x 11.1.x.x, and 10.2.x.x.
Below is the query that will show what it is currently which (unless you've changed it) will be the default: auto for my 11.2.0.3. Then to see the options, try to set it to something wrong, my options are false/true/never/auto/always. I think false/true were the only choices for 10.2 and are maintained only for compatibility. But I can't rememebnr 11.1.
orcl>
orcl> set lin 80
orcl> select KSPPSTVL from x$ksppcv join x$ksppi using (indx) where ksppinm='_serial_direct_read';
KSPPSTVL
--------------------------------------------------------------------------------
auto
orcl>
orcl> alter system set "_serial_direct_read"=rubbish;
alter system set "_serial_direct_read"=rubbish
*
ERROR at line 1: ORA-00096: invalid value RUBBISH for parameter _serial_direct_read, must be from among false, true, never, auto, always
orcl>
update: added 11.1.x.x to my wishlist
Actually in our environment , sysadmins are upgrading java versions on Unix (HP & Solaris) Machines and they have asked the query whether oracle database uses Java or not?
If it uses then all the databases need to be shut down for their planned activities and if not no need to shutdown the databases.
i am getting this error during expdp ORA-01555 -snapshot too old
1. i increased undo_retention i.e 50000
2. i execute expdp during late night when almost zero user is online so no commit from any other session.
3. we have a table having BLOB data type field in a table which is 500 GB in size.complete DB size is 700 GB
where and why this error exists.if corruption of BLOB so how to check that etc. the same error is occurring in three of our data centres during expdp.
which data dictionary view would you query to see the free extents in a tablespace.
i use dba_free_space;
SQL> select tablespace_name,sum(bytes),sum(blocks) from dba_free_space where
tablespace_name = 'SYSTEM' GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME SUM(BYTES) SUM(BLOCKS)
------------------------------ ---------- -----------
SYSTEM 9830400 1200
I need to display the parameter and status of DB for listener and Read Only.
I know those value could be get from command line , but could we get the values of Listener and Read only by SQL/PlSQL? So I can get it through the query of DB.
we have a few profiles in dba_profile. How to check what is the last modification date of the the resources inside each profile.
View 6 Replies View RelatedI 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.
View 11 Replies View RelatedI want to find out the columns on which primary constraints are enabled.Which view must I use to check this.
View 2 Replies View RelatedI 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.
View 13 Replies View RelatedI 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.
View 17 Replies View RelatedIn 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.