Common Oracle DBA scripts

0
Select * from v$version; //Version of the DB

Select * from dual;

SELECT * FROM dba_users;

SELECT username FROM all_users ORDER BY username; //getting all users in the DB

select owner FROM all_tables group by owner;

SELECT username FROM dba_users WHERE default_tablespace not in ('SYSTEM','SYSAUX');

SELECT username
  FROM dba_users u
 WHERE EXISTS (
    SELECT 1
      FROM dba_objects o
     WHERE o.owner = u.username );
     
select * FROM all_tables where owner ='SYS' order by table_name;  

alter session set container = DBCS_PDB1; //Switching to PDB

alter session set container=CDB$ROOT; //returning to container DB

select * FROM all_tables where owner ='SYS' order by table_name;  

select name, open_mode from v$pdbs;

alter user LNDWARU identified by "XXXXXXX"; // change the password

//Creating a new user and inserting a table

create user laban identified by "XXXXXXXXX";
grant create session to laban;
grant unlimited tablespace to laban;

create table laban.messages (msg_id number(10) GENERATED ALWAYS AS IDENTITY START WITH 1000, msg_date date NOT NULL, msg_val varchar2(100));
insert into laban.messages(msg_date,msg_val) values  (sysdate,'Nie reke ngwere...');
insert into laban.messages(msg_date,msg_val) values  (sysdate,'No kugeria...');
commit;
select * from laban.messages;

alter user LNDWARU identified by "********";
ALTER USER LNDWARU ACCOUNT UNLOCK;

// Add a unlimited time password to your user:

ALTER USER apex_public_user IDENTIFIED BY "XXXXXXXX" ACCOUNT UNLOCK;

CREATE PROFILE password_unlimited LIMIT PASSWORD_LIFE_TIME UNLIMITED;

ALTER USER apex_public_user PROFILE password_unlimited;

ALTER SESSION SET CURRENT_SCHEMA = <schema name> //switch between different schemas 
0 0 vote
Article Rating

I am a Cloud Solutions Engineer working with a leading cloud solutions company. I have more than 5 years’ experience in the Tech ecosystem. I began my career as a software developer. I am a tech enthusiast, an autodidact whose interests includes Data Management, Cloud Native Development, Mobile and Bots Development, Integration, Low Code Development, AI, Analytics just to say the least.

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments