New answers tagged oracle
0
If so, can I call an existing script to create a procedure or would I
need to paste the PLSQL into the procedure script?
Both works.
DBMS_SCHEDULER.CREATE_JOB
The type of the job (job_type) can be PLSQL_BLOCK or STORED_PROCEDURE as well.
For PLSQL_BLOCK, you can specify an anonymous block, a piece of PL/SQL code such as begin ... end;.
For ...
1
If you do that, the statement is rolled back, not the transaction. Below I hit Ctrl+C after starting the UPDATE.
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> insert into t1 select * from t1;
23849 rows created.
SQL> insert into t1 select * from t1;
47698 rows created.
SQL&...
1
That would depend on what the "same" patches are. At the very least, you'd need the patch specifically for the new version of the database. But it could be that the the 11g patch fixed something in 11g that was inherently fixed in 12c. You just have to look at each patch on a case-by-case basis. Also, are you talking about individual patches or patchset ...
2
Known issue:
https://community.oracle.com/thread/4125750
ORA-39006, ORA-39065 and ORA-00942: table or view does not exist When Starting DataPump in 12.2 (Doc ID 2341607.1)
Rebuild the missing part by running:
catalog.sql
catproc.sql
utlrp.sql
4
Simple addition and subtraction.
Although the code is SQL code, the math works the same in PL/SQL.
This code:
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
with data as (
select TO_date( '13/10/2019 00:00:00', 'DD/MM/YYYY HH24:MI:SS') as foo
, to_date( '01/10/1914 16:33:11', 'DD/MM/YYYY HH24:MI:SS') as bar from dual
)
select ...
-3
You can use DATEPART to pull the hour/min/sec from BAR and do the same to pull day/month/year from foo, them use DATEFROMPARTS to reassemble.
Pity SQL Server doesn't support the ANSI INTERVAL type, which would make this super simple.
2
Use a local connection.
sqlplus sys as sysdba
or
sqlplus / as sysdba
In your output, you used a remote connection. Database instances can be started through remote connections only if you have registered them in the listener manually with static registration.
0
It's really impossible to suggest any real improvements without knowing the table definition and some representative data. But one thing pops out immediately. THere is no reason to convert a DATE to a VARCHAR to a NUMBER just to compare. You can compare dates directly -
Instead of
AND TO_NUMBER(TO_CHAR(ADD_MONTHS(ANALYZED_DATE,0),'YYYYMM')) = TO_NUMBER(...
0
The referenced MOS just states the following:
Dblinks do not support TAF. This refers to FAILOVER_MODE parameter, not FAILOVER. The first parameter is about TAF, the second is about connect-time failover.
It is not recommended to use the SCAN address for dblinks in case of RAC. Instead of the SCAN address, use the VIP addresses.
Removing FAILOVER parameter ...
-3
Can you please try to use the proper alias syntax?
from T_D_DATES as td
But I don't really see the advantage of receiving a temporary table made up of select results over JUST receiving said select results....
So in my eyes the whole test function (if you want some kind of stored procedure or similar) could be shortened
3
That will not work like that. test_table is a table of test_row objects, not 2 columns, so you need to create test_row objects from the 2 columns, like below:
create or replace FUNCTION test_function
(
p_year_start_in integer,
p_year_end_in integer
) RETURN test_table AS
v_ret test_table;
p_start date;
p_end date;
BEGIN
...
0
If you don't want call a function several times, you may wrap it into subselect and then treat its result as my_address_detail_t several times
SELECT
SERVICE_TYPE,
ADDR.apt_number,
ADDR.district_name,
ADDR.zipcode,
ADDR.whatever
FROM (SELECT SERVICE_TYPE, FN_GET_ADDRESS_DETAIL(USER_ID)
FROM REG_ADDRESS);
BTW, a believe even you write:
...
2
You can get invalid views with a procedure like this:
DECLARE
CURSOR InvaliViews AS
SELECT OBJECT_NAME
FROM USER_OBJECTS
WHERE object_type = 'VIEW'
AND status = 'INVALID';
BEGIN
FOR aView IN InvaliViews LOOP
UTL_MAIL.SEND(
sender => 'noreply@your_domain.com',
recipients => 'your-email@...
4
No, it is effectively impossible. The RDBMS is simply too complicated. The block structure has been reversed engineered, but to even begin to do what the RDBMS itself does with those blocks is a gigantic task.
If your goal is to speed up INSERTs, then you can use an /*+APPEND*/ hint in some situations to speed things up, or use Data Pump to do the imports.
...
1
Administering the Audit Trail
begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP
(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
last_archive_time => sysdate - 7
);
end;
/
begin
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL
(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
use_last_arch_timestamp => true
);
end;...
1
DBA_RESUMABLE
...
TIMEOUT | NUMBER | Timeout of the resumable statement
...
Session 1:
SQL> select sys_context('userenv', 'sid') from dual;
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
53
SQL> alter session enable resumable timeout 600 name 'hello world';
Session altered....
1
You can define the environment (TNS_ALIAS) before launching sqlplus.
First let's create two users (a and b)
$sqlplus system/oracle@XEPDB1
SQL*Plus: Release 18.0.0.0.0 Production on Wed Oct 9 13:59:04 2019
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Wed Oct 09 2019 13:56:31 +02:00
Connected to:
...
0
Sure, pass it as a parameter too.
sqlplus MY_USER/pswd@TNSID @script.sql TNSID par2 par3
Then you can use the below in the script:
connect MY_USER2/pswd@&1
0
My guess is that there is a logon trigger that checks the application name. Apparently written by someone who doesn't understand that the name of the application can not be relied upon.
The following advice might get you in trouble, use with caution!
Oracle's JDBC driver lets you specify the application name through a connection property.
SQL Developer ...
1
Per Oracle Support, this is probably a bug
https://support.oracle.com/knowledge/Oracle%20Database%20Products/2322290_1.html
Function kcidr_file_header_check_common is File Header check which cause of this issue. If you see this in the Stack Trace and Standby opens Read only successfully then you are most likely encountering this bug.
2
What you described is normal behaviour.
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
delete from t1 where object_id is null;
delete from t2 where object_name = 'T1';
alter table t1 add primary key (object_id);
alter table t2 add constraint t1_fk foreign key (object_id) references t1(object_id);
create index ...
1
DESC / DESCRIBE is a client command (eg SQL*Plus) and not available in PL/SQL.
You can try querying the user_arguments or all_arguments view. It is relatively simple as long as you don't have overloaded procedure / functions and nested data types or weird procedure / variable names.
1
On its face, the solution is in fact simple and consists of the following basic steps, performed as the oracle user:
Change N to Y on only entry in /etc/oratab
Start the listener: lsnrctl start
Start the database(s): dbstart
Automating the above steps is a bit more onerous.
Create Startup and Shutdown Scripts
Create the directory in which to store the ...
1
show parameter db_create_file_dest will point to default datafile location.
To change default location execute this command
alter system set db_create_file_dest='target_dir' scope=both;
(change is immediate no need to bounce the database)
-2
at the command prompt (cmd) type
tnsping dbname
The description string you get is the connect information. The script below should create the basic connect information for a linked server to Oracle in SQL Server management studio.
EXEC master.dbo.sp_addlinkedserver @server = N'ORCL12C', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'...
0
Regarding the ORA-01555: snapshot too old errors with select statements - this usually happens when you have a long running query with data changes whilst it's running, and an undersized UNDO tablespace.
When you run a select statement Oracle makes it read-consistent, meaning that any changes to the data after you started the query won't be seen by the ...
1
No, it is not circular, alter database open is the 3rd step.
If you have a backup of that datafile, then restore and recover it.
If you do not have a backup, then do what the post you referenced tells you. Since your database is already mounted, you can skip step 1.
If the database is down, mount it.
STARTUP MOUNT;
Offline drop the ...
2
Sure, it is called splitting a partition.
Splitting a Partition of a Range-Partitioned Table
The below example (taken from the above URL) creates a table with quarterly partitions, then splits a quarter into monthly partitions:
CREATE TABLE orders
(prod_id NUMBER(6),
cust_id NUMBER,
time_id DATE,
channel_id CHAR(1),
promo_id ...
0
For what it's worth, the real table that this question applies to is the asset table in Maximo 7.6.1.1.
The asset table in Maximo does not have a unique index on the assetnum column (nor a primary key on assetnum).
However, it does have a unique index on the assetnum and the siteid columns.
While the index isn't exactly what I want, perhaps it will help ...
0
The NOLOGGING mechanism, as you pointed out, is to avoid the generation of redo logging information, and that can make bulk loading faster.
I use it typically for big loading jobs. For example, loading terabytes of satellite imagery: it means that I will write TB of bits to the database proper (the data files), but that I will also write terabytes to the ...
2
There is no such hint as NOLOGGING.
Sure, UPDATE and DELETE operations can not take advantage of NOLOGGING, but there are other operations that can:
logging_clause
NOLOGGING is supported in only a subset of the locations that support
LOGGING. Only the following operations support the NOLOGGING mode:
DML:
Direct-path INSERT (serial or ...
0
I can't tell you why this happens, but I get such errors regularly with the pluggable database. What I do as a quick fix is I connect using sqlplus "/ as sysdba", then I run the following code for that specific database:
alter pluggable database orclpdb open;
Or the following for all databases:
alter pluggable database all open;
And I stop getting the ...
3
Nothing. count(1) is implicitly transformed into count(*). See below:
SQL> select tracefile from v$process where addr = (select paddr from v$session where sid = sys_context('userenv', 'sid'));
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl_o71/ORCL/trace/ORCL_ora_3610.trc
SQL&...
3
Think about it. Such a join without further information can not be skipped. Even if you do not select columns from maximo_assets, the join to it may increase the number of rows returned, because for 1 ID in gis_sidewalks, you can have multiple rows in maximo_assets with the same ID.
If the IDs are unique (or PK), and you make this known to the database by ...
0
It looks like using a subquery instead of a join might do the trick:
create or replace view gis_sidewalks_vw_2 as (
select
s.id,
s.last_edited_date as gis_last_edited_date,
case when
s.last_edited_date > (select lastsyncdate from maximo_assets a where s.id = a.id) then 1
end as sync_needed
from
gis_sidewalks s
);
select
...
2
If the systems support / allow it, I would be inclined to build a queuing table and use triggers, in the app or in the db, as applicable, to insert sync requests into the queuing table. You could then have a job that reads the queuing table, does the requested syncs, maybe copies the sync request to a sync-audit table, then deletes the sync requests. If a ...
1
There is no maximum. Neither in a query result nor in a subquery or CTE.
If the table has a thousand rows, the query will return a thousand rows.
If it has a billion, it will return a billion rows. If it's a trillion, a trillion rows will be returned.
Whether the receiving application can handle so much data is another matter.
0
On cmd, type echo %ORACLE_HOME%. If ORACLE_HOME is set it will return you the path or else it will return %ORACLE_HOME%.
0
While TKProf is generally referred to as something that makes a trace file 'human readable' it is primarily a Profiling tool that aggregates the SQL statements to find the most impactful.
The raw trace file can be used. However if you are primarily interested in what the client sends to the server, you should look at logging options in JDBC (
https://www....
0
Yes, all_objects (oracle-10g+), restricts data retrieval from views (even with grant-select-to-view privilege to user/role); Can be fixed by granting 'select any table' privilege to user/role. Also to get views, packages, functions and procedures you may need to grant 'select_catalog_role' privilege to user/role. Other privileges like 'resource', 'create ...
2
A global temporary table is global, because its definition is persisted an other sessions can use the table as well, given that they have the necessary privilege on it. The content of the table is of course specific to the session using it.
Creating Global Temporary Tables
Global temporary tables are permanent database objects that are stored
on disk ...
1
You can try to check what the current consumer group for the different sessions are. I.e. log in as SAMPLE and USER1 and run
SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION
WHERE USERNAME IN ('SAMPLE','USER1','DBA1');
You can also check what the actual plans are assigned as DBA:
SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS
WHERE ...
1
Why can't you use null values in joins? In Oracle, both of the following don't evaluate to true:
NULL = NULL
NULL <> NULL
That's why we have IS NULL / IS NOT NULL to check for null values.
To test this, you can simply do:
SELECT * FROM table_name WHERE NULL = NULL
Joins are evaluating a boolean condition, and they didn't program them to operate any ...
2
Your real problem is not about storing the password in a table securely, but the need for repeatedly changing and recreating the dblink.
Yes, you can store a password in a wallet, and no, you will not be able to retrieve the actual password from the wallet.
To store a password securely in a table, you can encrypt it (e.g. with DBMS_CRYPTO) and store the ...
2
Are you running flashback database? If you were you could run a process that would start just after the top of every hour. The process would look at each table as it was at the beginning of every hour and do a Merge command between the source and destination databases for a one way sync. You would then be able to replicate all of the tables every hour. If ...
1
In the GIS tables, I can add a gis_last_edit_date field using out-of-the-box editor tracking functionality.
In the WMS, I'll figure out a way to populate a wms_last_sync_date field.
From there, I can create views on the GIS tables:
Using a database link and a join, the views would compare the gis_last_edit_date with the wms_last_sync_date.
If the ...
Top 50 recent answers are included
