Posts Tagged ‘database’

Find A String In Database

July 1st, 2017, posted in Oracle
Share

I’ve tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results :

SELECT * from dba_objects WHERE
object_name like '%DTN%'

A column isn't an object. If you mean that you expect the column name to be like '%DTN%', the query you want is:

SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DTN%';
Share

Oracle : Size Of Database

March 31st, 2015, posted in Oracle
Share

An Oracle Database consists of data files, redo log files, control files, temporary files.Oracle : Size Of Database,Oracle,Size Of Database,Database,data files, redo log files, control files, temporary files,Oracle data files,Oracle  redo log files,Oracle  control files,Oracle  temporary files
Whenever you say the size of the database this actually means the summation of these files.

 

 

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from 
( select sum(bytes)/1024/1024 data_size from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) d;

-------------------------------------------------------------
For Round Figure For MB :

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from 
( select round( sum(bytes)/1024/1024 ) data_size from dba_data_files ) a,
( select round( nvl(sum(bytes),0)/1024/1024 ) temp_size from dba_temp_files ) b,
( select round( sum(bytes)/1024/1024 ) redo_size from sys.v_$log ) c,
( select round( sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 ) controlfile_size from v$controlfile) d;

-------------------------------------------------------------

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from 
( select sum(bytes/1024/1024/1024) data_size from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) b,
( select sum(bytes/1024/1024/1024) redo_size from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) d;

-------------------------------------------------------------
For Round Figure GB :

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from 
( select round( sum(bytes/1024/1024/1024) ) data_size from dba_data_files ) a,
( select round( nvl(sum(bytes),0)/1024/1024/1024 ) temp_size from dba_temp_files ) b,
( select round( sum(bytes/1024/1024/1024) ) redo_size from sys.v_$log ) c,
( select round( sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 ) controlfile_size from v$controlfile) d;



----------------------------------------------------------

Get database size from v$datafile:

select round((sum(bytes)/1048576/1024),2) from v$datafile;

------------------------------------------------------------
Get Oracle Database size from dba_data_files:

select "Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)"
from(
select 
(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)",
(select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"
from dual
);

-----------------------------------------------------------------
-

 

 

here
a is megabytes allocated to ALL datafiles
b is megabytes allocated to ALL TEMP files
c is megabytes allocated to ALL redo-logs
d is megabytes allocated to ALL control files

Share

ORACLE :How to Configure Net8 in Non-RAC Database

February 17th, 2013, posted in Oracle
Share

To access the database from other than the system in which database installed you need to configure oracle supplies Net8 for this purpose. It is a common interface to client application that needs to connect to the oracle database. It consists of the three files: tnsname.ora, listener.ora, sqlnet.ora.

Steps or Process:
1.      Edit the tnsname.ora file and add the new service name for use by the client software.
2.      Edit the listener.ora file add an entry for another database instance.
3.      Use the ping command to verify connectivity to the Host.
4.      Use the tnsping command to verify the connectivity to the Net8 listener.
5.      Start/Stop the Net8 listener to reload newly added DB instance.
6.      Connect to a remote database over the network.

tnsname.ora:
The normal location for this file is D:oracleora92networkadmin. This file is located on both client and server. If you make configuration changes on the server ensure you can connect to the database through the listener if you are logged on to the server. If you make configuration change on the client ensure you can connect from your client workstation to the database through the listener running on the server.
Add the following entry in your tnsname.ora file and change the value in the bracket according to your environment.
RMAN.AL-SADHAN.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rmanbackup)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RMAN)
)
)

Note: You can put either system name or directly static IP (192.168. 0.1) as HOST.
C:tnsping rman
Use the above command to verify the connectivity on windows environment.

listener.ora:
The normal location for this file is D:oracleora92networkadmin. This file is client side file (typically on remote PC). The client uses thistnsname.ora file to obtain connection details from the desired database. Add the following entry in your listener.ora file and changed the value in bracket as per your environment.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:oracleora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = RMAN)
(ORACLE_HOME = D:oracleora92)
(SID_NAME = RMAN)
)
)

Note: Provide the unique global name as you specify at the time of database creation. You can change it later with your own domain name.

SQL>ALTER DATABASE rename global_name to rman.al-sadhan.com;
C:lsnrctl
Lsnrctl>stop listener
Lsnrctl>start listener
Lsnrctl>reload listener
You can use above command to bounce the listener.

sqlnet.ora:
The normal location for this file is D:oracleora92networkadmin. The sqlnet.ora file is the profile configuration file, and it resides on the client machines and the database server. The sqnet.ora is text file that contain basic configuration details used by the SQL*Net.
NAMES.DEFAULT_DOMAIN = al-sadhan.com
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
trace_level_client = OFF
sqlnet.expire_time = 30
names.default_domain:  If the net service name does not have a domain specified, this parameter value is appended to the service name.

names_directory_path: This parameter specifies the order of naming methods used when a client attempts a connection to a database. Possible values include: LDAP; TNSNAMES; HOSTNAME; ONAMES; and EZCONNECT.
sqlnet.expire_time:  This parameter, set on the server, enables dead connection detection.  After the specified time interval, expressed in minutes, the server checks to see if the client is still connected. If the client is not still connected, the server process exits.

sqlnet.authentication_services: This parameter is used to enable one or more authentication services.  There is no default setting, so if authentication has been installed, it this parameter should be set to either NONE for no authentication methods or ALL for all authentication methods.

trace_level_client: This parameter enables tracing unless it is set to OFF or 0, which is the default.  Use the following values to set tracing levels: USER (4); ADMIN (10); and SUPPORT (16).

*********************************************************************************************************************
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as
 well.
*********************************************************************************************************************
Share

Convert Your CVS File Into MySQL File Online For Free

September 23rd, 2011, posted in TEChNoLoGY
Share
These are a Great Online Tools and I have used it in the past and it has worked well for me.From these free online tools you can convert your CSV format file into MySQL format. And best part is its free and you dont have to do anything. All you have to do is upload your file through browser and than you will get the MySQL query or MySQL file and just use it.
Its just sitting back and enjoyin the movie type thing…..
So, without any further delay here are the best links :
Share