Uncommited rows with F5 function key not showing up in TOAD same session

Uncommited rows with F5 function key not showing up in TOAD same session

I use putty -sqlplus for most of the work... as i see easy to handle DML's.

I am inserting rows with insert statements and pressed F9 for execution..
I am able to see records in toad without commiting records..
whereas
I am inserting rows with multiple insert statements and pressed F5 for execution..
I am not able to see the records in both ways either running select statement with f5 and f9.. ..

Should i set any configuration option in toad when i use F5 for statements execution and to see uncommited records without committing or rollback..just to see what records are inserted or modified???

wonder how to see records which are inserted with insert statement and F5 function key??!!!!

Anyone knows how to see ???

Uncommited rows with F5 function key not showing up in TOAD same session

Uncommited rows with F5 function key not showing up in TOAD same session

I use putty -sqlplus for most of the work... as i see easy to handle DML's.

I am inserting rows with insert statements and pressed F9 for execution..
I am able to see records in toad without commiting records..
whereas
I am inserting rows with multiple insert statements and pressed F5 for execution..
I am not able to see the records in both ways either running select statement with f5 and f9.. ..

Should i set any configuration option in toad when i use F5 for statements execution and to see uncommited records without committing or rollback..just to see what records are inserted or modified???

wonder how to see records which are inserted with insert statement and F5 function key??!!!!

Anyone knows how to see ???

Difference between TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE in oracle 10g:

Difference between TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE in oracle 10g:


The TIMESTAMP WITH TIME ZONE data type data is normalized to the database time zone when it is saved into the database and users see the data in their local session time zone when they select from it.

TIMESTAMP WITH TIME ZONE: The data will not be normalized when data is being saved into database but it has time zone indicator to which refers.

All other DATE, TIMESTAMP data type’s data is normalized to DB time zone when it is saved and data remain unchanged when selected.


NLS_DATE_FORMAT and NLS_TERRITORY parameter values can be used to format the data in desired format.


Difference between Schema and User in oracle

A schema is a collection of database objects, schema is owned by a database user and has same name as that user. By default user has all the access on its own schema objects and other schemas objects based on the grants been granted.
There is 1 to 1 relationship between schema and user.

...

..

Difference between TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE in oracle 10g:

Difference between TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE in oracle 10g:


The TIMESTAMP WITH TIME ZONE data type data is normalized to the database time zone when it is saved into the database and users see the data in their local session time zone when they select from it.

TIMESTAMP WITH TIME ZONE: The data will not be normalized when data is being saved into database but it has time zone indicator to which refers.

All other DATE, TIMESTAMP data type’s data is normalized to DB time zone when it is saved and data remain unchanged when selected.


NLS_DATE_FORMAT and NLS_TERRITORY parameter values can be used to format the data in desired format.


Difference between Schema and User in oracle

A schema is a collection of database objects, schema is owned by a database user and has same name as that user. By default user has all the access on its own schema objects and other schemas objects based on the grants been granted.
There is 1 to 1 relationship between schema and user.

...

..

How to display background processes,remote, local user processes in oracle 10g database.

How to display oracle background processes,remote, local user processes in oracle 10g database at shell command prompt:

Occasionally we may require to see the background processes, remote, local users processes started against oracle database in server system and the way to find those is below.

The way to find is different from unix systems to windows system, but in unix it is just running PS commond and which shows everything.

On unix , use the ps command to display them.

ps -ef |grep ora10g -- ora10g is the databasename or SID

you would see many ora__ora10g, these are all background processes.

How to see remote user processes:

Any process ID that include (LOCAL=NO) ,which is an indication that the server process is servicing a remote user process.

How to see local user processes:

Any process ID that has (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beg))), which is an indication that the user process is running on the same db server machine which means it did not come via listener.

On windows, server processes are threads within the single oracle.exe process and can be seen within windows task manager.

The following query can be used to list out all the processes/sessions started in database.

SELECT ses.sid,ses.serial#,pro.spid,ses.osuser,ses.type,
ses.username,
ses.program
FROM v$process pro,
v$session ses
WHERE pro.addr = ses.paddr;

Note: there is no username shown for backgrund processs and it is normal.

The best way to kill sessions which I feel should be incase of heavy load on the dB system very rarley or if any user request dba to do so most of the cases in development environment, the best way i see is to login into dB system and kill those using
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' -- best way
OR
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

KILLING ORACLE USER PROCESSES AT UNIX COMMAND PROMPT:
oracle spid can be found using ps -ef | grep ora command
$ kill spid

IN WINDOWS COMMAND PROMPT:

orakill sid spid

sid,Spid can be taken from running above select statement.
Note: however killing oracle sessions at OS command prompt is NOT suggestable as it may crash database systems.. so please avoid it all the time... always login into dba sql plus command prompt to do dba activities...

-- cheers...

How to display background processes,remote, local user processes in oracle 10g database.

How to display oracle background processes,remote, local user processes in oracle 10g database at shell command prompt:

Occasionally we may require to see the background processes, remote, local users processes started against oracle database in server system and the way to find those is below.

The way to find is different from unix systems to windows system, but in unix it is just running PS commond and which shows everything.

On unix , use the ps command to display them.

ps -ef |grep ora10g -- ora10g is the databasename or SID

you would see many ora__ora10g, these are all background processes.

How to see remote user processes:

Any process ID that include (LOCAL=NO) ,which is an indication that the server process is servicing a remote user process.

How to see local user processes:

Any process ID that has (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beg))), which is an indication that the user process is running on the same db server machine which means it did not come via listener.

On windows, server processes are threads within the single oracle.exe process and can be seen within windows task manager.

The following query can be used to list out all the processes/sessions started in database.

SELECT ses.sid,ses.serial#,pro.spid,ses.osuser,ses.type,
ses.username,
ses.program
FROM v$process pro,
v$session ses
WHERE pro.addr = ses.paddr;

Note: there is no username shown for backgrund processs and it is normal.

The best way to kill sessions which I feel should be incase of heavy load on the dB system very rarley or if any user request dba to do so most of the cases in development environment, the best way i see is to login into dB system and kill those using
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' -- best way
OR
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

KILLING ORACLE USER PROCESSES AT UNIX COMMAND PROMPT:
oracle spid can be found using ps -ef | grep ora command
$ kill spid

IN WINDOWS COMMAND PROMPT:

orakill sid spid

sid,Spid can be taken from running above select statement.
Note: however killing oracle sessions at OS command prompt is NOT suggestable as it may crash database systems.. so please avoid it all the time... always login into dba sql plus command prompt to do dba activities...

-- cheers...

How to extract records that starts Only with Alphabets and Numerical with Regular Expressions in Oracle

How to extract records that starts with only Alphabets and Numerical & both with Regular Expressions in Oracle:

Occasionally we may required to display records having only numerical numbers or only alphabets data in a particular column and here are the few simple tips and tricks to use, thanks to Regular Expressions concept in oracle as we do a lot with this concept and few of those are here with simple examples…

the example table structure is

SQL> desc test1;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(15)


List the records having column data with only Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'^[[:alpha:]]+$');

COL1
---------------
thisisatestdata

List the records having column data with only Numerical/digits

SQL> select * from test1 where REGEXP_LIKE(col1,'^[[:digit:]]+$');

COL1
---------------
1234456789

List the records starting with Numerical in particular column but not with Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'^[0-9]+');

COL1
---------------
1234456789
123end

List the records starting with Alphabets in particular column but not with Numericals

SQL> select * from test1 where REGEXP_LIKE(lower(col1),'^[a-z]+');

COL1
---------------
thisisatestdata
youand144mereers
end123
Or
select * from test1 where REGEXPR_LIKE(col1,'^[a-z]+','i');
few match parameters:
'c' - case sensitive
'i'- case insensitive
'x'-ignore whitespace chars
and 'm', 'n' ... etc..


List the records ending with Numerical numbers in particular column but not with Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'[0-9]$');

COL1
---------------
1234456789
end123

2 rows selected.

List the records ending with Alphabets in particular column but not with Numerical numbers

SQL> select * from test1 where REGEXP_LIKE(lower(col1),'[a-z]$');

COL1
---------------
thisisatestdata
youand144mereers
123end

------ A real time example -----

We got a requirement few days back to know the internal trainee count and external trainee count and we did come up with an efficient select statement having regular expression in it. The key point to differentiate trainee is that the internal trainee identification number starts with number and external trainee start with alphabets.

Here is the query

SELECT TO_CHAR(COMPL_DTE,'YYYY-MM') AS COMPLETION_MONTH, COUNT(stud_id) AS COUNT_,
COUNT(
CASE WHEN REGEXP_LIKE (stud_id, '^[0-9]+')
THEN stud_id
ELSE NULL
END
) AS INTRNL
,
COUNT(
CASE WHEN REGEXP_LIKE (stud_id, '^[a-z]+')
THEN stud_id
ELSE NULL
END
) AS EXTRNAL
FROM ORACLE_TABLE
GROUP BY
TO_CHAR(DTE,'YYYY-MM');



A lot more examples will come soon on Regular Expressions concepts...

.. cheers... hope this will help..

How to extract records that starts Only with Alphabets and Numerical with Regular Expressions in Oracle

How to extract records that starts with only Alphabets and Numerical & both with Regular Expressions in Oracle:

Occasionally we may required to display records having only numerical numbers or only alphabets data in a particular column and here are the few simple tips and tricks to use, thanks to Regular Expressions concept in oracle as we do a lot with this concept and few of those are here with simple examples…

the example table structure is

SQL> desc test1;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(15)


List the records having column data with only Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'^[[:alpha:]]+$');

COL1
---------------
thisisatestdata

List the records having column data with only Numerical/digits

SQL> select * from test1 where REGEXP_LIKE(col1,'^[[:digit:]]+$');

COL1
---------------
1234456789

List the records starting with Numerical in particular column but not with Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'^[0-9]+');

COL1
---------------
1234456789
123end

List the records starting with Alphabets in particular column but not with Numericals

SQL> select * from test1 where REGEXP_LIKE(lower(col1),'^[a-z]+');

COL1
---------------
thisisatestdata
youand144mereers
end123
Or
select * from test1 where REGEXPR_LIKE(col1,'^[a-z]+','i');
few match parameters:
'c' - case sensitive
'i'- case insensitive
'x'-ignore whitespace chars
and 'm', 'n' ... etc..


List the records ending with Numerical numbers in particular column but not with Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'[0-9]$');

COL1
---------------
1234456789
end123

2 rows selected.

List the records ending with Alphabets in particular column but not with Numerical numbers

SQL> select * from test1 where REGEXP_LIKE(lower(col1),'[a-z]$');

COL1
---------------
thisisatestdata
youand144mereers
123end

------ A real time example -----

We got a requirement few days back to know the internal trainee count and external trainee count and we did come up with an efficient select statement having regular expression in it. The key point to differentiate trainee is that the internal trainee identification number starts with number and external trainee start with alphabets.

Here is the query

SELECT TO_CHAR(COMPL_DTE,'YYYY-MM') AS COMPLETION_MONTH, COUNT(stud_id) AS COUNT_,
COUNT(
CASE WHEN REGEXP_LIKE (stud_id, '^[0-9]+')
THEN stud_id
ELSE NULL
END
) AS INTRNL
,
COUNT(
CASE WHEN REGEXP_LIKE (stud_id, '^[a-z]+')
THEN stud_id
ELSE NULL
END
) AS EXTRNAL
FROM ORACLE_TABLE
GROUP BY
TO_CHAR(DTE,'YYYY-MM');



A lot more examples will come soon on Regular Expressions concepts...

.. cheers... hope this will help..

Identifying ORACLE DB Host Name and its IP Address

Identifying ORACLE DB Host Name and its IP Address including client IP address (from which client is connected):

SYS_CONTEXT function returns the current session information:

There are various ways to find db name to which connection is made are as follows:

select sys_context('userenv','db_name') from dual;
select ora_database_name from dual;
select global_name from global_name;

select sys_context('USERENV','IP_ADDRESS' ) FROM DUAL;
-- returns the IP address of client machine from which connection is established

few more..
select USERENV('LANGUAGE') ,
USERENV('ISDBA') from dual;
..etc..

We can find local and remote hosts information using UTL_INADDR package in oracle 10g.
more info just type desc UTL_INADDR

SELECT UTL_INADDR.get_host_address from dual;
-- returns the IP address of database physical system

there is lot more info we can find with UTL_INADDR and SYS_CONTEXT.

... cheers...

Identifying ORACLE DB Host Name and its IP Address

Identifying ORACLE DB Host Name and its IP Address including client IP address (from which client is connected):

SYS_CONTEXT function returns the current session information:

There are various ways to find db name to which connection is made are as follows:

select sys_context('userenv','db_name') from dual;
select ora_database_name from dual;
select global_name from global_name;

select sys_context('USERENV','IP_ADDRESS' ) FROM DUAL;

-- returns the IP address of client machine from which connection is established

few more..

select USERENV('LANGUAGE')  ,
USERENV('ISDBA') from dual;

..etc..

We can find local and remote hosts information using UTL_INADDR package in oracle 10g.
more info just type desc UTL_INADDR

SELECT UTL_INADDR.get_host_address from dual;

-- returns the IP address of database physical system

there is lot more info we can find with UTL_INADDR and SYS_CONTEXT.

... cheers...