DBA Blogs

alter table xxx shrink not reclaiming space

Tom Kyte - Tue, 2018-01-09 03:06
Dear Ask Tom Team, I hope you had wonderful vacations and happy new year!!! I have question related to Table's Space Reclaiming using "shrink" command. There are some fragmented tables in our DB. I found their current size and wasted space u...
Categories: DBA Blogs

Changing dbms_job to dbms_scheduler

Tom Kyte - Tue, 2018-01-09 03:06
Hi Tom, I have a requirement where I need to change dbms_job functionality to dbms_scheduler.Regarding that I have few query : 1.The existing dbms_job.submit_job is called within loop which calls certain procedure ex: for I in 1..10 dbms_job.su...
Categories: DBA Blogs

Export application through command prompt

Tom Kyte - Tue, 2018-01-09 03:06
I have moved some pages from 1 application to another but when exporting application through the application itself. Then some component is not working of the application. So can you provide an alternative way to export application through command p...
Categories: DBA Blogs

ORA-01722: invalid number ORA-06512: in "SYS.DBMS_SQL" - Fetching cursor with bind variables

Tom Kyte - Tue, 2018-01-09 03:06
Hi, I'm trying do run the following PL/SQL block: <code>DECLARE v_trad_cur CLOB; v_trad_par VARCHAR2 (1000); cur PLS_INTEGER := DBMS_SQL.open_cursor; fdbk NUMBER; retorn VARCHAR2 (1000); vd...
Categories: DBA Blogs

pdb_to_apppdb.sql returns ORA-65021- A workaround

Oracle in Action - Tue, 2018-01-09 01:26

RSS content

While exploring Oracle Multitenant Application Containers, I learnt that in order to convert a regular PDB  to an application PDB

  • Clone a regular PDB  into an application root
  • Connect to the cloned  PDB and execute the $ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql to convert the cloned regular PDB  to an application PDB

However, when I connected to cloned PDB remotely using @… and executed the script pdb_to_apppdb.sql, I got ORA-65021 :

SQL>@$ORACLE_HOME/rdbms/admin/pdb_to_apppdb

 

 

.

.

.

.

 

SQL>create or replace view sys.cdb$common_root_objects&pdbid sharing=object as

2  select u.name owner, o.name object_name, o.type# object_type, o.namespace nsp,

3         o.subname object_subname, o.signature object_sig,

4         decode(bitand(o.flags, &sharing_bits),

5                &edl+&mdl, 'EDL', &dl, 'DL', 'MDL') sharing

6    from sys.obj$ o, sys.user$ u

7   where o.owner#=u.user# and bitand(o.flags, &sharing_bits) <> 0

8     and bitand(o.flags,&fedobjflag)=&fedobjflag;

old   1: create or replace view sys.cdb$common_root_objects&pdbid sharing=object as

new   1: create or replace view sys.cdb$common_root_objects4 sharing=object as

old   4:        decode(bitand(o.flags, &sharing_bits),

new   4:        decode(bitand(o.flags, (65536+131072+4294967296)),

old   5:               &edl+&mdl, 'EDL', &dl, 'DL', 'MDL') sharing

new   5:               4294967296+65536, 'EDL', 131072, 'DL', 'MDL') sharing

old   7:  where o.owner#=u.user# and bitand(o.flags, &sharing_bits) <> 0

new   7:  where o.owner#=u.user# and bitand(o.flags, (65536+131072+4294967296)) <> 0

old   8:    and bitand(o.flags,&fedobjflag)=&fedobjflag

new   8:    and bitand(o.flags,134217728)=134217728

create or replace view sys.cdb$common_root_objects4 sharing=object as

*

ERROR at line 1:

ORA-65021: illegal use of SHARING clause

After various repeated trials, I realized that connecting to cloned PDB using “Alter session set container ..“ results in successful execution.

Here is the full article where I encountered this error and found out the workaround.

I faced similar issue while creating application seed from application root and resolved it by connecting to the  application seed  created from application root using Alter session set container …

Conclusion:  In order to execute the script $ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql to convert the cloned regular PDB  to an application PDB, connect to the target regular PDB by switching the container. (Do not connect remotely using @)

References:



Tags:  

Del.icio.us
Digg

Copyright © ORACLE IN ACTION [pdb_to_apppdb.sql returns ORA-65021- A workaround], All Right Reserved. 2018.

The post pdb_to_apppdb.sql returns ORA-65021- A workaround appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Partner Webcast – Accelerate Your Digital Transformation in the Cloud with Oracle Visual ...

With increasing demands for modern business applications that will serve specific business needs, and the proliferation of data sources, the speed at which IT departments address line of business...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partner Webcast – Accelerate Your Digital Transformation in the Cloud with Oracle Visual ...

With increasing demands for modern business applications that will serve specific business needs, and the proliferation of data sources, the speed at which IT departments address line of business...

We share our skills to maximize your revenue!
Categories: DBA Blogs

How to cancel SQL statements and disconnect sessions in #PostgreSQL

The Oracle Instructor - Mon, 2018-01-08 12:29

In PostgreSQL, you can cancel problem statements or terminate offending sessions remotely with PG_CANCEL_BACKEND and PG_TERMINATE_BACKEND. This article shows how you can do it with working examples.

edb=# select current_database,current_user;
 current_database | current_user 
------------------+--------------
 edb              | enterprisedb
(1 row)

I’m connected as superuser. The demo is done with EDB Postgres Advanced Server 10.1.5 but the shown technique should work the same with other Postgres distributions and older versions. First I create a demo user:

edb=# create role adam password 'adam' login;
CREATE ROLE
edb=# grant connect on database edb to adam;
GRANT

Now opening another session with that new user:

-bash-4.2$ psql -U adam
Password for user adam: 
psql.bin (10.1.5)
Type "help" for help.

edb=> begin
edb$> loop
edb$> null;
edb$> end loop;
edb$> end;

That session burns CPU now in an endless loop. Back to the superuser session:

edb=# select pid,usename,query
edb-# from pg_catalog.pg_stat_activity where datname='edb';
  pid  |   usename    |                         query                         
-------+--------------+-------------------------------------------------------
 14346 | adam         | begin                                                +
       |              | loop                                                 +
       |              | null;                                                +
       |              | end loop;                                            +
       |              | end;
  5517 | enterprisedb | select pid,usename,query                             +
       |              | from pg_catalog.pg_stat_activity where datname='edb';
(2 rows)
This cancels the SQL statement of one session:
edb=# select pg_cancel_backend(14346);
 pg_cancel_backend 
-------------------
 t
(1 row)

The session that was doing the endless loop gets this output:

ERROR:  canceling statement due to user request
CONTEXT:  edb-spl function inline_code_block line 3 at NULL
This disconnects a single session:
edb=# select pg_terminate_backend(14346);
 pg_terminate_backend 
----------------------
 t
(1 row)

The disconnected session gets this output after trying to do anything:

edb=> \d
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
This way you cancel all SQL statements of a certain user:
edb=# select pg_cancel_backend(pid) from pg_stat_activity where usename='adam';
This way you disconnect all sessions of a certain user:
edb=# select pg_terminate_backend(pid) from pg_stat_activity where usename='adam';

In the unlikely event that your end users are connected with psql, it will try to reconnect them. That’s the background of the above listed output “… Attempting reset: Succeeded.” Means the session got reconnected. If you want to prevent that particular user from (re-)connecting, you need to do this additionally:

edb=# revoke connect on database edb from adam;
REVOKE ROLE

Remember to also revoke from public if you haven’t done that already, otherwise the above revoke doesn’t show any effect:

edb=# revoke connect on database edb from public;
REVOKE
Now this disconnects all sessions of one user and the above prevents new connects by that user:
edb=# select pg_terminate_backend(pid) from pg_stat_activity where usename='adam';
 pg_terminate_backend 
----------------------
 t
(1 row)

The terminated session then gets this output:

edb=> \d
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Keep in mind that the revoke is impacting the whole user, not just one particular session. In other words no session with that user can be established subsequently until you say:

edb=# grant connect on database edb to adam;
GRANT ROLE
You can terminate all sessions connected to one particular database – except your own session – like this:
edb=# select pg_terminate_backend(pid)
edb-# from pg_stat_activity
edb-# where datname='edb' and pid<>pg_backend_pid;

Once again, this does not prevent new sessions from connecting. So either you REVOKE CONNECT on the user layer as shown above, or you do it on the database layer.

This is how normal users are prevented from connecting to the database:
edb=# alter database edb with connection limit 0;
ALTER DATABASE

Superusers can still connect. The above is the equivalent to ALTER SYSTEM ENABLE RESTRICTED SESSION in Oracle. This is what normal users get now upon trying to connect:

-bash-4.2$ psql -U adam
Password for user adam: 
psql.bin: FATAL:  too many connections for database "edb"
The default of unlimited number of sessions allowed to connect can be set back like this:
edb=# alter database edb with connection limit -1;
ALTER DATABASE
This prevents also superusers from connecting to a database, but you must not be connected to that database yourself:
edb=# alter database edb with allow_connections false;
ERROR:  cannot disallow connections for current database

So either you connect to another existing database in the cluster or you create another database temporarily:

edb=# create database dummy;
CREATE DATABASE
edb=# \c dummy
You are now connected to database "dummy" as user "enterprisedb".
dummy=# alter database edb with allow_connections false;
ALTER DATABASE

Now both normal users and superusers get this output when trying to connect:

psql.bin: FATAL:  database "edb" is not currently accepting connections
The default that connections to that database are allowed can be set back with this command:
dummy=# alter database edb with allow_connections true;
ALTER DATABASE
dummy=# \c edb
You are now connected to database "edb" as user "enterprisedb".
edb=# drop database dummy;
DROP DATABASE

What I like especially about the shown functionality is the option to remotely cancel a particular (ongoing) statement without having to terminate the session that runs the statement. I’m not aware of a supported way to do that in Oracle. It can be done if Resource Manager has been configured appropriately beforehand, but that requires quite some effort and doesn’t work just out of the box.


Tagged: PostgreSQL
Categories: DBA Blogs

DISTINCT clause and nulls

Tom Kyte - Mon, 2018-01-08 08:46
I have a table Test_Null with columns A, B and Data Present Inside Table is: SQL>select * from test_null; A B ---------- ------ 1 NULL 2 NULL 3 NULL 4 NULL <u>Query</u> SELECT DI...
Categories: DBA Blogs

ORA-01417 when outer joining many tables in 11g but not 12c

Tom Kyte - Mon, 2018-01-08 08:46
Hi, I ran this below statement in 12C and 11g Data bases. <code>select * from temp0101 a ,temp0101 b ,temp0101 c where 1 = 1 and a.sno = b.sno(+) and c.sno = b.sno(+);</code> in 12 C it executed successfully but in 11i it throwed below error...
Categories: DBA Blogs

Partner Webcast - Data Archives (Tape)

            Data Archives (Tape) - When you...

We share our skills to maximize your revenue!
Categories: DBA Blogs

RMOUG Training Days 2018 early registration deadline rapidly approaching

Bobby Durrett's DBA Blog - Fri, 2018-01-05 10:02

The deadline for early registration for RMOUG Training Days 2018 is rapidly approaching. The deadline is January 12. The early registration fee is $385 for RMOUG members and $450 for non-members. There is a packed agenda over the three-day event. The hotel costs are reasonable at $159/night which is very good compared to the hotel prices at the vendor conferences in San Francisco. RMOUG is reasonably priced and high quality training. With all of the change going on with cloud computing and some of the database systems that are competing with Oracle now I am looking forward to hearing from other people about how they are adapting in this changing climate.

I am giving two talks which are both about personal development for DBAs. One is about whether a DBA should learn the Python programming language. The other is about whether DBAs could benefit from communication and leadership training through Toastmasters. Here are the dates and times for my two talks:

Toastmasters for the Oracle DBA
Thursday, February 22
Session 12, 1:30 pm – 2:30 pm

Python for the Oracle DBA
Thursday, February 22
Session 14, 4:00 pm – 5:00 pm

As things change it makes sense for DBAs to continue to develop themselves and I think that my talks and the conference in general will help us to move forward by preparing us to meet the challenges that the future will bring. I hope to see you in Denver and don’t forget to register by January 12.

B0bby

 

Categories: DBA Blogs

Its Cloud Service, Not Oracle 18c RDBMS which is Self-Driving and Autonomous

Pakistan's First Oracle Blog - Thu, 2018-01-04 20:08
Look at the following picture displayed at Oracle's official website here, and you would forgive anyone who would naively believe that the Oracle 18c is a self-driving, autonomous database.





Now, in above mentioned article after reading the title and the first paragraph, one still maintains the notion that Oracle 18c is autonomous and self-driving database. Its only after reading second paragraph carefully, one should understand the true picture.

The second paragraph in that article clearly says that self-driving and autonomous is Oracle Autonomous Database Cloud  powered by Oracle 18c database and not the Oracle database 18c itself.

So this autonomy is about cloud service and not about the RDBMS. This cloud service could very well run on Oracle 12c and say the same or any other version for that matter. DBA's role in such managed database cloud services is still nominal. So if its not on the cloud then DBA's role is more challenging then before as every new version is packed with new features.
Categories: DBA Blogs

Oracle MOOC: Introduction to NodeJS Using Oracle Cloud (2018)

 Oracle Application Container Cloud Service lets you deploy Java SE, Node.js, PHP, Phyton, Ruby and Go applications to the Oracle Cloud. You can also Java EE web...

We share our skills to maximize your revenue!
Categories: DBA Blogs

SQLNET.COMPRESSION

Tom Kyte - Wed, 2017-12-27 04:06
Hi Tom, I would like to get your opinion this new parameter SQLNET.COMPRESSION for network performance and the additional ones (compression level) in 12c, and any recommendations to use it under some circumstances. I know this is a very vague q...
Categories: DBA Blogs

UNUSABLE INDEX

Tom Kyte - Wed, 2017-12-27 04:06
Hi Tom, I have a table 'F_CUST_TRANSACTION_LINE_ITEMS' with primary key.I want to load rows here 11 Billons through Informatica (SQLLOADER). While loading completed first 5 Millon rows,I checked index(Primary key) status has been changed vali...
Categories: DBA Blogs

Domain Indexes -- 2 : STOPLIST for a Context Index

Hemant K Chitale - Tue, 2017-12-26 09:29
In the previous example, I created a simple CONTEXTIndex.
For a CONTEXT Index, Oracle automatically applies a default "STOPLIST".  This is a list of common words that are automatically excluded from the Index.
If you notice in the previous post, the words "this", "is", "a", "to", "be" are all in the MY_TEXT column of the MY_TEXT_TABLE but do not appear in the list of tokens in DR$MY_TEXT_INDEX$I.


Can we expand and build our own STOPLIST ?

I start with a new table containing the same rows :

SQL> connect ctxuser/ctxuser
Connected.
SQL> create table another_text_table
2 as select id_column as id_column,
3 my_text as another_text
4 from my_text_table;

Table created.

SQL> select another_text from another_text_table;

ANOTHER_TEXT
--------------------------------------------------------------------------------
This is a long piece of text written by Hemant
Another long text to be captured by the index

SQL>


I then build a custom STOPLIST (which I name as "another_text") and then create the CONTEXT Index.

SQL> begin
2 ctx_ddl.create_stoplist('another_text');
3 ctx_ddl.add_stopword('another_text','Hemant');
4 ctx_ddl.add_stopword('another_text','long');
5 end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> create index another_text_index
2 on another_text_table(another_text)
3 indextype is ctxsys.context
4 parameters('stoplist another_text');

Index created.

SQL>


This time, I don't have to use CTX_DDL.SYNC_INDEX as the Index is created after the table is populated. Nevertheless, if I execute DML (INSERT, UPDATE or DELETE) to change data, I will need a call to CTX_DDL.SYNC_INDEX to update the Index.

Now, I check the Tokens that are created in this index.

SQL> select token_text, token_count
2 from dr$another_text_index$i
3 order by token_text
4 /

TOKEN_TEXT TOKEN_COUNT
---------------------------------------------------------------- -----------
A 1
ANOTHER 1
BE 1
BY 2
CAPTURED 1
INDEX 1
IS 1
OF 1
PIECE 1
TEXT 2
THE 1
THIS 1
TO 1
WRITTEN 1

14 rows selected.

SQL>


The STOPLIST words that I defined ('Hemant' and 'long') are not in the Tokens list.  However, since I defined my own Custom STOPLIST, Oracle's default BASIC_STOPLIST has been overriden.  So, now the Tokens list includes words like "a", "be", "by", "is" etc.

Contrast this with the Tokens list in the CONTEXT index that was created on MY_TEXT_TABLE using the default BASIC_STOPLIST :

SQL> select token_text, token_count
2 from dr$my_text_index$i
3 order by token_text
4 /

TOKEN_TEXT TOKEN_COUNT
---------------------------------------------------------------- -----------
ANOTHER 1
CAPTURED 1
HEMANT 1
INDEX 1
LONG 2
PIECE 1
TEXT 2
WRITTEN 1

8 rows selected.

SQL>


So, if you want to build a Custom STOPLIST, make sure you identify all the "common" words you want excluded.
.
.
.

Categories: DBA Blogs

Data Warehouse In The Cloud (DWaas)

Are you unable to scale your compute platform to meet the current growth in data , is it difficult to handle multiple data types in an era of IoT and Big data? All these questions will come up in...

We share our skills to maximize your revenue!
Categories: DBA Blogs

@OracleIMC Season’s Greetings

      from all of us in Oracle at the EMEA Cloud Centers of Excellence and the Innovation and Modernization...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partner Webcast – Practical use cases of Oracle API Platform Cloud Service

Why do Application Programming Interfaces (API) and API Management matter? What would happen if we woke up tomorrow and APIs didn’t exist? We know they are a part of modern technological...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs