Restrictions
The following restrictions apply to the RETURNING clause:
The expr is restricted as follows:
Each expr must be a simple expression or a single-set aggregate function expression. You cannot combine simple expressions and single-set aggregate function expressions in the same returning_clause.
Single-set aggregate function expressions cannot include the DISTINCT keyword.
You cannot specify the returning_clause for a multitable insert.
You cannot use this clause with parallel DML or with remote objects.
You cannot retrieve LONG types with this clause.
You cannot specify this clause for a view on which an INSTEAD OF trigger has been defined.
ORA-22816: unsupported feature with RETURNING clause
ORA-06512: at "LDBO.TG_RKPOAPLEDGEUPDATE", line 36
ORA-04088: error during execution of trigger 'LDBO.TG_RKPOAPLEDGEUPDATE'
Problematic Command
update tblrktodel@lnk_Rakshak set nPoastocks=nPoastocks+........
Where cClientcode=lcTerminalcode and cIsincode=lcIsincode and nSegmenttype=lnCommodity and nSourcetable=0 returning nHaircut,nT2Tscrip into lnHaircut,lnT2TScrip ;
Solution
Begin
Select tblRktodel.nHaircut,tblRktodel.nT2Tscrip into lnHaircut,lnT2Tscrip From tblrktodel@lnk_Rakshak Where dTransactiondate=:Old.Dtoftran and cClientcode=lcTerminalcode and cIsincode=lcIsincode and nSegmenttype=lnCommodity and nSourcetable=0 ;
Exception
When NO_DATA_FOUND then
lnHaircut:=0 ;
lnT2Tscrip:=0 ;
End ;
ORA-31626 ORA-31633 ORA-06512 ORA-01950
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_09"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-01950: no privileges on tablespace 'SYSTEM'
ALTER USER system QUOTA unlimited ON SYSTEM;
ALTER USER system QUOTA unlimited ON USR;
ALTER USER system QUOTA unlimited ON INDX;
GRANT UNLIMITED TABLESPACE TO SYSTEM;
ALTER USER ldbo QUOTA unlimited ON SYSTEM;
ALTER USER ldbo QUOTA unlimited ON USR;
ALTER USER ldbo QUOTA unlimited ON INDX;
GRANT UNLIMITED TABLESPACE TO ldbo;
If error is occur at time of software login, plz check tnsping for db service at local / App server.
if error is occur at time of process, plz check the db links.
Tnsping 172.168.1.7:1521/kshitij
Telnet 172.168.1.7 1521
Alter table LDBO.POWEROFATTORNEYSTOCKS add PARTITION SAUDA_APR2019 values LESS THAN (TO_DATE('01-MAY-2019','DD-
Alter table LDBO.TBLSNPRISKDETAILS add PARTITION SAUDA_APR2019 values LESS THAN (TO_DATE('01-MAY-2019','DD-
Features which can help us
Row limiting : Oracle 12c onwards introduces sql syntax for row limiting. This makes it easier to retrieve records in sets for display or processing.
Example :-
create table employee (id integer primary key, name varchar2(10));
insert into employee values (1,'Adam');
insert into employee values (2,'Ben');
insert into employee values (3,'Colin');
insert into employee values (4,'Dean');
insert into employee values (5,'Evan');
insert into employee values (6,'Frank');
insert into employee values (7,'Greg');
insert into employee values (8,'Hank');
insert into employee values (9,'Ian');
insert into employee values (10,'Jack');
commit;
SQL> select * from employee order by id fetch first 3 rows only;
ID NAME
---------- ----------
1 Adam
2 Ben
3 Colin
SQL> select * from employee order by id offset 3 rows fetch next 3 rows only;
ID NAME
---------- ----------
4 Dean
5 Evan
6 Frank
SQL> select * from employee order by id fetch first 50 percent rows only;
ID NAME
---------- ----------
1 Adam
2 Ben
3 Colin
4 Dean
5 Evan
Extended Datatypes VARCHAR2 32767 bytes
it is now possible to create VARCHAR2, NVARCHAR2, and RAW attributes of size 32767 bytes.
Limitations to RESOURCE, SELECT ANY DICTIONARY
Dictionary tables containing password hashes (DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, XS$VERIFIERS) are no longer included in the SELECT ANY DICTIONARY system privilege. This makes it safer to give developers access to dictionary tables for tuning and debugging, without giving them the chance to run brute force attacks ...
Data Redaction via DBMS_REDACT SECURE SENSTIVE DATA(MASKING) which I shared previously
some bulk transactions like "create table as" and "insert into select from" will automatically collect statistics. No need to analyze
Identity columns
In previous versions of Oracle there was no implicit relationship between a primary key and the sequence that maintained it's value, so developers needed to implement this relationship via insert triggers or application code. Oracle 12c removes that requirement for custom coding by implementing the relationship in the CREATE TABLE statement using the IDENTITY clause.
SQL> CREATE TABLE TEST_TABLE (ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY, TEXT VARCHAR2(100));
Table created.
When we insert into the table the ID column is automatically populated.
SQL> insert into test_table (text) values ('aaa');
1 row created.
SQL>commit;
Commit complete.
SQL> select * from test_table;
ID TEXT
------- ----------
1 aaa
When we created the table a sequence was also created.
SQL> select object_name, object_type from dba_objects where owner = 'TEST';
OBJECT_NAME OBJECT_TYPE
--------------- ------------------------------
SYS_C009854 INDEX
ISEQ$$_91602 SEQUENCE
TEST_TABLE TABLE
sequence.nextval as default and identity columns
The new Oracle 12c now allows to define a table with the sequence.nextval directly in the in-line column definition:
SQL> create table bar (
2 id number generated as identity,
3 foo varchar2(50) not null,
4 constraint bar_pk primary key (id)
5 );
Table created.
SQL> insert into bar (foo) values ('baz');
1 row created.
SQL> insert into bar (foo) values ('test');
1 row created.
SQL> insert into bar (foo) values ('whoo');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from bar;
ID FOO
---------- ------------------------------
1 baz
2 test
3 whoo
Concurrent Execution of UNION and UNION ALL Branches Oracle Database 12C release 1
UNION or UNION ALL consists of many queries(branches) that in pre 12C releases were executed one by one.
<BRANCH1>
UNION
<BRANCH2>
<BRANCH1>
UNION ALL
<BRANCH2>
So in pre 12C first is processed BRANCH1 then BRANCH2. Of course each individual query(branch) can be processed in serial or in parallel but only one branch at time.
Oracle 12C allows to run branches(statement) of UNION or UNION ALL concurrently. It means that BRANCH2 can be processed together with BRANCH1 :) . It can even return data faster than BRANCH1.
This feature is turned on automatically and entire UNION or UNION ALL is processed in parallel
Enhancement allows PLSQL declarations in the WITH clause
https://oracle-base.com/
JSON compatiabilty
Already shared
SQL Plus Last Login Time
select USERNAME,LAST_LOGIN from DBA_USERS where LAST_LOGIN is not null;
sequence reset
alter sequence ldbo.FWDBROKUNIQUENUMBER restart start with 1;
SELECT ldbo.FWDBROKUNIQUENUMBER.
default on null
There is new feature: default on null. For example:
CREATE TABLE tab1 (
col1 NUMBER DEFAULT 5,
col2 NUMBER DEFAULT ON NULL 7,
description VARCHAR2(30)
);
So when you try to INSERT null in col2, this will automatically be 7.
Oracle Default User
select * from dba_users where oracle_maintained='Y';
READ ANY TABLE
GRANT READ ANY TABLE TO psmith;
As with the READ object privilege, the READ ANY TABLE system privilege does not enable users to lock tables in exclusive mode nor select tables for update operations. Conversely, the SELECT ANY TABLE system privilege enables users to lock the rows of a table, or lock the entire table, through a SELECT ... FOR UPDATE statement, in addition to querying any table.
PRIVATE TEMPORARY TABLES
Private temporary tables enables Oracle 19c users to create temporary database objects that are automatically dropped at the end of a transaction or a session.
You can create temporary tables with these characteristics:
– NAME must be prefixed by “ORA$PTT_” (or the current value of PRIVATE_TEMP_TABLE_PREFIX initialization parameter)
– DATA is automatically deleted at the end of the transaction (just like GLOBAL TEMPORARY TABLES created with ON COMMIT DELETE ROWS clause)
– DEFINITION is automatically dropped at the end of the transaction (ON COMMIT DELETE DEFINITION) or at the end of the session (ON COMMIT PRESERVE DEFINITION)
This feature is useful for developers as well as DBAs, because it could reduce the number of temporary or “test” tables created in the database.
Example:
CREATE PRIVATE TEMPORARY TABLE ora$ptt_testptt (testcol NUMBER)
ON COMMIT DROP DEFINITION;
SQL CANCELLING
A new command is introduced for DBAs to cancel blocking or too expensive statements instead of kill the originating session.
ALTER SYSTEM CANCEL SQL ‘sid,serial#’;
ALTER SYSTEM CANCEL SQL ‘sid,serial#,sqlid’;
ALTER SYSTEM CANCEL SQL ‘sid,serial#,@inst#’;
ALTER SYSTEM CANCEL SQL ‘sid,serial#,@inst#,sqlid’;
APPROX_COUNT(), APPROX_SUM() and APPROX_RANK() are introduced with Oracle 18c, which can calculate 99% accurate results in very high speed.
select oowncode, approx_count(*)
from sauda
group by oowncode
having approx_rank(partition by oowncode order by approx_count(*) desc) <= 1
order by 1;
DBMS_LOCK.SLEEP is depreciated in Oracle 18c and DBMS_SESSION.SLEEP is introduced, and is available with no additional grants needed.
alter system set "_kolfuseslf" = true scope=spfile sid='*';
restart both node services
select inst_id,name,value from gv$parameter where name like '%kolfuseslf%';
CREATE OR REPLACE
FUNCTION FN_LDVPD (obj_owner IN VARCHAR2, obj_name IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN 'rowid = ''0''';
END FN_LDVPD;
/
begin
dbms_rls.drop_policy (
object_schema => 'LDBO',
object_name => 'ACCOUNTADDRESSDETAIL',
policy_name => 'LD_PAN_POLICY'
);
END;
/
SELECT OOWNCODE,PANGIR FROM ACCOUNTADDRESSDETAIL WHERE TRIM(PANGIR) IS NOT NULL;
BEGIN
DBMS_RLS.ADD_POLICY(object_
object_name=> 'ACCOUNTADDRESSDETAIL',
policy_name=> 'LD_PAN_POLICY',
function_schema=> 'LDBO',
policy_function=> 'FN_LDVPD',
statement_types => 'SELECT',
sec_relevant_cols=> 'PANGIR',
policy_type => DBMS_RLS.SHARED_STATIC,
sec_relevant_cols_opt=> dbms_rls.ALL_ROWS);
END;
/
SELECT OOWNCODE,PANGIR FROM ACCOUNTADDRESSDETAIL WHERE TRIM(PANGIR) IS NOT NULL;
https://www.oracle.com/
install it at Visual Studio machine to select the dll in project
DLL location
Oracle.ManagedDataAccess.dll
C:\Program Files (x86)\Oracle Developer Tools for VS2017\odp.net\managed\common
ORA-01652
Temporary tablespace or server space is full. Please contact to your IT/Server Team.
ORA-01653
Database tablespace or server space is full. Please contact to your IT/Server Team.
ORA-01691
Database tablespace or server space is full. Please contact to your IT/Server Team.
ORA-12801
Check the Server Resources. Please contact to your IT/Server Team.
ORA-12805
Check the Server Resources. Please contact to your IT/Server Team.
ORA-03111
Network/Firewall timeout between application and server. Please contact to your IT/Network Team.
ORA-03135
Network/Firewall timeout between application and server. Please contact to your IT/Network Team.
ORA-00054
Resource is busy. Please try after sometimes.
Command
Alter Table Tblpledgeemaster add nFundingallowed number(16,4) not null /* Contains Total Funding allowed */ ;
Error
ORA-01758: table must be empty to add mandatory (NOT NULL) column
Solution
1) provide a default value for the column along with alter table add column command.
2) Add the column without the NOT NULL constraint then update then modify not null
Alter Table Tblpledgeemaster add nFundingallowed number(16,4) /* Contains Total Funding allowed */ ;
UPDATE Tblpledgeemaster set nFundingallowed= 0; /* Please contact to business team or developer for default value */
commit;
ALTER TABLE Tblpledgeemaster MODIFY (nFundingallowed NOT NULL);
3) Empty the table, apply the NOT NULL and add the data back to the table
