New answers tagged oracle
1
You can identify the tablespaces that are empty with this:
select tablespace_name
from dba_tablespaces
where tablespace_name not in (select tablespace_name
from dba_segments)
;
And with that list, drop the selected tablespaces with
drop tablespace <tablespace name>
including contents
and data files;
0
It looks like I can use the getvertices() function and the table() function
:
select
a.line_id,
--sdo_util.getvertices(a.shape),
b.id as vertex_id,
b.x, b.y
from
a_sdo_geometry_tbl a
cross join
table(sdo_util.getvertices(a.shape)) b
order by
a.line_id, b.id;
LINE_ID VERTEX_ID X Y
---------- ---------- ------...
-1
in the dba_hist_sys_time_model, there is a stat_name for 'connection management call elapsed time'. You can check that.
1
If you want the view to be read-only, you can use the with read only option:
create or replace view v_table_test as
select *
from table_test
with read only;
insert into v_table_test values(1)
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view
Otherwise, why shouldn't you be able to perform any DML you want on a view? That also ...
0
Quote from your question:
So in MS SQL I would write something like:
Select Id from table grouped by Name order by Date
But in Oracle it doesn't seem to work
Using a column name in the ORDER BY clause that is not mentioned in the GROUP BY clause seems to be problematic, both with MSSQL and Oracle. MSSQL may complain about ...
Column ... is invalid in the ...
0
This is a correct behavior, when you create a view that references one table, and the structure of the view holds every single field of the table that is constrained for the insert statement, then an insert operation can work, and is intended to happen, since the view becomes a maintenance view, imagine that instead of giving the entire definition of a table ...
0
The connections "/ as sysdba" is using OS authentication. That is, the fact that the logged on user (logged on at the OS) is a member of the OS group "dba", tell oracle that the user is "good to go". If the logged on OS user is not a member of the group "dba", then he would have to supply a username and password. ...
0
The answer will depend on how the tablespace being enlarged is defined. At a minimum you will need to drop the first tablespace to free up space on your storage (see here for syntax):
drop tablespace old_tablespace [including contents and datafiles];
If the datafiles in the tablespace marked for growth were defined with AUTOEXTEND and still have room to ...
0
Not.
What you can do is to change the password.
Since the password hashing is more or less protected it should be possible to decode it somehow but that will take time.
Normally we don't use that password at all.
Normally we don't /hardly use the sys account.
2
PARTITION_OPTIONS
A value of MERGE combines all partitions and subpartitions into one
table.
Example
The following example assumes that the sh.sales table has been
exported into a dump file named sales.dmp. It uses the merge option to
merge all the partitions in sh.sales into one non-partitioned table in
scott schema.
impdp system TABLES=sh.sales ...
0
There are many ways to get what you want but ‘solutions’ that store plaintext passwords or that show them on the command line are wrong.
There is a solution for storing passwords since many versions, called oracle wallet. https://docs.oracle.com/database/121/DBIMI/walet.htm#DBIMI10236
Using this makes scripting a lot easier because you can limit your logon ...
0
It appears that it just isn't recognizing the json_transform() function - but it isn't clear exactly when it was added. It isn't mentioned in the "what's new" sections - though json_mergepatch() is. It's touted as a new 21c feature but, like e.g. SQL macros, seems to have been back-ported to a later 19.x release. it seems to work in 19.11, for ...
1
You are not measuring the read speed. If you dig a bit deeper you will discover that most time is spent formatting the output and writing it to stdout.
For the real read speed a table scan will be quicker than an indexed read.
If you look in the wait events of your session, the event sqlnet more data to client will be the winner, if there are a lot of rows ...
0
The fix was to run the following command in standby before running the BMR recovery in the primary database:
RMAN> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
1
Remove the semicolon at the end of the execute immediate DDL string. Oracle SQL doesn't have statement terminators - semicolons are only used by client tools and the PL/SQL language, and your DDL string is not PL/SQL.
This fails due to the unrecognised semicolon character:
begin
execute immediate 'create table demo (id int);';
end;
This works:
begin
...
0
Change the archive deletion policy to applied on all standbys. This will prevent the situation that an archive can’t be transported because it is deleted after a backup.
And now hope your backup is ok and restore all archivelogs:
restore archivelog from sequence 94742;
After this restart the recovery process.
Monitor your archive space after this setting.
0
If you need to join the result of two queries you can use UNION ALL clause:
select 1 as nm from dual
union all
select 2 as nm from dual;
It would be additional DISTINCT step using UNION without ALL. We don't need this in this case.
If you need to generate record set with subsequential records, good solution is to "misuse" CONNECT BY clause:
...
0
First you need to find the datafiles which can be re-claimed.
Run the below query to identify the datafiles of SYSAUX tablespace which needed to resize.
set verify off
set pages 1000
set lines 350
undef enter_tablespace_name
column file_name format a70 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 ...
-1
Please refer to section "Configuring tnsnames.ora" on the below article.
https://aws.amazon.com/blogs/database/configuring-and-using-oracle-connection-manager-on-amazon-ec2-for-amazon-rds-for-oracle/#:~:text=ora%20or%20tnsnames.,in%20Amazon%20RDS%20for%20Oracle.
1
Excel (and possibly other spreadsheet software) calls this a "pivot table" because you take the row-based groups and essentially pivot them by 90 degrees when you put them into columns.
Since people are familiar with the term it carried over into the SQL world. Example
Note that it is not necessarily the best way to do it, there might be other ...
4
It is variously known as:
conditional sum
filtered aggregation
conditional aggregation
selective aggregation
It may be either written explicitly using CASE or equivalently using FILTER syntax introduced with SQL:2003 e.g. COUNT(*) FILTER (WHERE tornado = 1). Currently Postgres, HSQLDB and SQLite support the new syntax.
1
This error looks similar in this DOC ID : 2649163.1
0
We found the sessions with the ospid(=24108) from the alterlog and did a session trace.
--show Session details
Set lines 500
Set pages 500
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,15) box,
substr(b.username,1,10) username,
-- b.server,
substr(b.osuser,...
0
To add to what Beda says, Oracle should process JSON without any sensitivity to the Locale setting.
This means the JSON text should use the decimal point and not the comma. In fact the comma cannot be supported as a radix character in JSON because it implies the next element in an array or the next key/value pair in an object.
If you see behaviour like that, ...
0
An LEFT (OUTER) JOIN returns all rows that satisfy the join condition and also returns all of rows from left table for which no rows from the other satisfy the join condition. An INNER JOIN returns only all rows that satisfy the join condition.
If IDs from t1 and t2 subqueries don't match, then INNER JOIN will return no rows.
To view the actual execution ...
2
The sql_id is 6jknqnu4qzw02
It's telling you the bad SQL is coming from
Current username=SCHEMA_NAME
Application: xxxx.exe Action:
The SQL is:
begin work
As a side note, I recommend you add basic instrumentation to your application to set the action so you can easily link calls on the DB to a section of your application. You can use dbms_application_info ...
1
This seems that virtual memory configured less than recommended value. I suggest please increase the swap space and restart the gg porcesses.
For determine the swap space look into the below DOC ID : 1528945.1
0
Comma decimals are not legal inside JSON data. For example:
{"pi":3,1415} is not valid.
Therefore, JSON_VALUE input is invalid JSON (and a NULL ON ERROR handler kicks in).
If you see a NULL value then changing from the default to ERROR ON ERROR often helps. See the ERROR ON ERROR doc.
SELECT json_value(metadata, '$.Amount'
...
0
The datafiles are used. If you are over size, you won’t notice this until you stop the database and try to start it again. Weird errors will appear like invalid size errors.
0
The problem was solved by someone else in the team, the differences that I see in the provided setup document are:
Login as sys@xepdb1
Instead of creating new table spaces, we now rename the table spaces in Oracle XE, with the following two commands:
ALTER TABLESPACE temp RENAME TO temporary_data;
ALTER TABLESPACE users RENAME to user_data;
Secondly this ...
1
If we create VARCHAR2(5) or VARCHAR2(5 BYTE), we put constraint on max 5 bytes.
If we create VARCHAR2(5 CHAR), we put constraint on max 5 characters.
In WE8ISO8859P15 every character consists of 1 byte, so VARCHAR2(5) is equivalent to VARCHAR2(5 CHAR) and DATA_LENGTH = CHAR_LENGTH.
In AL16UTF16 every character consists of 2 bytes and in all cases DATA_LENGTH ...
2
The IN predicate accepts either an explicit list of values or a subquery (returning a single column in this case).
In your query, the right side of IN is a list consisting of a single value, where the value is a reference. The reference is invalid because V_TBL1 is undefined in that context. If you want to check that TABLE2.ID is included in the V_TBL1 set,...
1
Considering this the data below (with ties):
colval
-------
1
2
3
4
5
6
7
7
7
This is the shortest way that comes to mind :
select distinct colval
from (select colval, dense_rank() over(order by colval desc) as rn
from [TableName])
where rn = 2
Which returns number 6.
0
you can run sql as below:
update job$ set failures=0 where failures<>0
1
The Embedded PLSQL Gateway is actual code inside the database via the XDB HTTP listener. That is not possible with ORDS.
ORDS is a java process that has a built-in webserver by leveraging Jetty (https://www.eclipse.org/jetty/ ). This allows ords to be run with just a single command like
java -jar ords.war standalone
This will start the webserver on the ...
0
When I try to run one of the long running queries with default jdbc parameters ...
Is there some reason you can't use the method ...
java.sql.Statement.setQueryTimeout()
... to extend the timeout for these particular queries?
1
For XE in a current release you can try 18c.
One problem here is the certification matrix 3 Requirements 18c
Requirement Value
Operating system
Oracle Database XE for Windows x64 is supported on the following operating system versions:
Windows 7 x64 - Professional, Enterprise, and Ultimate editions
Windows 8.1 x64 - Pro and Enterprise editions Windows 10 ...
0
The book is essentially the "PDF version" of Markus' website https://use-the-index-luke.com
The website also has the scripts for each DBMS to create the sample schema and test data: https://use-the-index-luke.com/sql/example-schema
0
first of all, the DESC statement causes ORA-00900 when used in a programming environment, like PHP or Python. o0x258's answer above returns only the CREATE statement of the given table. if you are interested in the fields themselves, you should try something like:
select sys.all_tab_columns.*, sys.all_indexes.uniqueness
from sys.all_tab_columns
left join sys....
2
You are trying to select from a table called 'table_name' in the schema i.
There's is probably no schema i, and if there was, it probably doesn't contain a table called 'table_name'.
What you're trying to do may be resolved using dynamic SQL, but you can't use a variable as table in PL/SQL.
0
At the very least, the statement can be rewritten to avoid hitting table2 twice. For that, you can use an EXISTS predicate:
DELETE FROM
table1
WHERE
EXISTS
(
SELECT
*
FROM
table2
WHERE
table2.taskid = 10502
AND SCN_TO_TIMESTAMP(table1.ORA_ROWSCN) BETWEEN table2.starttime AND table2.finishtime
)
;
In addition to ...
2
I found out that Oracle has a dbms_metadata_diff package. Here is an example how to use it. I don't try it and don't know if this is useful
Here's the example from the page above:
Comparing Object Metadata
Create two tables, TAB1 and TAB2:
SQL> CREATE TABLE TAB1
2 ( "EMPNO" NUMBER(4,0),
3 "ENAME" ...
Top 50 recent answers are included