How to find SQL,SQL_ID history on Oracle Session related Queries

How to find SQL,SQL_ID history on Oracle Session related Queries

Last/Latest Running SQL ----------------------- set pages 50000 lines 32767 col "Last SQL" for 100 SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL" FROM gv$session s, gv$sqlarea t WHERE s.sql_address =t.address AND s.sql_hash_value =t.hash_value / Current Running SQLs -------------------- set pages 50000 lines 32767 col HOST_NAME for a20 col EVENT for a40 col MACHINE for a30 col SQL_TEXT for a50 col USERNAME for a15 select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait, to_char(logon_time,'DD-MON-RR HH24:MI') login from gv$session S,gV$SQLAREA A,gv$instance i where S.username is not null -- and S.status='ACTIVE' AND S.sql_address=A.address and s.inst_id=a.inst_id and i.inst_id = a.inst_id and sql_text not like 'select S.USERNAME,S.seconds_in_wait%' / Current Running SQLs -------------------- set pages 50000 lines 32767 col program format a20 col sql_text format a50 select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text from v$session b,v$sqlarea c where b.sql_id=c.sql_id / Last/Latest Running SQL ----------------------- set pages 50000 lines 32767 select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history where sql_id is not null order by 1 desc / SQLs Running from longtime -------------------------- alter session set nls_date_format = 'dd/mm/yyyy hh24:mi'; set pages 50000 lines 32767 col target format a25 col opname format a40 select sid ,opname ,target ,round(sofar/totalwork*100,2) as percent_done ,start_time ,last_update_time ,time_remaining from v$session_longops / Active Sessions running for more than 1 hour --------------------------------------------- set pages 50000 lines 32767 col USERNAME for a10 col MACHINE for a15 col PROGRAM for a40 SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM, to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Logon Time", ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON, ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL From gv$session WHERE STATUS='ACTIVE' AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)*(24*60),1) > 60 ORDER BY MINUTES_LOGGED_ON DESC; Session details associated with SID and Event waiting for --------------------------------------------------------- set pages 50000 lines 32767 col EVENT for a40 select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME,to_char(Sysdate, 'dd-mon-yy-hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8; Session details associated with Oracle SID ------------------------------------------- set head off set verify off set echo off set pages 1500 set linesize 100 set lines 120 prompt prompt Details of SID / SPID / Client PID prompt ================================== select /*+ CHOOSE*/ 'Session Id.............................................: '||s.sid, 'Serial Num..............................................: '||s.serial#, 'User Name ..............................................: '||s.username, 'Session Status .........................................: '||s.status, 'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client, 'Server Process ID ......................................: '||p.spid Server, 'Sql_Address ............................................: '||s.sql_address, 'Sql_hash_value .........................................: '||s.sql_hash_value, 'Schema Name ..... ......................................: '||s.SCHEMANAME, 'Program ...............................................: '||s.program, 'Module .................................................: '|| s.module, 'Action .................................................: '||s.action, 'Terminal ...............................................: '||s.terminal, 'Client Machine .........................................: '||s.machine, 'LAST_CALL_ET ...........................................: '||s.last_call_et, 'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600 from v$session s, v$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) / set head on Checking for Active Transactions SID ------------------------------------ select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr; Session details from Session longops ------------------------------------- select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops; Session details with SPID ------------------------- select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS, MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss') from v$session where paddr in (select addr from v$process where spid = '&spid') / To find Undo Generated For a given session ------------------------------------------ select username, t.used_ublk ,t.used_urec from gv$transaction t,gv$session s where t.addr=s.taddr and s.sid='&sid'; To list count of connections from other machines ------------------------------------------------ select count(1),machine from gv$session where inst_id='&inst_id' group by machine; To get total count of sessions and processes -------------------------------------------- select count(*) from v$session; select count(*) from v$process; select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual; To find sqltext thru sqladdress ------------------------------- select sql_address from v$session where sid=1999; select sql_text from v$sqltext where ADDRESS='C00000027FF00AF0' order by PIECE; To find sqltext for different sql hashvalue ------------------------------------------- select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875, 248741712,2235840973,2787402785) To list long running forms user sessions ---------------------------------------- select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and p.addr=s.paddr ; To list inactive Sessions respective username --------------------------------------------- SELECT username,count(*) num_inv_sess FROM v$session where last_call_et > 3600 and username is not null AND STATUS='INACTIVE' group by username order by num_inv_sess DESC; SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND STATUS='INACTIVE'; SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND STATUS='INACTIVE'; To find session id with set of SPIDs ------------------------------------ select sid from v$session, v$process where addr=paddr and spid in ('11555','26265','11533'); To find Sql Text given SQLHASH & SQLADDR ---------------------------------------- select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece; select piece,sql_text from v$sqltext where ADDRESS ='&addr' order by piece;

where i was?

A quick overview of each of the PeopleSoft HRMS applications or modules

A quick overview of each of the PeopleSoft HRMS applications or modules

Following is a quick overview of each of the PeopleSoft HRMS applications or
modules available today:

Core HRMS The core PeopleSoft HRMS application, which includes
Manage Workforce, contains the basic functions to hire, transfer, promote,
and make job changes for an employee. This portion of the application contains the basic employee information, including history as well as
department, location, and job information. Concepts to support basic EEO,
affirmative action, labor relations, employment verifications, and basic HR
functions are contained in this module. With PeopleSoft 8, an additional
module, eProfile, allows employees themselves to update basic employee or
profile information.

Career Planning/Performance Management This functionality is
included as part of the core HRMS module. This area supports major
career planning processes such as competency planning, career management,
employee review processing, and succession planning. With People-
Soft 8, a new module, eDevelopment, is also available, providing manager
and employee features to help individuals manage their careers more
effectively over the Internet.
Recruiting This functionality is also included as part of the core HRMS
module. The recruiting function includes creation of job requisitions and job
search or skill search capabilities including applicant tracking, interviewing,
and hiring. This application integrates directly with the basic employee
information such that applicant data is transferred directly to employee
tables once an applicant is hired. A new module in PeopleSoft 8, eRecruit,
allows for full external recruiting capabilities enabled through Web
technology.

Position Management The Position Management functions support
budgeting and managing job positions within an organization. This functionality,
included in the core HRMS application, is typically used for governmental
or regulatory establishments that have a predefined budget and a set
of job positions that are authorized for employment. Companies that let each
manager determine job requirements, which are not tracked on a fixed job
basis, don’t implement this feature.

Training Administration The Training Administration module is also
included in the core HRMS product. Within this area, training programs
can be set up and sessions scheduled to enroll and track employee training.
Information on each course and session can be tracked, including instructors
and class requirements. Waiting-list features are included.
Pension Administration Pension Administration is a fairly new application
that provides the capability to track and manage pension deductions
and allocations, which are then passed to the plan provider. This module is
only U.S.-based at this time and doesn’t support pension requirements globally. It isn’t included as a part of the core HRMS product and must be
purchased separately.

Benefits Administration and Base Benefits Two types
of benefits processing are available with PeopleSoft. Base Benefits processing,
which is included in the core HRMS application, provides for the basic tracking
of benefit plans and links each plan to a payroll deduction. However, any
changes to the plan or enrollment processing is done manually. Benefits
Administration allows you to automate the benefits enrollment and change
process. This module is an add-on to the core package. With PeopleSoft 8,
a new module called eBenefits supports key employee self-service features,
allowing employees to enroll in their benefit plans and make changes to them.
It’s available at an additional price.

Salary Planning This function is included in the core HRMS application.
It features the ability to create salary plans, develop salary-increase
budgets by department, and process salary changes. Standard salary matrices
and review-rating distributions can be accomplished by leveraging this
functionality.

Variable/Total Compensation The features of this capability are part
of the core HRMS application. This module allows you to create variable
compensation plans and administer them to your staff using eligibility
criteria calculation methods, and provides specific payout options for your
employees (such as a special bonus check, an incentive payment, or a salary
increase). Incentive goals, both group and individual, can be set up as well.
A manager’s self-service module, eCompensation, is provided with People-
Soft 8; it allows managers to perform key compensation planning functions,
leveraging the Internet.

Payroll for North America This major application area for the U.S. and
Canada includes the processes that support the payroll and pay the employees,
including the processing of specific earnings and deductions as well as both
on- and off-cycle payrolls. In addition to these features, PeopleSoft 8, with
its new ePay module, allows paycheck presentation and electronic stub
review via the Internet.

Payroll Interface This feature includes processing to support the
calculations of benefits and deductions, which can be passed to a third-party
vendor for payroll purposes. It includes an interface generator that can create specific files that can integrate with payroll outsourcing vendors such
as ADP (Automatic Data Processing, Inc.).

Time and Labor The Time and Labor application has improved over the
past few PeopleSoft releases and now supports online (even Web-enabled)
time collection, including maintenance of schedules and pay or time rules
and retroactive processing. This application interfaces closely with the
Payroll module and passes “buckets” of time and expenses by pay period to
the pay sheets (found in the Payroll module) used to create the employee
paychecks. Another module, Time and Attendance, is also available but only
with the Asia/Pacific local version. In addition to this core functionality,
PeopleSoft 8 also includes a Web-based, employee self-service module
called eTime.

Stock Administration This is one of PeopleSoft’s newest HRMS modules.
It supports the tracking of employee stock programs and distributions,
including stock purchase plans, restricted stock payments, and stock options.

FSA Administration This module creates and tracks flexible spending
accounts (FSAs) for employees, including tracking deductions, contributions,
and reimbursements. Most companies outsource this service and therefore
do not need this module. The FSA deduction can be created and tracked
within the Payroll module without the use of this additional module.

Global Payroll This module is new with PeopleSoft 8 and provides a
global payroll engine that supports payroll processes for four countries
initially: the U.K., France, Germany, and Switzerland. Payroll for North
America is still available as well to support the requirements of the U.S.
and Canada.

In addition to those modules, there are specific modules that support the student
administration functions of an academic institution. This application,
called Student Administration, includes functionality that supports academic
advising, admissions, student records, advancement, financial aid, and student
financials.

Also, with the release of PeopleSoft 8, the Federal/Public Sector HR and
Benefits functionality is incorporated into the core HRMS application.

Peoplesoft Technical Interview Questions and Answers

Peoplesoft Technical Interview Questions and Answers
Posted Under Peoplesoft-Interview-Questions

Interview questions on peoplesoft.

1. Explain briefly about record properties?
KEY: the record knows a field, which uniquely identifies each row, as a key.
1. According, to the key field, we will search and retrieve data from the database.
2. This will not allow duplicate and not null values.
3. It is a primary key
4. It allows multiple keys
5. Maximum keys allowed in a record is 15
6. It should be placed at the top of the record definition.

Duplicate Order Key:

1. It provides a way of ordering data in the table when the duplicate values are allowed.
2. It will create index for the column. However the index can be disabled.

Alternate Search key:

1. It identifies the field as a key that provides an alternative path into table data.
2. Due to alternate search key the system becomes slow, because database index will be created when SQL creates the tables, so it will consume the disk space.
3. It allows duplicate values.
4. Alternate search will be seen in the search dialog box when update/display mode is selected.

Descending order key:
It is used to retrieve data in the form of 3-2-1 or C-B-A and normally it is
used in Effective date.

2. What is ordering key?
The following way we have to arrange the fields
1. Field with primary key and search box.
2. Field with primary key
3. Field with duplicate order key.

3. Define Scroll? How many types of scrolls are there?
A Scroll is used to enter multiple rows and retrieve multiple row data. There are two types of scrolls:

1. Nested scrolls – For each repeating data and for each entry with other set of repeating set of data is called a nested scroll. Maximum 3 nested levels are allowed. The nested scrolls share same high-level keys.
2. Independent scrolls – At each scroll level a maximum of 15 screen be included.

4. Define Occurs Level?
Occurs level specifies the level of a scroll bar relative to any other scroll bars on a panel. The following are the occurs level at each nested scroll bar.
The primary scroll bar in a set of nested scroll bars has an occur level 1 A Secondary scroll bar has an occurs level of 2, which means it is subordinate to scroll bar 1.The third scroll bar has an occurs level of 3, which means it is subordinate to scroll bar 2.

5. What are the types of controls that can be placed on the panel?
There are three types of controls:
1. Aesthetic Control: It is used to organize the information on the panel and it is not associated with the database. There are 4 types of aesthetic contents. A) Text B)Frame C) Group box D) Static image.
2. Data entry control: It is used to offer different way to enter and maintain the information. There are 7 types of Data entry controls. A) Edit box B) Drop down list box C) Long edit box D) Check box E) Radio button F) Sub-panel image.
3. Function and data processing control: It adds command and maintains level of information on the command. There are 6 types of data processing controls
A) Command push button B) Process push button C) Secondary panel D)Scroll bar E) Grid control F) Tree.

6.Define Sub-panel and Secondary panel?
Sub-panel : It is a predefined, preside group control, which will appear in more than one panel. (Utility: if you have group of controls, such as address coasts, which you use on multiple panel definition, you can save those panel control groups on sub- panel)
Secondary Panel: An invisible control that associates a secondary panel with a primary panel. (Utility: Secondary panels are used to gather or display supplemental information related to the object that appears in a primary panel)

7. Explain about display control field and related display control field?
Display control field: It is controlling the field in some other record.
Related control field: display control field controls it.

8. What is panel group? Explain in brief about panel group?
Panel group is the bridge between panel and menu. A panel group is a set of panels
That should be processed as if it is one panel. The panel group represents a complete business transaction because all the Information goes together. The group must be stored in the database as a single Unit of work. At runtime, we are opening and saving panel group not individual Panels.

9.Define a Menu? How many types of Menus exist?
Menus are used to navigate the application (Menu is a window to the application)
There are two types of menus:

1. Standard menu: It appears in the menu bar of a Peoplesoft application.
2. Popup menu: Allows the user to navigate related information in other areas of application by right clicking on a panel or panel control.

39. How many types of securities are available in People soft?
There are 6 types of securities:
1. RDBMS Security 2. Network security 3.Operator security 4. Object security 5.Tree level security 6. Query security (row level security)

Peoplesoft Generic Interview Questions and Answers

Peoplesoft Generic Interview Questions and Answers
Posted Under Peoplesoft-Interview-Questions

Peoplesoft Interview Questions and Answers listed below.
1. What is an ERP?
It is a process of integrated flow of Information, which binds on the organization together.

2. What is an ERP system?
It is an integrated application software module providing operational, managerial and strategic Information for improving productivity, quality and quantity to improve competence.

3. Describe the Life Cycle of a Project (ERP Implementation)?
The Project passes through the following stages.
1. Analysis
2. Designing
3. Coding
4. Testing
5. Implementation
6. Maintenance.

4. Define People Tools?
A Collection of software programs, utility scripts and (reports, messages, Cobol SQL statements, database tables and data that provide the frame work for creating, using and modifying people soft applications. People tools provide built in business functionally and maintains the capability that directly increase productivity and simplify system design.

5. What does Application Designer mean?
It is an integrated development environment that is used to develop People soft applications.

6. Functionality of Application Designer?
The following are the uses of Application Designer.
1. Design and create database tables.
2. Design on-line panels
3. Controlling on-line processing flow.
4. Create application windows and menus
5. Providing security for the database.

7. Difference between Two-tier and Three-tier architecture?
Incase of three-tier architecture an application server is connected between the database server (Back end) and the client (front end).
Incase of two-tier architecture the server is directly connected to the client.

8. Advantages of three-tier architecture?
The advantages of three-tier configuration are…
1. Reduce the LAN traffic.
2. Built-in functionality
3. Increase the productivity.
4. No SQL at the client level.
5. Improved performance over WAN.
6. Increased Scalability. (regardless of the number of users connected you can maintain constant performance level)

9. What is a project?
User defined collection of related definitions (fields, records, panels, panelgroups and menus).

10. What are the steps for building an application?
The following are the steps to create application designer.
1. Defining an application.
2. Create field definition.
3. Create Record definition using the fields.
4. Create SQL table.
5. Create Panel.
6. Add panel to the panel group.
7. Add panel group to the Menu.
8. Authorize operators to access Pan.
9. Test.

Peoplesoft Component Interface Interview Questions and Answers

Peoplesoft Interview Questions - Component Interface Builder

1) What are the main elements in the component Interface ?
Main elements of component Interface
? Component interface name
? Keys
? Properties and collections
? Methods

2) Difference between Get keys, find keys and Create Keys ?
Get keys: - These are mapped to the fields marked as scrh in the component’s search record. Automatically “Search key” fields in search record become Get keys.
We cannot change it.

Find keys: - These are mapped to fields marked as Alt or Srch in the component search record. You may remove Find keys that you do not wish to make available for searching.
Create Keys: - It is generated from the key fields for the search record. If Add search record is present then its key fields are taken. We cannot change it.

3) How do you provide security for the component interface?
? Open the Permission list
? On the Component Interface tab
? Add row and select the newly created Component Interface
? Edit the permissions to give permission for the standard methods
? Get, Create, Save, cancel, find.

4) What the steps that you need to do in people code to invoke Component Interface?
? Establish a user section
? Get the component interface definition
? Populate the create keys
? Create an instance of the component interface
? Populate the required fields
? Save the component Interface.

&Session = GetSession();
&CI = &Session.GetcompIntfc(CompIntfc.INTERFACE_NAME)
&CI.KEY_FILED_NAME = ‘NEW’
If not &CI.Create () Then

Else
Populate other fields
End-if;

Populate the other fields

If not &CI.Save () Then
Else
End-if;

5) How do you test Component Interface?
? Using the Component Interface tester
? Give values in the tester for options
? Get Existing, Create new, Find and perform the operation from the CI Tester

6) Catching error message in the component Interface? Or
Use of PSMessages in the CI ?
This function needs to be called when ever methods like Find, Save, Create methods return false.
Error text and Error type can be printed in the log message for any other action in to the log messge.

Function CheckErrorCodes()

&PSMessages = &Session.PSMessages;
&ErrorCount = &PSMessages.Count;
For &i = 1 To &ErrorCount
&ErrorText = &PSMessages.Item(&i).Text;
&ErrorType = &PSMessages.Item(&i).Type;
End-For;

End-Function;

7) What is method? What are the different types of method?
Methods: - A method is an object that performs a very specific function on a component interface at run-time.
Standard methods and user-defined methods.
Standard methods: - Automatically generated upon the creation of a new component Interface in Application.
Apart from the Standard methods there are Standard methods available for the use with any collection.
User-Defined methods: - User-defined methods are those that you can create to meet the requirements of an individual component interface.

8) What are properties?
The Fields in the level 0 in the component are the properties of the component.

Standard properties User-Defined properties
Createkeyinfocollection Developer can further control the exposed Getkeyinfocollection field properties.
Findkeyinfocollection
Property Info collection
GetHistoryItems (Update/Display mode or Correction mode)
EditHistory Items
InteractiveMode.

9) Traversing the Collections in the Component Interface?
COLL_JOB – Collection
Coll_JOBItm – Row in the collection.
&COLL_JOBCol = &CI_JOB_DATA.COLL_JOB;
For &i = 1 to &COLL_JOBCol.Count
&COLL_JOBItm = &COLL_JOBCol.Item (&i);
&COLL_JOB_JRCol = &COLL_JOBItm.COLL_JOB_JR;
For &J = 1 to &COLL_JOB_JRCol.Count
&COLL_JOB_JRItm = &COLL_JOB_JRCol.Item (&j);
&COLL_JOB_JRItm.KEYPROP_EFFDT =;

10) How do you login in correction mode in the Component Interface?
Get History Items and Edit History items property to should be set to true.
Get History Items alone: - Update display all - modes will be used.

IBM Realtime Interview Questions on Peoplesoft

IBM Realtime Interview Questions on Peoplesoft

1. Advantages of People Code?

2. Why SQR is used and advantages of SQR?

3. How are you running your HRMS systems either by JOBCODE or by POSITION_NBR?

4. What is the challenging and complicated People code you have written?
Why do you think it is complicated?

5. What is the challenging and complicated SQR you have written why do you think it is complicated?

6. Steps involved in Data Conversion?

Extract data from the legacy system
Reconcile the extracted data
Identify the tables to be leaded with the new system
Data Mapping
Identify the tools (SQR or Import Manager or SQL Loader etc)
Write programs to perform conversion
Test the programs using test data
Check the data outline
Reconcile concerted data.

7. Why SQR is used?

Data conversion
Reports
Interface programs.

8. SQR Runtime flags?

9. Import SQC files?

10. How do you link SQR reports to process scheduler?

Create/modify/add run control table if you have any new fields
Create/modify/add run control panel if you have any program inputs
Create a menu definition (Note Menu group name: XYZ)
Give operator security
Create Process scheduler definition
Use-Process definition – process definition add
Give report name and report type
Give menu group name (XYZ)

11. How do you create operator classes / users?

12. What are variable types in SQR?

& Data base reference fields – Read only
$ Character (Same for Date)
# Numeric
{ } Variable in ASK or # define
[$ variable] Dynamic variable referencing

13. How do you format in PRINT (9,X, B, etc)?

14. How many types of display views are there in Data Designer?

15. What is a record definition?

16. What are the types of record definitions?

SQL Tables
SQL views
Dynamic views
Derived / Work Records
Sub Records
Query views

17. What is an Application Processor?

18. Types of People code?

Search
Field
Row
Save
Workflow

19. Field Edit Vs Field change?

20. Save edit Vs save pre change Vs save post change?

21. Scroll select Vs scroll select new?

22. SQL EXEC Vs Scroll select?

23. Scroll select Vs Row Scroll select?

24. Row scroll select Vs Row scroll select new?

25. Table loading Sequence (installation)?

Company table
Installation
Location
Department
Salary Plan
Salary step
Job code
Pay group
Benefit Programs

26. What are FUNCLIB* records?