New answers tagged oracle
0
Option 1:
Example:
run
{
set newname for datafile 1 to '/u01/NEW_PATH/system_tbs';
set newname for datafile 2 to '/u01/NEW_PATH/undo_tbs';
set newname for datafile 3 to '/u01/NEW_PATH/sysaux_tbs';
set newname for datafile 4 to '/u01/NEW_PATH/users_tbs';
...
set newname for tempfile 1 to '/u01/NEW_PATH/temp_tbs';
...
duplicate target database ...
0
No solution, but possible things to look at.
ALERT_.LOG (ALERT.LOG) File Specifically
Have a look at the alert.log file for your instance. It should contain lines that look a bit like this after you sent the command:
2022-01-07T08:37:09.455525+01:00
ALTER TABLESPACE RATEL
ADD DATAFILE 'e:\app\oracle\oradata\rvas\RATEL141.dbf'
SIZE 20000M
AUTOEXTEND ON
...
0
Three issues:
There shouldn't be any as keyword for table aliases.
"emc" and emc are two different things.
count(employees.employee_id) in the inner query isn't used anywhere. If it was used, you would probably want to give it an alias such as employee_count. (Also count(*) is the standard expression for counting rows, but it will give the same ...
2
Below command shows you the ORACLE_HOME used for that process.
strings /proc/54621/environ | grep ORACLE_HOME
Sometimes people add an unnecessary slash to the ORACLE_HOME, for example they use /product/oracle/1120/db_1/ instead of /product/oracle/1120/db_1, which may cause the above issue.
0
SQL Error: OA-12991 - Unable to Drop a Column after removing all referenced Multi-Column constraints
If your application is having GG replication. Ensure GG is stopped, delete the trandata. Execute the drop and then add trandata. Start GG.
These are the steps followed by my DBA.
0
with emc as (SELECT Employees.manager_id ,COUNT(employees.employee_id)
From employees Group by employees.manager_id)
Select phones From employees
left join emc
on employees.manager_id = emc.manager_id;
or
Select phones From employees left join
SELECT Employees.manager_id ,COUNT(employees.employee_id)
From employees Group by employees....
2
Use this one:
SELECT TABLE_NAME, PARTITIONING_TYPE, COLUMN_NAME, DATA_TYPE
FROM USER_PART_TABLES
JOIN USER_PART_KEY_COLUMNS ON NAME = TABLE_NAME
JOIN USER_TAB_COLS USING (TABLE_NAME, COLUMN_NAME)
where OBJECT_TYPE = 'TABLE';
3
You seem to be assigning too much meaning to the operation ID. It is there simply to uniquely identify each operation and has nothing to do with the order of operation execution.
You need to read the plan "inside out", from the most nested operation to the less nested one. With that in mind, let's see what is happening:
Op. 6. IDX1_SEGMENT is ...
0
You can import only single schema from your dump file by using "schemas" parameter. Also, you can import the schema into non-original name.
For example, if you want to import schema MY_SCHEMA into MY_NEW_SCHEMA use this command:
impdp system/password dumpfile=full.dmp logfile=full.imp_log schemas=MY_SCHEMA remap_schema=MY_SCHEMA:MY_NEW_SCHEMA
Omit ...
1
To find the name of the edition a session is using, do following query:
select object_name as USED_EDITION, vses.*
from V$SESSION vses
JOIN ALL_OBJECTS AO on AO.object_id = vses.session_edition_id
It will show you the name as used in DBA_EDITIONS or in ALL_EDITIONS.
0
Oracle APEX 4.2 is not supported beyond Oracle 12cR1. To get to database version 19c, you will first have to
Upgrade your existing APEX in the 12cR1 database to a minimum of version 18.2
Update anything in your application broken by the upgrade
Install that APEX target version separately in your 19c database
Export/import your applications using the APEX ...
2
Those steps work.
Instead of ALLSTATS LAST +COST +BYTES I do ALLSTATS LAST TYPICAL
This gets all the columns you get from the default execution plan display but with the additional row source execution statistics. I don’t know if my way gets additional columns but it will include at least cost and bytes anyway.
The warning means that that child cursor was ...
0
I have also searched answer for this question and we use similar to your option 1.
We create table
CREATE TABLE "EVENT"
( "GUID" VARCHAR2(40) NOT NULL ENABLE,
....
"BODY" CLOB NOT NULL ENABLE,
"DO_SEND" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE,
"MOMENT_CREATE" TIMESTAMP (6) DEFAULT ...
5
As documented, you can add PRETTY to a JSON function to prettify it
SELECT json_object(* PRETTY)
from dual;
db<>fiddle
Output
{
"DUMMY" : "X"
}
0
IMHO you have to buy another license (for BigData) or somehow transform your current one.
Basically there are these ways to implement CDC from Oracle DB.
Oracle streams - deprecated but still technically usable(no license cost)
Parse binary format of Oracle redo-logs. GoldenGate and Shareplex can do that. Both product require SW license. Otherwise it is ...
0
A tablespace is just a logical set of data files. It is ok for data files to be full; this will not affect performance unless you add data to a full tablespace. At that point you may have to wait for a data file to grow according to its autoextend settings, or if it is already at its maximum allowed size you may encounter an error stating that more space was ...
2
This should be what you want:
merge INTO category a
using (
select id, :status status
from category
start with id=:id
connect by parent_id = prior id
) b
on (a.id = b.id)
when MATCHED then update
set a.status = b.status;
The SELECT clause picks out the IDs and their new STATUS values that you want to change. The MERGE statement does the ...
0
You are using a filter condition on a table whose PK is referenced by an FK.
From Oracle Documentation
Filter conditions are unsupported for tables with primary or unique keys that are referenced by enabled foreign keys.
0
It fails because there is a foreign key, not because of integrity: the move is DDL, not DML.
2
I want to define read only (select) permission only on its own objects (USER30) if it comes from HOST2.
This isn't MySQL.
You cannot grant a User different permissions based on where that User connects from.
A User will always have change permissions on their own schema.
There is no way around this.
This is why any Data that matters should live in its own ...
1
Permissions should be granted by Privileges.
By default every user can read and write his own objects. I don't think there is an easy way to prevent that. Solution would be another user account where you only GRANT SELECT ON USER30.table_name TO USER30_RO). Unfortunately there is no privilege in Oracle which covers all tables/view within a schema. Privilege ...
3
Single database with an individual schema for each client
Schema-per-client is not real not infrastructure isolation; it's weaker, and available on both PostgreSQL and Azure SQL Database. But that doesn't give you per-client backups, per-client scaling, etc. Even Oracle supports database-per-client via Pluggable Databases since Oracle 12.
But both SQL ...
answered Dec 20 '21 at 3:04
David Browne - Microsoft
37.8k3 gold badges39 silver badges83 bronze badges
Top 50 recent answers are included