|
Forum: Server Administration
|
|
Topic: ALLOW_LEGACY_RECO_PROTOCOL
|
| ALLOW_LEGACY_RECO_PROTOCOL [message #689754] |
Tue, 16 April 2024 05:05 |
John Watson
Messages: 8928
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I'm trying to catch up on some 23c new features and came across this parameter ALLOW_LEGACY_RECO_PROTOCOL, default TRUE. The only doc I can find for it is in the DB Reference,
https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/ALLOW_LEGACY_RECO_PROTOCOL.html
which implies that Uncle Oracle has a new (and presumably better) way for managing distributed transactions, that can be enabled only if all the DBs are release 23. I've read all the way through the sections in the admin guide on distributed transactions, and I don't see anything new: the same 2PC mechanism as before. Has anyone come across this? And know what this "upgraded recovery protocol" is?
I'm asking only out of curiosity, appreciate any insight.
|
|
|
|
|
Forum: Server Utilities
|
|
Topic: impd data only on the same database from one table to another table
|
| Re: impd data only on the same database from one table to another table [message #689753 is a reply to message #689752] |
Tue, 16 April 2024 00:57 |
 |
Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
You have to use a db link as in my previous example.
It seems silly to create a db link on the same database but this is how it works.
I opened a SR on this in 10g and Oracle did not accept to create an enhancement request on this point.
Thinking about this, it seems it is just a command line syntax issue: as "dumpfile" parameter is optional, it can't know if your command refers on an implicit dump file or an export+import request.
I suggested we could specify a null dumpfile for this later but...
Note that I did not check but using a loopback db link may not mean Oracle uses the network layer.
[Updated on: Tue, 16 April 2024 01:19] Report message to a moderator |
|
|
|
| Re: impd data only on the same database from one table to another table [message #689757 is a reply to message #689753] |
Tue, 16 April 2024 08:17 |
 |
Barbara Boehmer
Messages: 9086
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I don't know if this is considered a loopback database link or not, but if you create a database link to the same database and use that as if it were a network link, then it works, as shown below.
SCOTT@orcl_12.1.0.2.0> CONN scott/tiger@orcl
Connected.
SCOTT@orcl_12.1.0.2.0> CREATE DATABASE LINK mylink CONNECT TO scott IDENTIFIED BY tiger USING 'ORCL'
2 /
Database link created.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE dept2 AS SELECT * FROM scott.dept@mylink WHERE 1 = 0
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM dept2
2 /
no rows selected
SCOTT@orcl_12.1.0.2.0> HOST impdp scott/tiger tables=SCOTT.DEPT content=data_only network_link=mylink remap_table=DEPT:DEPT2
Import: Release 12.1.0.2.0 - Production on Tue Apr 16 06:14:45 2024
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** tables=SCOTT.DEPT content=data_only network_link=mylink remap_table=DEPT:DEPT2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . imported "SCOTT"."DEPT2" 4 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Tue Apr 16 06:14:51 2024 elapsed 0 00:00:04
SCOTT@orcl_12.1.0.2.0> SELECT * FROM dept2
2 /
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
30 SALES CHICAGO
4 rows selected.
|
|
|
|
| Re: impd data only on the same database from one table to another table [message #689759 is a reply to message #689757] |
Tue, 16 April 2024 08:21 |
|
Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:I don't know if this is considered a loopback database link or not, but if you create a database link to the same database and use that as if it were a network link, then it works, as shown below.
Well, in my example in his previous topic I used a db link on the current database, I didn't specify it as it does not matter. The main point is you have to use a db link if you want to import without exporting.
|
|
|
|
|
Forum: SQL & PL/SQL
|
|
Topic: Oracle ORA-00918: column ambiguously defined
|
| Oracle ORA-00918: column ambiguously defined [message #689755] |
Tue, 16 April 2024 07:05 |
 |
Unclefool
Messages: 84
Registered: August 2021
|
Member |
|
|
I'm trying to generate some test data but I am getting the following error during the insert.
ORA-00918: column ambiguously defined
I can't seem to fix the issue. Any help will be greatly appreciated.
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF';
create table purchases(
ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
customer_id number,
PRODUCT_ID NUMBER,
QUANTITY NUMBER,
purchase_date timestamp
);
insert into purchases (customer_id, product_id, quantity, purchase_date)
select 1, 102,1, DATE '2024-04-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)
from dual
connect by level <= 3
UNION all
select 1, 102,1, DATE '2024-03-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '0 23:59:59' DAY TO SECOND)
from dual
connect by level <= 3;
|
|
|
|
| Re: Oracle ORA-00918: column ambiguously defined [message #689756 is a reply to message #689755] |
Tue, 16 April 2024 07:55 |
|
Barbara Boehmer
Messages: 9086
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You need column aliases for the columns in the first select statement in your insert statement, as shown below.
C##SCOTT@XE_21.3.0.0.0> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF';
Session altered.
C##SCOTT@XE_21.3.0.0.0>
C##SCOTT@XE_21.3.0.0.0>
C##SCOTT@XE_21.3.0.0.0> create table purchases(
2 ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
3 customer_id number,
4 PRODUCT_ID NUMBER,
5 QUANTITY NUMBER,
6 purchase_date timestamp
7 );
Table created.
C##SCOTT@XE_21.3.0.0.0>
C##SCOTT@XE_21.3.0.0.0> insert into purchases (customer_id, product_id, quantity, purchase_date)
2 select 1 customer_id, 102 product_id, 1 quantity,
3 DATE '2024-04-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)
4 as purchase_date
5 from dual
6 connect by level <= 3
7 UNION all
8 select 1, 102, 1,
9 DATE '2024-03-12' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '0 23:59:59' DAY TO SECOND)
10 from dual
11 connect by level <= 3
12 /
6 rows created.
C##SCOTT@XE_21.3.0.0.0> column purchase_date format a30
C##SCOTT@XE_21.3.0.0.0> select * from purchases
2 /
ORDER_ID CUSTOMER_ID PRODUCT_ID QUANTITY PURCHASE_DATE
---------- ----------- ---------- ---------- ------------------------------
1 1 102 1 12-APR-2024 13:00:00.000000
2 1 102 1 13-APR-2024 13:00:01.000000
3 1 102 1 14-APR-2024 13:00:02.000000
4 1 102 1 12-MAR-2024 13:00:00.000000
5 1 102 1 13-MAR-2024 12:59:59.000000
6 1 102 1 14-MAR-2024 12:59:58.000000
6 rows selected.
|
|
|
|
|
|
|
|
|
Topic: Convert input from user into UPPERCASE
|
| Convert input from user into UPPERCASE [message #689761] |
Tue, 16 April 2024 10:02 |
 |
watisditnou@msn.com
Messages: 1
Registered: April 2024
|
Junior Member |
|
|
For Oracle i have made a script to create a user after the input of a name
var muser VARCHAR2(10)
ACCEPT muser CHAR PROMPT 'Give username :'
CREATE USER "&&muser" PROFILE "WORK" IDENTIFIED BY "Test123" PASSWORD EXPIRE DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "&&muser";
GRANT "WORK_READ" TO "&&muser";
GRANT "CLUSTER_MUTATE" TO "&&muser";
This script is working fine but the user is created in lowercase and i want it to be in uppercase. I tried a lot but i can't get it done. It would also be ok if the name will be converted to another variable before the create and grant commands.
Any ideas of maby another script that wil do the trick?
I tried below commands and lots of more:
SET @muser2 = UPPER('&&muser');
SELECT @muser2 := SELECT UPPER('&&muser') from dual;
CREATE USER UPPER(&&muser');
|
|
|
|
| Re: Convert input from user into UPPERCASE [message #689762 is a reply to message #689761] |
Tue, 16 April 2024 10:35 |
|
Barbara Boehmer
Messages: 9086
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Welcome to the OraFAQ forums. Here is a simplified demonstration of one method;
SCOTT@orcl_12.1.0.2.0> var muser VARCHAR2(10)
SCOTT@orcl_12.1.0.2.0> ACCEPT muser CHAR PROMPT 'Give username :'
Give username :Test123
SCOTT@orcl_12.1.0.2.0> DECLARE
2 v_user VARCHAR2(10) := UPPER('&&muser');
3 BEGIN
4 EXECUTE IMMEDIATE
5 'CREATE USER "' || v_user || '" IDENTIFIED BY "Test123"';
6 EXECUTE IMMEDIATE
7 'GRANT CONNECT TO "' || v_user || '"';
8 END;
9 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> SELECT username FROM all_users WHERE UPPER(username) = 'TEST123'
2 /
USERNAME
--------------------------------------------------------------------------------
TEST123
1 row selected.
|
|
|
|
| Re: Convert input from user into UPPERCASE [message #689763 is a reply to message #689762] |
Tue, 16 April 2024 10:52 |
|
Barbara Boehmer
Messages: 9086
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is another method that works from SQL*Plus:
SCOTT@orcl_12.1.0.2.0> COLUMN muser NEW_VALUE v_user NOPRINT
SCOTT@orcl_12.1.0.2.0> ACCEPT muser CHAR PROMPT 'Give username :'
Give username :Test123
SCOTT@orcl_12.1.0.2.0> SELECT UPPER('&&muser') AS muser FROM DUAL
2 /
1 row selected.
SCOTT@orcl_12.1.0.2.0> CREATE USER "&v_user" IDENTIFIED BY "Test123"
2 /
User created.
SCOTT@orcl_12.1.0.2.0> GRANT "CONNECT" TO "&v_user"
2 /
Grant succeeded.
SCOTT@orcl_12.1.0.2.0> SELECT username FROM all_users WHERE UPPER(username) = 'TEST123'
2 /
USERNAME
--------------------------------------------------------------------------------
TEST123
1 row selected.
|
|
|
|
| Re: Convert input from user into UPPERCASE [message #689764 is a reply to message #689761] |
Tue, 16 April 2024 11:20 |
|
Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Just remove the " around the user name in CREATE USER then you don't need to upper case, Oracle will do it:
SQL> select username from all_users where username='TEST123';
no rows selected
SQL> create user test123 identified by "test123";
User created.
SQL> select username from all_users where username='TEST123';
USERNAME
------------------------------
TEST123
1 row selected. |
|
|
|
Blog
Search
Help
Members
Register
Login
Home
