Server Administration :: Data File / Table Relation
Feb 25, 2011I have 2 schemas in my database with over 500 tables in each.i am trying to know which tables actually belong to which datafile in which tablespace.
View 2 Replies
ADVERTISEMENT
Server Administration :: Any Relation Of Tablespace Disk Space And Actual Data Present In Tables
Aug 16, 2012We are using Oracle 10g and have 10 tablespaces defined for our Database which have 108 tables. Size of 108 tables is around 251 MB as seen during importing the dump. While creating these 10 tablespaces I used below parameters for allocation of space
SIZE 1M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 1M;
which set the initial space for 10 tablespaces to around 1032Kb each. Now my Question is after importing the dump , how the disk space for 10 tablespaces increases to 398 MB in total ?
Is there any relation of Tablespace disk space and Actual Data present in the tables ?
Server Administration :: Location Of Table In Tablespace / Data File
Jul 6, 2010I was wondering if there is any way to know in which Tablespace and Datafile my Table is located. I have exported a table and about to delete it as i am partitioning it.
View 9 Replies View RelatedSQL & PL/SQL :: Find Non-relation Table And Relation Table?
Feb 7, 2012Suppose i have to two tables.. emp and dept. Deptno column Foriegn for the both table. how to find the non relation table and relation table.
View 8 Replies View RelatedServer Administration :: ORA-01111 / Name For Data File 636 Is Unknown - Rename To Correct File
Nov 3, 2012i have two tablespaces dictionary managed (SYSTEM,APPLSYSX) i tried to change to locally cause it will cause problem in future when trying to run OATM migration.i did it successfully on APPLSYSX,when i did it on system upon oracle procedure.i have to change all tablespaces to read only when i did that with tablespace APPLSYSD(alter tablespace APPLSYSD read only) i received errors
SQL> alter tablespace APPLSYSD READ ONLY;
alter tablespace APPLSYSD READ ONLY
*
ERROR at line 1:
ORA-01230: cannot make read only - file 636 is offline
ORA-01111: name for data file 636 is unknown - rename to correct file
ORA-01110: data file 636: '/vol5u/oracle/prddb/9.2.0/dbs/MISSING00636'
i have not this file on the OS
Server Administration :: What Can Be Maximum Size Of Data File
Nov 24, 2011I am having I/O issues if i create 20 GB DATAFILES on SMALL TABLE SPACE. guide me with the maximum size limit of data file that I can create in Windows 2003 32 bit server.
View 3 Replies View RelatedServer Administration :: ORA-01157 / Cannot Identify / Lock Data File 201
Aug 12, 2013Our server got crash in server1 and we recovered the same DB to server2 using RMAN back up. But while running our scheduler for email alert we got the error message as ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: 'D:ORADATAKFDBTEMP01.DBF' Our temp file is already in E folder.
View 4 Replies View RelatedServer Administration :: How To Reclaim Space After Deleting A Data File
Jun 30, 2010i have a tablespace with a datafile of 20g. now by mistake i delete the datafile and then try to delete the tablespace from EM but i got an error which says that data file is not present to delete
Now initially after deleting the file physically so then i check space by applying df -ah at os lvl so it didn't reclaim the space now i try to delete the tablespace from em so it gives me the above error. This might be due to tablespace existence. so how can i reclaim the space.
Server Administration :: Disk Space When Add Data File On Primary Database?
Jun 24, 2013I'm new into Oracle, consider the below scenario and respond it accordingly.
I want to add 500GB data file at primary database side, but doesn't have space at standby database side. My STANDBY_FILE_MANAGEMENT is set to Auto.
Server Administration :: Database File System Architecture For Data Warehouseing Application
Mar 14, 2011Which is the best possible way of configuring the Physical file structure in case of Data Warehouseing application?
I mean to ask you Multiplexing of the data file or control file is required. Or any best possible way to configure Physical file structure.
SQL & PL/SQL :: Ftp File To Remote Server By Reading Data From Table?
May 1, 2012I need a way to ftp file to remote server by reading data from table. I searched a couple of sites which asked me to use Chris xutl_ftp package..but unfortunately the site is no accessible..
Here is the code
CREATE OR REPLACE PACKAGE UTL_FTP
AUTHID CURRENT_USER
AS
/**
* LICENSE: GNU Lesser General Public License (LGPL)
* Copyright (C) 2003-2006 Russ Johnson (john_2885@yahoo.com)
[code].....
PL/SQL :: Transferring Data From Oracle Table To A File On A Windows Server
Nov 12, 2013I have an oracle DB version 11.2 running on oracle enterprise linux 5.9. How to transfer data from the oracle DB to a flat file on a windows server. What i have done so far is to use utl_file to create a csv file on the oracle server and am now attempting to transfer this file.
I was going to use scp or rcp but am unable to get this to work(was looking at filezilla). Another option i can use is ftp as i have a UNIX script which i can run to do this. All this is done through an oracle package which is run hourly through dbms_scheduler. I have been using sp_host_command to run unix commands directly from pl/sql so can use this to run a unix script for last resort if i cant find an easier way to automate this.
Server Administration :: How To See Whether Table Data Using Compression
Mar 13, 20131)i have 2 SWP TABLES. while dropping a column, i am getting error -
ORA-39726: unsupported add/drop column operation on compressed tables.
2) when i checked compression status, those were not compressed. But as per our code standard, SWP tables should not be in compress mode.
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
NOVAR PAYMENT_SWP DISABLED
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
NOVAR PREPAYMENT_SWP DISABLED
3) as a workaround, i compressed these 2 SWP tables with OLTP option, and then i was able to drop the column from these 2 SWP tables.
4) Below statement is correct or not ?
IF A TABLE USING BLOCK LEVEL COMPRESSION, THEN this error will come - ORA-39726: unsupported add/drop column operation on compressed tables.
if above statement is correct, then how to find out whether table data is using block level compression ?
5) we have DBMS_COMPRESSION.GET_COMPRESSION_TYPE. using this i just tried to find out, but i am getting "1" as output. I am not getting the exact meaning of it.
confirm what is the conclusion on this ?
SQL> declare
rid rowid;
n number;
begin
select max(rowid) into rid from NOVAR.PAYMENT_SWP;
n := dbms_compression.get_compression_type('NOVAR','PAYMENT_SWP',rid);
dbms_output.put_line(n);
end;
/
2 3 4 5 6 7 8 9 1
PL/SQL procedure successfully completed.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> /
1
PL/SQL procedure successfully completed.
SQL> SELECT max(rowid) from NOVAR.PAYMENT_SWP;
MAX(ROWID)
------------------
AAsz4fAHSAAAD3IABs
(ii) 2nd table
SQL> set serveroutput on
SQL> declare
rid rowid;
n number;
begin
select max(rowid) into rid from NOVAR.PREPAYMENT_SWP;
n := dbms_compression.get_compression_type('NOVAR','PREPAYMENT_SWP',rid);
dbms_output.put_line(n);
end;
2 3 4 5 6 7 8 9
10 /
1
PL/SQL procedure successfully completed.
SQL> SELECT max(rowid) from NOVAR.INVOICELINE_SWP;
MAX(ROWID)
------------------
AAsz4ZAEkAAAp8XAAA
Server Administration :: Size Of Data Of Table
Oct 20, 2011ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
2 avg_space, num_freelist_blocks
3 from user_tables
4 where table_name = 'T'
5 /
BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
19 35 2810 3
Ok, the above shows us:
- we have 55 blocks allocated to the table (still)
- 35 blocks are totally empty (above the HWM)
- 19 blocks contains data (the other block is used by the system)
- we have an average of about 2.8k free on each block used.
Therefore, our table
- consumes 19 blocks of storage in total.
- of which 19 blocks * 8k blocksize - 19 block * 2.8k free = 98k is used for our data.
not too sure this calculation is accurate for getting the size (data)of the table.
Server Utilities :: Truncate Table And Load It With Data Present In File
Jul 17, 2010My requirement is to to truncate the table and load it with the data present in file. In the control file, I used the "TRUNCATE" command as well.In case, if the file has some invalid data and sqlldr fails, my existing data will be lost. Is there any option in which the sqlldr does not TRUNCATE the table in case of a failure.
View 6 Replies View RelatedServer Utilities :: How To Write Control File To Load Data Into Revenue Table
Aug 16, 2011I have a table revenue
create table revenue
(
person varchar2(23),
month varchar2(3),
rev_amt number
)
and i have data in a file like below
Person Jan Feb Mar Apr Mai Jun Jul Aug Sep Oct Nov Dez
--------------------------------------------------------
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
Weber,234,234,123,457,456,287,234,123,678,656,341,567
Keller,596,276,347,134,743,545,216,456,124,753,346,456
Meyer,987,345,645,567,834,567,789,234,678,973,456,125
Holzer,509,154,876,347,146,788,174,986,568,246,324,987
Müller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
Stoller,596,237,976,876,346,567,126,879,125,568,124,753
Marty,094,234,235,763,054,567,237,457,325,753,577,346
Studer,784,567,235,753,124,575,864,235,753,864,634,678
i want to load it into the table in the following way.
Person Month Revenue
-------------------------
Schnyder Jan 345
Schnyder Feb 223
Schnyder Mar 122
Schnyder Apr 345
Schnyder Mai 324
Schnyder Jun 244
Schnyder Jul 123
Schnyder Aug 123
Schnyder Sep 345
Schnyder Oct 121
Schnyder Nov 345
Schnyder Dez 197
........ ... ...
How to write control file to load this data into the above revenue table.
Server Administration :: Cyrillic Character Set - Import A Table Of Data To Another Database
Aug 31, 2011I'm try to import a table of data (character set: CL8ISO8859P5) to another database (character set: AL32UTF8) using exp/imp utility.After the import, all Cyrillic text was corrupted!
View 5 Replies View RelatedSQL & PL/SQL :: File Data Versus Table Data Comparison
Jun 21, 2010I have written a java code which reads 2 millions of data under a particular column from CSV file and store it into a set. Now there is a table in Oracle database which contains 10 millions of records for that particular column. Now, I want to form a SQL query which select those records under that particular column from the database table which is in CSV file but not in database table. For e.g.
If I consider the CSV file name as employee.csv and it has column called employee_name under which the records are as follows
employee_name
---------------------
Sudip
Kaushik
Joyanta
Soumya
Ritesh
Gautam
And the database table name called cmp_employee and it has column called employee_name under which the records are as follows
employee_name
--------------------
Sudip
Kaushik
Joyanta
Soumen
Souvik
Sahoo
Now, the "SQL Query" should return ---- "Soumya, Ritesh, Gautam" this three names.
Server Utilities :: How To Execute SQLLDR / Where Data File Reside In Another Server
Mar 24, 2010How to execute the SQLLDR, where Data File Reside in another Server?
View 1 Replies View RelatedServer Administration :: Identify Tnsnames.ora File?
Jun 10, 2011How to find out the database TNSNAMES.ora file if there are multiple .ora files with different names in Oracle_home/network/admin directory ?
View 6 Replies View RelatedServer Administration :: UNDO In Parameter File?
Dec 9, 2011I have a question ragarding undo tablespace. I want to ask that why only undo tablespace information we need to specify in parameter file. We do not specify any other tablespace information. Not even for temporary tablespace. Then why we need to give undo tablespace name while instance is creating.
View 5 Replies View RelatedServer Administration :: ARC File Size Too Small?
Oct 2, 2012I noticed my DB is generating a lot of "small" .arc files and I am usure why. As you can see from the v$log query my log file size is set to 50MB. But yet BLOCKS*BLOCK_SIZE never adds up to 50MB.
Is there anything else I can look into to see how to make the .arc files larger?
SQL> select group#, thread#, bytes from v$log;
GROUP# THREAD# BYTES
---------- ---------- ----------
1 1 52428800
2 1 52428800
3 2 52428800
4 2 52428800
select blocks, block_size, blocks*block_size from v$archived_log where sequence# between 63876 and 72851 and thread# = 1
BLOCKS BLOCK_SIZE BLOCKS*BLOCK_SIZE
---------- ---------- -----------------
28 512 14336
28 512 14336
28 512 14336
55 512 28160
[code]...
Server Administration :: How To Check Alertlog File
Mar 3, 2012you 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 RelatedServer Administration :: Archive Log File Generating?
Jan 10, 2011I want to know how many archive log generating in One hour at the peak time. We have 6 nodes RAC multiplex 2.
Is there are any query through which I can achieve the above purpose.
Note: As this is a prod instance client is not happy to implement Log Miner utility.
Server Administration :: TNS Errors In Listener Log File?
Sep 11, 2012We are getting the below error messages in LISTENER LOG FILE. That too every 5 Mins.
10-SEP-2012 16:25:43 * (CONNECT_DATA=(SERVICE_NAME=dpm)(CID=(PROGRAM=W:Applicationdpm2010.exe)(HOST=ISSLDMUM01PC169)(USER=bharathi))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.80.100.39)(PORT=2241)) * establish * dpm * 0
10-SEP-2012 16:25:46 * service_update * DPM * 0
10-SEP-2012 16:25:47 * 12546
TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied
I am not getting the reason for it.
Server Administration :: Errors While Starting DB Using SP File
Sep 16, 2008SP file was removed during the testing and I started the database after making some changes in the pfile, and it works fine.
But when I try to start the database using the SPFILE, it gives an error.
SQL> STARTUP PFILE='D:oracleproduct10.2.0adminorclpfileINITORCL.ORA'
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 109052608 bytes
Database Buffers 171966464 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;
File created.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORA-01565: error in identifying file 'D:oracleproduct10.2.0db_7/dbs/SPFILEORCL.ORA'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 1541)
The contents of the spfile in $ORACLE_HOMEdatabase are
*.SPFILE='D:oracleproduct10.2.0db_7/dbs/SPFILEORCL.ORA'
Server Administration :: Unable To Run Setup EXE File?
Mar 14, 2012when i try to install Oracle 10g (10.1.0.2) 64- bits on windows server 2003 enterprise edition 64 bits service pack1 and i try for windows server 2003 standard edition 64 bits on a pc,i am getting the below error.
the image file E: is valid, but it is for a machine type other than current machine
i need to install it as soon as possible .
Server Administration :: File Format Of DBF Files
Apr 12, 2010I want to know the file format of .dbf file.I want to open it in hex editor and want to read contents of that file from there.
View 4 Replies View RelatedServer Administration :: Create A New Control File?
Jul 12, 2012I am trying to create a new controlfile. I used "alter database backup controlfile to trace;" and then I copied and pasted the contents of the trace file into the .sql file. My sql script is
CREATE CONTROLFILE SET DATABASE "FIVESTAR" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
[code]....
Actually, the name of my database was 'STAR' and I changed it to "FIVESTAR" int this .sql script. Now when I run this script with SQL> @ <path of my sql script>, it gives me the following errors:
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01504: database name 'FIVESTAR' does not match parameter db_name 'STAR'
Server Administration :: How To Recreate Control File
Jun 12, 2012 I can not run the control file treace file,why?
[oracle@hxl oradata_bak]$ more run_sql.sql
CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
[code]...