DBMS_PROFILER and performance tunning
What is dbms_profiler and how it is working? How it help in db performance ?
do you know?
how many words do you know
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.
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
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
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
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)
I want to add the following columns to the query:
- MULTILINE_NUM (the original multi-part line number)
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
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.
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.
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
.bacpacfile 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
.bacpacalready 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