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 

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.

Leave a Reply

avatar
  Subscribe  
Notify of