[go: up one dir, main page]
Scribd
Upload
38 views1 page

SQL tp1

The document outlines a series of SQL commands and PL/SQL procedures for managing and querying user and table information in an Oracle database. It includes commands for connecting as a sysdba, retrieving user details, counting tables, and creating procedures to list tables for a specific user. Additionally, it demonstrates how to create a function that counts objects owned by a specified user.

Uploaded by

Dhafer Harbaoui
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
38 views1 page

SQL tp1

The document outlines a series of SQL commands and PL/SQL procedures for managing and querying user and table information in an Oracle database. It includes commands for connecting as a sysdba, retrieving user details, counting tables, and creating procedures to list tables for a specific user. Additionally, it demonstrates how to create a function that counts objects owned by a specified user.

Uploaded by

Dhafer Harbaoui
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

1- connect as sysdba

2- select username, created from dba_user;


3- desc v$session
select username from v$session where type='USER';
4- SELECT select SUM(VALUE) from v$sga;
5- conn HR/hr
Describe user_objects;
select object_type, created, last_ddl_time from user_objects;
6-
select table_name from ALL_TABLES where owner = 'HR';

7-
create or replace PROCEDURE list_table
is
begin
for i IN( select table_name from user_tables)
loop
dbms_output.put_line(i.table_name);
end loop;
END;
/

execute list_table;

8- select count(*) from all_tables;


9- select count(*) from all_tables where owner='HR';
10-

create or replace function fn_nbrObjet (utilisateur varchar)


return number
is
a number;
BEGIN
select count(*) into a from dba_objects where owner = UPPER(utilisateur);
RETURN a;
END;
/
11-
CREATE OR REPLACE PROCEDURE PS_GET_TABLES_OF_USER (utilisateur varchar)
IS
BEGIN
for i in (select table_name from dba_tables where owner=utilisateur) LOOP
dbms_output.put_line(i.table_name);
END LOOP;
END;
/

execute PS_GET_TABLES_OF_USER('&a')

You might also like