DBMS_PROFILER and performance tunning

2022-05-06 19:45 Abinash Sahoo imported from Stackoverflow

What is dbms_profiler and how it is working? How it help in db performance ?

See also questions close to this topic

Why does this Sql query shows the possible changes but does not implement it?

So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.

SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'

Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.

how to get weeklytotal and yesterday record in mysql in one table

Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.

Table name-dailydata-

Sample data

employee_id	date	total
20	2022-04-25	10
20	2022-04-26	20
20	2022-04-27	20
20	2022-04-28	10
20	2022-04-29	20
20	2022-04-30	30
20	2022-04-31	40
20	2022-05-01	50
40	2022-04-26	20

expected output

employee_id	weekly_total	yesterday_record
20	200	40
40	20	0

mysql query to get weekly data

select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'

Procedure to display the Employees of a specific Department
Create a stored procedure, to display the lastnames as "Name" from the employee table. The requirement is as below: The name of the Procedure : EmployeesDept

Name of the argument as Input : DeptNo

Write the code to create the procedure

Schema

I wrote the Query like this but i didnt get expected output
```
CREATE  PROCEDURE Employeesdept(@Deptno varchar)
AS
Begin
SELECT lastname as name
from employee
where workdept= 'D21'
end
```
Expected output:

Name --------------- Pulaski Jefferson Marino Smith Johnson Perez Monteverde Name ---------------
How to remove non-numeric characters and search for phone number?

SELECT * FROM Customer WHERE REGEXP_REPLACE(HOME_PHONE, '[^0-9]', '') LIKE '%1234567'

This is my SQL query and I get error, Some fields have more than 7 character that’s why I am using % for searching last 7 characters in phone number. FYI I’m using oracle database.
Does oracle jdbc driver only support oracle database (21c,18c,19c etc) or does it support the oracle virtualbox as well?

Does oracle jdbc driver only support oracle database (21c,18c,19c etc) or does it support the oracle virtualbox as well? I have the virtualbox downloaded and somehow the oracle jdbc is not getting installed properly.

Multi-part geometries: Get line & part number when extracting parts (via SDO_UTIL.EXTRACT_ALL)

I have multi-part SDO_GEOMETRIES in Oracle 18c.

I can extract the parts as individual rows using the SDO_UTIL.EXTRACT_ALL() function:

with cte as (
--Each part is wrapped in brackets and separated by commas: (p1),(p2)
select sdo_geometry('MULTILINESTRING((1 1 1, 2 2 2),(3 3 3, 4 4 4))')    as shape from dual union all --3D: (X,Y,Z)
select sdo_geometry('MULTILINESTRING((5 5, 6 6),(7 7, 8 8),(9 9, 0 0))') as shape from dual union all --2D: (X,Y)
select sdo_geometry('MULTILINESTRING((1 1, 2 2))')                       as shape from dual           --2D: (X,Y)
    )
select
    a.object_value,
    sdo_util.to_wktgeometry(a.object_value)
from
    cte,
    table(sdo_util.extract_all(shape)) a

OBJECT_VALUE         SDO_UTIL.TO_WKTGEOMETRY(A.OBJECT_VALUE)                                         
-------------------- ---------------------------------------
[MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0 1.0, 2.0 2.0 2.0)                                           
[MDSYS.SDO_GEOMETRY] LINESTRING (3.0 3.0 3.0, 4.0 4.0 4.0)                                           
[MDSYS.SDO_GEOMETRY] LINESTRING (5.0 5.0, 6.0 6.0)                                                   
[MDSYS.SDO_GEOMETRY] LINESTRING (7.0 7.0, 8.0 8.0)                                                   
[MDSYS.SDO_GEOMETRY] LINESTRING (9.0 9.0, 0.0 0.0)                                                   
[MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0, 2.0 2.0)

db<>fiddle here

I want to add the following columns to the query:

MULTILINE_NUM (the original multi-part line number)
PART_NUM

MULTILINE_NUM PART_NUM OBJECT_VALUE         SDO_UTIL.TO_WKTGEOMETRY(A.OBJECT_VALUE)                               

------------- -------- -------------------- ---------------------------------------
            1        1 [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0 1.0, 2.0 2.0 2.0)                                           
            1        2 [MDSYS.SDO_GEOMETRY] LINESTRING (3.0 3.0 3.0, 4.0 4.0 4.0)                                           
            2        1 [MDSYS.SDO_GEOMETRY] LINESTRING (5.0 5.0, 6.0 6.0)                                                   
            2        2 [MDSYS.SDO_GEOMETRY] LINESTRING (7.0 7.0, 8.0 8.0)                                                   
            2        3 [MDSYS.SDO_GEOMETRY] LINESTRING (9.0 9.0, 0.0 0.0)                                                   
            3        1 [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0, 2.0 2.0)

When adding those columns, I don't want to "fake it" after-the-fact using window functions, etc.

I want to extract that information directly from the geometries, to ensure the numbers are correct (i.e., part number shouldn't be arbitrary; it should reflect the part number order from the original geometry).

Is there a way to get the MULTILINE_NUM and PART_NUM from the table(sdo_util.extract_all(shape)) query?

Oracle : Use of CURSOR%FOUND in PL/SQL
I have a code as follows.

My question is - why do we need IF statement for CURSOR%FOUND ?

If no rows are found, that will trigger NO_DATA_FOUND condition and control will automatically go to the EXCEPTION block.

Correct ?

begin
```
OPEN l_cursor;
LOOP
  FETCH l_cursor into l_rec;
  
  if ( l_cursor%FOUND ) then
      do something
  else
      do_something_else;
  end if;
  
END LOOP;

CLOSE l_cursor

EXCEPTION
   WHEN no_data_found then
     ...
END;
```
select more than 4000 characters in Report Oracle Apex

I have a function in Oracle 21 version that return a CLOB variable, let's call it x (more than 4000 bytes) and I'm selecting this variable to display it in a classic report in Oracle Apex 21

But I always got the error: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 7258, maximum: 4000)

I tried also dbms.lob.substr but it won't work.

Is there any solution to this ?

startup database not coming up srvctl database status up and runinng

Prod db not coming up when starting up.

I h

ave done shutdown and then now starting up but db is not coming up below error

SQL> startup
ORA-39510: CRS error performing start on instance 'TSU' on 'TSU'
CRS-5702: Resource 'ora.tsu.db' is already running on 'dztup'
CRS-0223: Resource 'ora.tsu.db' has placement error.
clsr_start_resource:260 status:223
clsrapi_start_db:start_asmdbs status:223

i checked the srvctl status db is running up.

SQL Server - Bacpac import issue

I have a .bacpac file of database (created from azure). The database contains master key and certificate attached. Now, I have to import data into my local instance, I am using import-data tier feature but it gives error related to you have already database attached and create master key first. I didn't understand because I don't have that database in my local yet and .bacpac already has master key.

Why does it say: "create master key first" ?

This is the error screenshot:
Script causes this error, Import database from Oracle 19c to Postgres 12

Good day everyone! I'm trying to migrate from Oracle to Postgres according to the instructions https://habr.com/en/post/598313/, but I ran into a problem... I managed to upload it, but importing into the Postgres database didn't work.

[root@localhost client]# ./import_all.sh start -d client -o postgres -U postgres

ERROR: you must give a PostgreSQL database name (see -d option).

-a import data only -b filename SQL script to execute just after table creation to fix database schema -d dbname database name for import -D enable debug mode, will only show what will be done -e encoding database encoding to use at creation (default: UTF8) -f force no check of user and database existing and do not try to create them -h hostname hostname of the PostgreSQL server (default: unix socket) -i only load indexes, constraints and triggers -I do not try to load indexes, constraints and triggers -j cores number of connection to use to import data or indexes into PostgreSQL -n schema comma separated list of schema to create -o username owner of the database to create -p port listening port of the PostgreSQL server (default: 5432) -P cores number of tables to process at same time for data import -s import schema only, do not try to import data -t export comma separated list of export type to import (same as ora2pg) -U username username to connect to PostgreSQL (default: peer username) -x import indexes and constraints after data -y reply Yes to all questions for automatic import

Maybe I'm typing the wrong command...

I would be very grateful for help

Related questions

DBMS_PROFILER and performance tunning

See also questions close to this topic