New answers tagged oracle
0
Very effective is also moving everything to another new tablespace online:
## Lobs
select 'ALTER TABLE '||s.owner||'.'||l.table_name||' MOVE LOB('||l.column_name||') STORE AS (TABLESPACE USERNAME) online;'
from dba_segments s, dba_lobs l
where s.segment_name = l.segment_name
and s.tablespace_name = 'USERNAME'
and segment_type='LOBSEGMENT'
and ...
1
The exercise of updating the password hash that you reference is only necessary if you have users that only have a 10G password hash. If they also have 11G or 12C (or whatever is the minimum level you desire them to have) then there is no need to change or update individual users - just skip to setting the sqlnet.ora parameters described below to enforce the ...
1
If you have the proper permissions, you can change anyone's password. Of course, if you want to keep the password the same, just "changing" it so that it gets upgraded to new standards, you will need to know the current value.
And you don't have to expire it first. Expiring the password simply forces the user to set a "new" password the ...
1
What you are asking is exactly the way I use my databases that I have running in a Linux VM.
The first thing to do is to download and install the Oracle Instant Client for macOS. You can get it from here: https://www.oracle.com/database/technologies/instant-client/macos-intel-x86-downloads.html. This contains all essential command line tools you need: ...
-1
Downloaded Developer VM as you pointed in your post.
SSH into guest o/s does not set environment variables
C:\>ssh -p 2222 oracle@localhost
oracle@localhost's password:
Last login: Fri Feb 26 21:49:55 2021 from gateway
[oracle@localhost ~]$ echo $ORACLE_SID
[oracle@localhost ~]$ echo $ORACLE_HOME
As you can see above variables returns blank
Set ...
1
Wow, so much to process ... where to start?!?!
First, realize that the VM could just as easily be a physical server, behind a locked door in a lights-out data center on another continent. Communication between your host (the mac) and the vm is still via the ip network.
connected to the database inside the VM by 'talking' to port 1521 on
my machine
NO, you ...
-2
It prevent Aoplicatuions from inserting invalid data. P.For example for many currecncies it maks no senes to have more than 2 digits right from the decimal point.
0
Why not just get rid of the main_user and sub_user tables and have a nullable field on the users table called parent_user_id. Then you can use a self-join on users.parent_user_id = users.user_id to get all the sub-users for a given Travel Agency. (When the parent_user_id field is null, that's a Travel Agency.)
0
The command is definitely (user grid):
srvctl modify listener listener -p "TCP:1522,4444,nnnn"
Our issue was that the port we wanted to use, was used by another service on the ODA (e.g. ONS). The command completes but the listener ignores the port than. This explains the log above maybe.
We also needed to enter the local_listener on the ODA CDBs/...
0
There is no PK ... on this table.
Some might argue that this is not a "proper" table, but that's another "discussion".
This cannot be your actual [Java] code.
Your while loop is iterating through every row returned by the query (of which there could be lots, because your query has no "where" clause). The loop extracts values ...
0
The answer to a post mentioned on the side bar guided me to the following.
In
The Oracle® Database
Database Administrator’s Guide
21c
F31835-05
February 2021
in the chapter
11.12 Managing the SYSAUX Tablespace one can read
If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the ...
0
When you configure your Oracle Database instance to use the Dedicated connections, then you will have an ORACLE.EXE (SHAD) entry in the v$process view for each process that opens up a session with your instance.
So long as new programs connect to your Oracle instance in the Dedicated Server configuration, then the number of ORACLE.EXE (SHAD) processes will ...
0
What are the ramifications of having a full or almost nearly full SYSAUX tablespace (Oracle 19c)?
Nearly full? None at all.
Completely full? Probably a dead database!
By far the biggest consumer of SYSAUX in 19c is the Unified Audit Trail.
Where we used to dump gigabytes of Audit files into a file system somewhere and delete them occasionally, 19c now ...
0
This is one way to accomplish that, though there might be a simpler way:
WITH CTE_DirectorFilms_Distinct AS
(
SELECT DISTINCT D.DID, F.GID
FROM Directors D
INNER JOIN Films F
ON D.DID = F.DID
),
CTE_DirectorFilm_Matches AS
(
SELECT DF2.DID, COUNT(DF1.GID) OVER (PARTITION BY DF1.DID) AS DF1_GID_COUNT, COUNT(DF2.GID) OVER (PARTITION BY ...
1
You want to check AUTHENTICATION_TYPE in the DBA_USERS view. Users who are authenticated externally (EXTERNAL in the view), are authenticated by the operating system. A user cannot be both internally and externally authenticated, or be altered to switch authentication methods once created. If you haven't deliberately created externally authenticated users, ...
0
I moved all my data to new tablespace and then recreated old one and moved everything back. Everything seems in order, no data loss.
0
For more than one user to access the same ACL, grant the access privilege to each of the users, with the command "DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEG".
Example1
Grant the connect and resolve privileges for host www.us.oracle.com to SCOTT.
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'www.xml',
...
0
With A CTE and ROW_NUMBER in conhunction with Cross JOIN you can get your wanted result.
The CTE creates a temporary table, which has the row number for all all master ids and of course the wanted member id.
The SELECTmakes a Cartesian product of the first (latest) member id and the rest of the rows(member ids) for every master id
CREATE TABLE master
(&...
0
with data as
(
select 'Apple' as item, 'Rome' as destination from dual union all
select 'Apple' as item, 'Barcelona' as destination from dual union all
select 'Apple' as item, 'Moscow' as destination from dual union all
select 'Apple' as item, 'Paris' as destination from dual union all
select 'Apple' as item, 'London' as destination from dual union ...
0
Sorry it took me a while to get back to this. I see at least two major issues in the trace.log:
First, it appears your environment is misconfigured:
[main] [ 2021-02-03 15:09:37.030 GMT+03:00 ] [StepContext.:429]
Error initializing Network Configuration: Environmental error
detected: Oracle Home is set to "E:\oracle\client_19_3" in the
environment ...
1
No, because the Grid Infrastructure generally controls networking and storage access. If you can't start the DB without starting GI first, then you can't restart GI without restarting the database too.
1
You would need two different user roles: one with the permission granted for most users, and one without the permission for User B.
In the world of security, grants or permissions should always be assigned according to the principle of least privilege (only what the user absolutely needs to do their job). As such, privileges should always be denied by ...
0
The error might be a bit misleading.
It is correct to restore the control file from the latest backups assuming you want to recover to as late in time as possible. If your control_file_record_keep_time is long enough to also contain the backup pieces of the level 0 backup, rman will start restoring the level 0 backup and continue with the level 1 backup. If ...
0
Yes, there are.
every account puts load on the server and has the potential to bring it down by overloading it with silly queries or sometimes just bad luck.
more and more important: privacy issues. When a company leaks data they can get serious fines.
company secrets can be stored in the database. Leaking them does not help their case.
control and audits ...
0
In your tnsnames.ora on the standby side the needed tns alias for 'rac' is missing. In a data guard configuration it is extremely important that all members can resolve all other members.
0
You can not correct a bad implemented application from the database side in such a way that it will always work as expected. With cursor sharing the database tries to prevent parsing. This works when the application uses bind variables when working on item/person level and literals when working on group/class level.
You could try cursor_sharing force. This ...
0
Most likely this is in a database that was cloned from an other database and the tempfiles are not added. Oracle needs a functional temporary tablespace to be able to process joins and sorts. dba_temp_files is a view that does this.
Solution is to simply add a few tempfiles to the temporary tablespace.
0
By making those processes slower you als stretch the period with pain for both the regular users as well as the etl processes. We normally solve this by assigning a dedicated replica to the etl/reporting processes. That replicate can be tuned to better support the etl processing and it removes the need to put the extra pressure on the production database.
...
3
If this happens often there is an option to write a package or a stored procedure for that. The owner of the package would need the alter system privilege and the package should be created with definers rights.
You just need execute privileges on that package and feed the session kill procedure with the right parameters.
There is still a good chance that ...
0
Normally we allocate about half of the systems memory for shared_pool and database buffers, preferably mapped on huge pages. AMM is not compatible with huge pages so when a dba is working with the system he would disable that and manage the memory manually. Define a minimum size for the database buffers and also for the shared pool.
The rest of the memory is ...
0
User_B will be the owner of the objects and user_A is the user of those objects, which is a good separation and guarantees that the objects can not be changed by the application. You would not want user_A to be able to change any structure.
Normally you want an application manager to be able to connect as user_B to make the changes needed for a new version ...
0
normally you need a statically defined listener for this. So, in the listener.ora add the entries defining your instance. Next to that you need a remote passwordfile setup
remote_login_passwordfile = exclusive
to allow remote sys connects (don't forget the "as sysdba" part)
0
This happens when one processes cleans the archives and an other finds the need to backup it. Things to check:
do you have a archive log clean script other than rman? (hope not)
do you run archive log backups during the full database backup? if so, don't make archive backups in the db backup script but only in the archivelog backup script.
2
This should get you started:
declare
s date := date'2020-01-01';
e date := date'2020-01-04';
ndays number;
begin
ndays := e-s+1;
for i in 1..ndays loop
dbms_output.put_line (s);
s := s+1;
end loop;
end;
/
01-JAN-2020
02-JAN-2020
03-JAN-2020
04-JAN-2020
PL/SQL procedure successfully completed.
Just use simple date arithmetic: the ...
-2
Using just SQL and using Tom's as_many_rows trick(adjust as required):
with as_many_rows_as_you_need
as
( select level l
from dual
connect by level <= 100
)
select customer_num cust, card_number card, (a.card_issue_date + l) dat
from as_many_rows_as_you_need
, test_table a
where a.card_issue_date + l <= a.card_expire_date
and a....
2
Note that the following "auditing" mechanisms do not tell you who did the operation:
Temporal Tables
Change Data Capture
Change Tracking
Server Audit does, however, but it has weaknesses when it comes to parameterized SQL (you won't see the value in the WHERE clause, for instance). Server Audit is built on top of Extended Events.
Also, note that ...
0
The error just means that the shared_pool ran out of free space. This can be caused by many things, mostly application related. Make sure your shared_pool has a minimum size needed to run your application.
Sometimes it helps to increase the shared_pool_reserved_size so larger objects can be loaded in there.
0
No. rman does not delete directories that it did not create by itself.
0
The dba group name is registered in the oracle binary and can be verified using
grep SS_DBA_GRP $ORACLE_HOME/rdbms/lib/config.c
Members of this local defined group are normally dba's can connect locally to the database using os authentication and use their sysdba role doing so.
This prevents having to work using the software owner's account(often "...
0
Make sure that your tns files (tnsnames.ora, sqlnet.ora) are in a certain directory that is readable by all users. for example "/oracle/network/admin/"
Make sure that all your users and processes that need to work with the database[s] have the environt variable TNS_ADMIN defined with the value "/oracle/network/admin/".
Doing so gives a ...
0
There is a good chance that the default (and/or temporary) tablespace for MYSCHEMA was dropped. If that is the case, give MYSCHEMA a new tablespace, with quota and re-run.
This can be checked by viewing the entries in dba_users.
This is a bug and should normally not occur.
1
It's a requirement so that the SQL parser recognizes the elements. Without the alias it gets confused with the dot notation. Note that it applies to all object types. Not just ST_GEOMETRY or SDO_GEOMETRY.
(I was not aware of the treat(... as ...) trick. But then using an alias is still simpler.
2
Also @StefanPopescu take note that pretty much any auditing feature (at least in Microsoft SQL Server, but likely true in most modern RDBMS) will require more than just read-only access to the database to at least enable the feature and/or configure it.
In Microsoft SQL Server, database level Triggers work really well to audit schema changes, and can be used ...
3
For SQL Server, you have three options for looking at what has happened within the system. First are the Dynamic Management Views (DMV). However, they're either immediate, this query is running, or aggregations. Further, it's only the stuff that's currently in cache. Another choice is Query Store. It's also an aggregate, but one broken up, by default, into ...
5
Most major DBMS products today have facilities for this: it may be called
Change Data Capture (Oracle, SQL Server)
logical replication (Postgres, MySQL)
Most of these facilities operate on the publish/subscribe model and build upon the DBMS's built-in transaction logs to provide a stream of change events. Often the amount of logging will increase slightly ...
1
Because Oracle made it so.
t_alias
t_alias
Specify a correlation name, which is an alias for the table, view,
materialized view, or subquery for evaluating the query. This alias is
required if the select list references any object type attributes or
object type methods. Correlation names are most often used in a
correlated query. Other references to the ...
0
I tested in my test server.I have created as "COLA_MARKETS" Table using ST_GEOMETRY (TYPE). for the purpose of creating "Materialized View" i have to create view "COLA_MARKETS_VIEW" then create "Materialized View" name as "COLA_MARKETS_MVIEW".
CREATE TABLE cola_markets (
mkt_id NUMBER PRIMARY KEY,
name ...
0
A colleague has suggested that it's possible to solve this issue by:
"...creating a public synonym in the target DB on the top of DB link; the remote table will act like local table."
Supposedly, the synonym solves the issue of creating a materialized view with FAST refresh on a remote table with an SDO_GEOMETRY column.
CREATE public SYNONYM ...
0
v$sql gives you the information at a SQL child cursor level and sql_trace gives you the information at a SQL execution level. v$sql also tells you the program_id and the program_line# of the compiled pl/sql code that last hard parsed it.
That said, you are probably looking for the Hierarchical Profiler
0
There are basically 3 ways to copy the structure and data to another tables
Create table myschema.newtable as select * from anotherschema.oldtable
where 1 = 1; /* this copies all the data or 1 = 2 copies no data but creates the table */
this will not work for Oracle 12 and above if you have NVARCHAR (2000) and above, CLOBS or BLOBS or extended varchar2 ...
Top 50 recent answers are included
